SSRS 2008 - Dealing with division by zero scenarios
We're running into a problem with one of our reports. In one of our tablixes a textbox has the following expression:
=Iif(Fields!SomeField.Value = 0, 0, Fields!SomeOtherField.Value / Fields!SomeField.Value)
Which should be pretty self-explanatory. If "SomeField" is zero, set the text box value to zero, else set it to "SomeOtherValue / SomeValue".
What has us stumped is that the report still throws a runtime exception "attempted to divide by zero" even though the above expression should prevent that from happening.
We fiddled a bit with the expression just to make sure that the zero-check is working, and
=Iif(Fields!SomeField.Value = 0, "Yes", "No")
works beautifully. Cases where the data is in fact zero resulted in the textbox displaying "Yes" and vice versa. So the check works fine.
My gut feel is that the Report rendering engine throws the exception at run-time, because it "looks" as if we are going to divide by zero, but in actual fact, we're not.
Has anyone run into the same issue before? If so, what did you do to get it working?
Answer
IIf will always evaluate both results before deciding which one to actually return.
Try
=IIf(Fields!SomeField.Value = 0, 0, Fields!SomeOtherField.Value / IIf(Fields!SomeField.Value = 0, 1, Fields!SomeField.Value))
This will use 1 as the divisor if SomeOtherField.Value = 0, which does not generate an error. The parent IIf will return the correct 0 for the overall expression.