Occasionally you need to do division in a ReportingServices report, for example where you have to calculate percentages. But when your divisor is zero you get an ugly ‘#Error’ in the field where the division is done.
Checking for a zero in the divisor seems like an obvious solution, but unfortunately the ‘IIF’ statement that you would use to check for this evaluates the True and False parts of the answer regardless of the result of the result it is evaluating. So if Y = 0, and your field has the following: IIF(Y=0, 0, X/Y), you will still get the #Error message.
The fix for this is to put the following code in the report:
Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function
Then in the field replace the IIF statement with the following Code.Divide (X/Y).
That should take care of it.
