Geek Answers Handbook

SQL Reporting Services IIf expresses both sides if #ERROR is called

I am currently working on a report that contains data entered into it from a web service. This web service sends him a "double?" allowing zero values. The report displays the return values, which are later used to add and calculate numbers. My problem is the expression to display the value (and I see that a similar problem occurs when I work on the calculations, but one step at a time ...) I use the IIf operator to check if the value is numeric, if so display the value ( converting it to double) if you do not display an empty string. When its numerical value, the value is displayed correctly, but when its value is null, I get the value #ERROR. It seems that the IIf operator evaluates both ends of the IIf expression !!!

Anyway, I did some research, and it seems that people suggest changing the transmitting code to check if its value is null and sends 0 instead of BUT in my case it will not help (and there will not be a negative number as it also sends negative numbers ) The reason this doesn't work in my case is because I have to calculate the average value using all 0, but not zero values. For instance...

"100, 0, null" => it should be an average of 50 ... if this zero where to convert to 0 it will be an average of 25 ...

Anyway, my code ...

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

I also tried using the switch statement and got the same problem ...

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

I also tested that condition IIf works correctly by testing this:

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

And it works correctly ... In any case, any help would be greatly appreciated, since I do not know what else to try ... Below are some links that I found regarding my problem, but again they recommend sending 0 instead of null, which in my case it’s useless ... And one link suggests using a switch, but again this does not work ...

Link # 1

Link # 2

+4
source share
2 answers

I just solved this problem, how can I do it, hope it helps someone else with the same problem !!

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

You will have the same problem using an expression with a possible division by zero, for example IIF([B]=0, Nothing, [A]/[B])

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

This error has also been reported on Microsoft Connect , but there is no response from MS yet.

0
source

More articles:


All Articles