Error level and expressions

In a report, I have the expression :

=iif(Sum( Fields!X_A_1.Value)<>0,(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value),"NA")

This expression product the error

"The value expression for the textbox ‘textbox12’ contains an error: Attempted to divide by zero."

I think it's because reporting execute the expression
(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value)

even if X_A_1 is equal to 0.

I would like reporting to display NA and not "#Error" when X_A_1 = 0

Is there is a way to catch the error in reporting?

Is there is a way for reporting not to execute the expression

(Sum( Fields!X_A.Value)-Sum( Fields!X_A_1.Value))/Sum( Fields!X_A_1.Value)

when X_A_1 is equal to 0.

Sorry if my english is not very good

Note: IIF() is a function call and evaluates all arguments (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp)

Instead of the pattern =IIF(Y=0, A, X/Y), use =IIF(Y=0, A, X / IIF(Y=0, 1, Y)) to avoid the division by zero.

-- Robert

Error in SSRS Expressions

I am having an expression which is supposed to give me the values selected in a multivalued input parameter. I have tried following options however everytime it gives me error. Could someone let me know what might be the issue here.

1. Expression: =Parameters!i_Category.Label

Output: The Value expression used in textbox ‘textbox47’ returned a data type that is not valid.

2. Expression: =Parameters!i_Category.Label.ToString()

Output: System.Object[]

3. Expression: =CStr(Parameters!i_Category.Label)

Output: Error: Conversion from type 'String()' to type 'String' is not valid.

Take a look at this link for more information about parameters and working with multi-valued parameters.

http://msdn2.microsoft.com/en-us/library/aa337292.aspx|||Thanks Ian. It helped me a lot.

Error in SELECT clause: expression near '='.

When I use a construction like this in the sql designer of visual
studio 2005. i get the error that it doesnt understand this. However
the sql is just fine is there a way to make this work in visual studio?
i did set the prefix for the parameters so it does understands the @. in
other types of query.
DECLARE @.TEMP t_timestamp
this block in the designer:
SELECT @.TEMP=rprl_timestamp
FROM rap_rol
WHERE rol_id = 10Jeroen a crit :
> When I use a construction like this in the sql designer of visual
> studio 2005. i get the error that it doesnt understand this. However
> the sql is just fine is there a way to make this work in visual studio?
> i did set the prefix for the parameters so it does understands the @. in
> other types of query.
> DECLARE @.TEMP t_timestamp
^^^^^^^^^^^
what is this type '? t_timestamp ?
Do you use a CLR UDT ?

> this block in the designer:
> SELECT @.TEMP=rprl_timestamp
> FROM rap_rol
> WHERE rol_id = 10
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||its just a user defined datatype
it does not matter what type for my question. but in this case its a
datetime.
the problem is in the line SELECT @.TEMP=rprl_timestamp
where the @.temp is filled with the value of rprl_timestamp. its perfect
sql but the designer doesnt understand it.

Error in Hiding Report Item

