Divide by Zero Error in ReportingServices

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.