Persistent Divide by Zero Error

In an SSRS 2008 R2 report I had a textbox that used nested IIf functions in an expression.  There’s an IIf used to find a percentage whether the data used is monthly or annual.  That expression is wrapped in another IIf to return a 0 if the denominator is 0 to prevent a divide by zero error.  Here’s an example expression:

=IIf(Fields!Tasks.Value = 0 , 0,
        Fields!Goal.Value / IIf(Parameters!MonthlyGoalFlag.Value = 1, Fields!Tasks.Value/12, Fields!Tasks.Value)
    )
IIf(Parameters!MonthlyGoalDataFlag.Value = 1, Fields!Tasks.Value/12, Fields!Tasks.Value)

Unfortunately, SSRS evaluates all IIf’s regardless of their order and even though there’s a guard for the 0 denominator the report still renders with a #Error result in each textbox with a 0 denominator regardless of the guard.

I have a workaround that is quick and easy, use a little function to perform your own division.  Add this function to the Code property of the report:

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

You can then change the expression to:

=Code.Divide(Fields!Goal.Value,Fields!Task.Value))

Now there’s no need for the nested IIf’s and if you used an IIf as a guard it will still return the correct result.

Leave a Reply

Your email address will not be published. Required fields are marked *