CODEWITHSUNDEEP
reporting-servicesssrs-2008
Orlando
Orlando

Reputation: 975

SQL Reporting Services IIf statement evaluates both sides of if causing #ERROR

I am currently working on a report which has data injected into it from a Web Service. This webservice sends it a "double?" permitting null values. In the report it displays the values returned which are later used to sum and average the numbers. My issue is in the expression to display the value (and i can see a similar issue coming up when i work on the calculations but one step at a time...) I am using an IIf statement checking if the value is numeric, if so display the value (converting it to a double) if not display an empty string. When its a numeric value the value displays properly however when its null I am getting the value #ERROR. It seems that the IIf statement evaluates both ends of the IIf statement!!!

Anyhow I have done some research and it seems people suggest to modify the sending code to check if its null and send a 0 instead BUT in my case this wont help (nor will a negative number as it also sends negative numbers). The reason for this not working in my case is that I have to calculate an average using all 0s but not null values. For example...

"100, 0, null" => this should be an average of 50... if that null where to be converted to 0 it would average 25...

Anyways heres my code...

=IIf(IsNumeric(Fields!Ventas.Value), CDbl(Fields!Ventas.Value), "")

I have also tried with a switch statement and get the same issue...

=Switch(IsNumeric(Fields!Ventas.Value) = False, "", IsNumeric(Fields!Ventas.Value) = True, CDbl(Fields!Ventas.Value))

Also I have tested that the IIf condition is working properly by testing this:

=IIf(IsNumeric(Fields!Ventas.Value), 1, 0)

And that works properly... Anyways any help would be greatly appreciated as I dont know what else to try... below are some links I found regarding my issue but again they recommend sending a 0 instead of null which in my case is useless... And one link suggests using a switch but again that didnt work...

Link #1

Link #2

Upvotes: 4

Views: 6903

Answers (2)

Vad
Vad

Reputation: 898

You will have the same issue using expression with possible division by zero, like IIF([B]=0, Nothing, [A]/[B])

I used a simple workaround: IIF([B]=0, Nothing, [A]/IIF([B]=0, 1, [B]))

This error is also registered on the Microsoft Connect, but no answer from MS so far.

Upvotes: 1

Orlando
Orlando

Reputation: 975

I just solved this issue heres how it can be done hope this helps someone else with this same problem!!

=IIf(IsNumeric(Fields!Ventas.Value), CDbl(IIf(IsNumeric(Fields!Ventas.Value), Fields!Ventas.Value, 0)),  "")

Upvotes: 6

Related Questions