reporting services - How to handle IIF or Switch divide by zero giving #ERROR?

Question

I tried using IIF and Switch case, but I am unable to handle N/A case when there would be a divide by zero error. For example:

=switch(
    ReportItems!Textbox54.Value = 0, "N/A",
    ReportItems!Textbox54.Value <> 0, ((ReportItems!Textbox56.Value) / (ReportItems!Textbox54.Value)))

I am getting N/A with out this:

ReportItems!Textbox54.Value <> 0, ((ReportItems!Textbox56.Value) / (ReportItems!Textbox54.Value)

But if add this condition I am getting an error?

I am using SSRS 2008 R2.

Output:

19.47%
13.85%
#Error

Answer 1

The problem with the IIF function is that it is a function not a language construct. This means that it evaluates both parameters before passing the parameters to the function. Consequently, if you have a divide by zero error, this will get evaluated and cause an #ERROR condition even when it looks like that code shouldn't be executed due to boolean condition of the IIF statement.

There are two workarounds for this problem:

IIF bypass

Basically make two IIF function calls where you won't get divide by zero errors:

=IIF(ReportItems!Textbox54.Value <> 0, 
    ReportItems!Textbox56.Value / IIF(ReportItems!Textbox54.Value = 0, 1, ReportItems!Textbox54.Value),
    "N/A")

So where ReportItems!Textbox54.Value is zero, divide by 1 instead, throw that result away and use N/A.

Custom Code

Create a safe divide by zero function in custom code where you can use real language constructs.

Public Function SafeDivide(ByVal Numerator As Decimal, ByVal Denominator As Decimal) As Decimal
    If Denominator = 0 Then
       Return 0
    End If
    Return (Numerator / Denominator)
End Function

and then use this in your report for the Value expression instead of IIF or SWITCH:

=Code.SafeDivide(ReportItems!Textbox56.Value, ReportItems!Textbox54.Value)

and use a Format string to display zeroes as "N/A":

#,##0.00;-#,##0.00;N/A