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:
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.