Hi,
I am trying to show the textbox depending on the page numbers in Page
Footer. But, I get the following error in doing so
Expression in Visibility section of Textbox:
IIF(Globals!PageNumber>1,true,false)
Error: The hidden expression for the textbox 'textbox400' has the value
"IIF(Globals!PageNumber>1,true,false)", which is not a valid Boolean value.
Can someone help me with this
Thanks
Ponnurangamexpressions should begin with "="
i.e.
=IIF(Globals!PageNumber>1,true,false)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
news:OGwmayVmEHA.412@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am trying to show the textbox depending on the page numbers in Page
> Footer. But, I get the following error in doing so
> Expression in Visibility section of Textbox:
> IIF(Globals!PageNumber>1,true,false)
> Error: The hidden expression for the textbox 'textbox400' has the value
> "IIF(Globals!PageNumber>1,true,false)", which is not a valid Boolean
> value.
> Can someone help me with this
> Thanks
> Ponnurangam
>|||Try enclosing the TRUE and FALSE statements in double quotes (").
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message news:<OGwmayVmEHA.412@.TK2MSFTNGP10.phx.gbl>...
> Hi,
> I am trying to show the textbox depending on the page numbers in Page
> Footer. But, I get the following error in doing so
> Expression in Visibility section of Textbox:
> IIF(Globals!PageNumber>1,true,false)
> Error: The hidden expression for the textbox 'textbox400' has the value
> "IIF(Globals!PageNumber>1,true,false)", which is not a valid Boolean value.
> Can someone help me with this
> Thanks
> Ponnurangam|||Sorry, i read this error wrong. Do you have an equals (=) sign in
front of the expression? i tried what you did, and it works fine for
me.
=iif(Globals!PageNumber>1,TRUE,FALSE)
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message news:<OGwmayVmEHA.412@.TK2MSFTNGP10.phx.gbl>...
> Hi,
> I am trying to show the textbox depending on the page numbers in Page
> Footer. But, I get the following error in doing so
> Expression in Visibility section of Textbox:
> IIF(Globals!PageNumber>1,true,false)
> Error: The hidden expression for the textbox 'textbox400' has the value
> "IIF(Globals!PageNumber>1,true,false)", which is not a valid Boolean value.
> Can someone help me with this
> Thanks
> Ponnurangam

Error in formula any help please

i am trying to write a double expression to return a count of sales
orders when they are from "uk" and status is "good"
=SUM((IIf((Fields!locale.Value) = "uk",1,0)),IIf(Fields!
Status_name.Value ="Good",1,0))
but get an error
The scope parameter must be set to a string constant that is equal to
either the name of a containing group, the name of a containing data
region, or the name of a data set#
any help please my formula looks correct'
thanks in advanceignore me it was a simple error i needed and "and" not a ","
thanks anyway

error in executing SELECT statement

I get this message when executing SELECT statement in SQL 2000:
Location: recbase.cpp:1374
Expression: m_nVars>0
S PID: 51
Process ID: 2480
Why? Any help?is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>|||We've found that to be a memory issue with the MEM TO LEAVE region. SPID 51
is usually one of the SQL Agent processes on SS2K, and it is usally the db
maint packages, typically the transaction log backups, that have
insufficient memory to load the code base.
What does the DBCC MEMORYSTATUS show you?
Sincerely,
Anthony Thomas
"Olu Adedeji" <i-oluade@.microsoft.com> wrote in message
news:%23Dk$7%2357EHA.2016@.TK2MSFTNGP15.phx.gbl...
is this a large table(how big is the table - rows, data types)?
possible disk controller issue?
"Dzemo" <dzemo@.wizard.ba> wrote in message
news:eLgrfcy7EHA.3840@.tk2msftngp13.phx.gbl...
> I get this message when executing SELECT statement in SQL 2000:
> Location: recbase.cpp:1374
> Expression: m_nVars>0
> S PID: 51
> Process ID: 2480
> Why? Any help?
>

Error in CASE expression

I would appreciate assistance as to how I should correct the following CASE expression::

BilledCarrier = CASE LEN(cur_billed_carrier) WHEN >0 THEN cur_billed_carrier ELSE 'Not Billed' END

I want to return the cur_billed_carrier when its length is >0 but I can't find the correct syntax. My attempt returns an error at the '>' operator. Thank you.

CASE expression has two formats:

case <expr>

when <expr1> then <return_expr1>

when <expr2> then <return_expr2>

...

else <return_expr_N>

end

-- and

case

when <lt_expr1> <operator> <rt_expr1> then <return_expr1>

when <lt_expr2> <operator> <rt_expr2> then <return_expr2>

...

else <return_exprN>

end

So you need to use the 2nd syntax like:

case when LEN(cur_billed_carrier) > 0 then cur_billed_carrier else 'Not Billed' end

-- or

case sign(len(cur_billed_carrier)) when 1 then cur_billed_carrier else 'Not Billed' end

|||Very clear; thank you

Error in add count Expression

Dear All,
I'm creating a report in the CRM , i'm using group by clause and i need to
add count Expression in the group footer depending on the details area of
the group , i've added this code in a textbox :
= count(fieldname.value, scope)
it didn't work & it generates an error that the name of the scope that i
have entered is not valid group details area
i've entered the scope as string
could anyone help me ?
thanks in advanceIf the code you entered is literal and not an example, I would say it is a
syntax error first. I would not think that you would need to specify the
"scope" if this is simply a group footer, as it should know the count
expression is in the scope of the group it is in, but maybe I am missing
something from your description.
I would try:
=count(Fields!FieldName.Value)
Rodney Landrum
"Karim Mohamed" <k_a_r_i_m_._m_o_h_a_m_e_d@.link.net> wrote in message
news:O3pEyFaWGHA.4572@.TK2MSFTNGP03.phx.gbl...
> Dear All,
> I'm creating a report in the CRM , i'm using group by clause and i need to
> add count Expression in the group footer depending on the details area of
> the group , i've added this code in a textbox :
> = count(fieldname.value, scope)
> it didn't work & it generates an error that the name of the scope that i
> have entered is not valid group details area
> i've entered the scope as string
> could anyone help me ?
> thanks in advance
>