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?

Solution 1:

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.

Solution 2:

An easy clean way to prevent a divide by zero error is using the report code area.

In the Menu, go to Report > Report Properties > Code and paste the code below

Public Function Quotient(ByVal numerator As Decimal, denominator As Decimal) As Decimal
        If denominator = 0 Then
            Return 0
        Else
            Return numerator / denominator
        End If
    End Function

To call the function go to the the Textbox expression and type:

 =Code.Quotient(SUM(fields!FieldName.Value),SUM(Fields!FieldName2.Value))

In this case I am putting the formula at the Group level so I am using sum. Otherwise it would be:

 =Code.Quotient(fields!FieldName.Value,Fields!FieldName2.Value)

From: http://williameduardo.com/development/ssrs/ssrs-divide-by-zero-error/