iif statement problem

hi all,

i've browsed some of the smilar iif expression problems but no idea how to solve mine.

i get an #Error when i wrote the following expr :

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0))

but no error when it's

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0) - iif(Fields!revenue_type.Value="T", Fields!amount.Value,0))

If i filter the dataset and just take in data where revenue_type = "R" then there won't be error... how come?

did i do something wrong? please help... this seems so simple yet i couldn't get it..

oh btw, i wrote the expression in table footer|||Maybe somewhere Fields!revenue_type is "null", then accessing Fields!revenue_type.Value will generate error. Try checking (IsNothing(Fields!revenue_type.Value) = false and Fields!revenue_type.Value = "R").|||

hi Maciej, thanks for your reply but it still return #Error.

there's no null value for revenue_type

|||

Ok, after your post I've tried the same thing in one of my reports and got the same error (tip - it is good to read the "error list" ). Mine says:

Warning 1 [rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox116’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type. c:\...\Obroty producentow.v1.2.rdl

So the problem is in aggregation varying data types. Simplest way to fix it:

=Sum(iff(<your bool>,CDbl(<your sum field>),CDbl(0))

CDbl is used only as a example (it will conver every number to "double" data type). You can use different conversion method (from Common functions\Conversion).

I hope this will help

Maciej

|||

thanks Maciej. it works! but i don't get why the expr needs to be converted to double data type...

cos i got that warning as well but didn't get it...

|||Maybe RS is not so clever Your field if decimal or duble or something and "0" is integer so RS gets lost |||You hit the nail on the head with "Maybe RS is not so clever." I experienced the same #error issue in some of my calculated columns in a table in my report but oddly not until i added 2 text boxes above the table to my report. The text boxes display a begin and end time from a separate data set query. When I added those, I got the #error issue. But when i remove them, it goes away. I did not edit the table with the calculated columns in any way. I was able to get the #error issue to go away by using the CDbl solution you described (THANKS!) and leave the text boxes with the datetimes on the report. But there has to be a bug somewhere because that makes no sense. MICROSOFT> PLEASE NOTE AND FIX!!!