0

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.

Sujeet Sinha
  • 2,417
  • 2
  • 18
  • 27

1 Answers1

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/

Community
  • 1
  • 1
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • I have read about the short-circuit method you shared. What I don't get it is even with evaluating both the parts, why is it still not using the false part? I tried changing the order of the parts, in case the evaluation was stopping at the second part due to the error and not proceeding to the third but no success. What's more surprising is that the last statement worked which is essentially doing the same thing. Any leads on that? Hope I'm not too much trouble or confusing.. – Sujeet Sinha Jul 23 '16 at 03:33
  • I think the problem is that while the false part would work, the expression also evaluates the true part (even though it's not going to use it) which gets the #DIV/0 error. It then just returns the error instead of the valid part. Very annoying. – Hannover Fist Jul 25 '16 at 15:42
  • I think I understand what you are trying to say.. But, why is the last statement working. Taking reciprocal of a number should have the same result, right? One upvote for the effort! – Sujeet Sinha Jul 25 '16 at 16:25
  • I believe that it's technically infinity rather than divide by 0. http://math.stackexchange.com/questions/1009868/why-does-zero-raised-to-the-power-of-negative-one-equal-infinity – Hannover Fist Jul 25 '16 at 16:47
  • Well then, thats settled then.. i'm marking this as an answer but add the last part to the post so others may refer that as well.. – Sujeet Sinha Jul 26 '16 at 04:38