SSRS Report Builder expression error with IIF( ) function

1
votes

Recently I've encountered an inconsistency with the use of IIF() function in SSRS. I am working on an aggregate function with multiple conditions. Suppose I have this code:

=Sum(IIF(Fields!MATL_STAT.Value="Disposed", Fields!MATL_SIZE.Value,0))

By description, the expression determines the sum of the sizes of the MATL with a MATL_STAT = "Disposed" but everytime I run the report there is an #Error.

My theory is that you can't put fields in the "true option" of IIF() function.

Now, if my theory is correct, is there a workaround for this?

2
vb.netreporting-servicesssrs-2008
IIRC this should work. Could you have a NULL (particularly a DbNull) in one of those fields throwing things off? – lc.
I already eliminated all the null values on my query using NVL() function. btw, I use Oracle. – Christian Mark
You can most definitely put fields in the true option of the IIF function. Sometimes you get #error when it is expecting a different data type. Can you do CStr(Fields!MATL_STAT.Value) = CStr("Disposed") and see if you still get the error? – mmarie
Actually I solved it already using CDbl() function. – Christian Mark

2 Answers

1
votes

After an extensive research using builtin function is SSRS reporting services, the field is summing different data types (double/int). To unify the data type, I use CDbl() function like this:

=Sum(IIF(Fields!MATL_STAT.Value="Disposed", CDbl(Fields!MATL_SIZE.Value),CDbl(0.0)))
0
votes

you can use Decimal conversion (CDEC) as well reason being the value output is an amount coming in decimals..