SSRS #Error in combination with IIF and Cdbl

Question

hope someone more experienced can give me answer/solution to my problem. In a database I have two columns, one to control do we show data and the data to be shown is in the second column. I have data and expression as below:

ShowPriceOnMatrix | RSP
-------------------------------
0                 | 1.48
1                 | 10.26 euro    -> This one fails with #Error
1                 | 4.59
0                 | 7.12


=IIF(Fields!ShowPriceOnMatrix.Value = 1, CDbl(Fields!RSP.Value), "")

This expression sometimes gives me #Error. The problem is that the RSP field was defined as nvarchar and I cant change it now so I'm trying to convert to number and show as. Some users when input price on the filed they also type a text ex. "12.45 euro" or similar. When the expression hits a value with text the whole IIF fails with an #Error even if the first column is stated not to show the price.

The problem with IIF is that it is not skipping the second parameter if the first one evaluates to false - and therefore produces #Error in my case.

I've tried to use

IsNumeric(Cdbl(Fields!RSP.Value))

and

IsError(Cdbl(Fields!RSP.Value))

but both of them give an #Error also. From SSRS explanation for those functions, they should return a Boolean value but somehow I'm getting #Error :)

Answer 1

Just convert the field to numeric using

VAL(Fields!RSP.Value)

Also, you might want to consider using SWITCH instead of IIF. SWITCH stops at the first expression which evaluates to True, whereas IIF will evaluate all expressions regardless of if they will be used or not.

In your case, with a single IIF, SWITCH is not relevant but in cases where nested IIFs are used or situations where you may get a divide by zero error, SWITCH is often easier to read/debug. So something like this where we have a hypothetical situation that we want to test for divide by zero and return zero or if the thing we are dividing by is negative we want to return -1, otherwise do the calc...

=IIF(Fields!MyDivisor.Value = 0, 0, IIF(Fields!MyDivisor.Value <0, -1,  Fields!MyNumber.Value/ Fields!MyDivisor.Value))

would become

=SWITCH (
    Fields!MyDivisor.Value =0, 0,
    Fields!MyDivisor.Value <0, -1,
    True, Fields!MyNumber.Value/ Fields!MyDivisor.Value
    )

each expression/result pair is evaluated in order until it hits the first True expression, so if MyDivisor was zero it would return 0 and stop, if it was negative, it would return -1 and stop. The final True acts like an else (as True always returns True ! ) so it would only do the calculation if all other expressions returned false.