SSRS IIF statement behaving strangely

Question

I have a chart data to be displayed and SSRS is behaving in the strangest way possible. To explain things, I have a bar chart and a line chart in a single chart area. The line chart is where the issue is.

To understand this, below is the exact data I'm working on:

ID  Hours   Cost
-------------------
1   NULL    NULL
2   NULL    NULL
3   NULL    NULL
4   NULL    NULL
5   NULL    NULL
6   NULL    NULL
7   NULL    NULL
8   0       718.11
9   0       905.61
10  0       546.73
11  0       -708.4
12  0       1095.19
13  NULL    NULL
14  0       864.32
15  NULL    NULL
16  0       2165.96
17  0       1745.61
18  NULL    NULL
19  0       25984.04
20  80      8720.37
21  0       7074.41
22  0       -2431.18

Now, with this data, my line chart is printed with the below test expression:

= IIF(RunningValue(Fields!Hours.Value,Sum,Nothing) <> 0, 100, 200)

This expression gives me a proper output, that is a continuous line chart at value 200 till ID value 19. After that, the graph goes to 100 and continues.

The issue begins when I change the expression to my actual requirement as follows:

= IIF(RunningValue(Fields!Hours.Value,Sum,"Details") <> 0, (RunningValue(Fields!Cost.Value,Sum,"Details")/RunningValue(Fields!Hours.Value,Sum,"Details")), 0)

The graph simply breaks from ID 8 to 19. I don't understand why. The ELSE of the IIF method should have been executed and hence, value of 0 should be plotted. There should be no effect on the set of values affected. Can someone help me understand this?

I have fixed this using the following statement but I myself am not sure why this works and not the previous statement.

= IIF(RunningValue(Fields!Hours.Value,Sum,"Details") <> 0, (RunningValue(Fields!Cost.Value,Sum,"Details") * RunningValue(Fields!Hours.Value,Sum,"Details") ^ -1), 0)

Let me know if any other info is needed to understand the issue.

Answer 1

I think this is the issue with IIF statements resolving both possibilities and coming up with the divide by zero error.

Does the iif function compute both paths in SSRS or is it short-circuited?

Usually users add an IIF to the numerator to be 0 if the Denominator is 0 and a second IIF to the denominator to divide by 1 if 0 so the result is 0 if the denominator is 0.

I believe that changing the function to be to the power of 1 instead of divide by zero, the result is infinity rather than divide by 0.

https://math.stackexchange.com/questions/1009868/why-does-zero-raised-to-the-power-of-negative-one-equal-infinity/