How do you handle divide-by-zero errors when doing division at the report level in SSRS? Unfortunately, a single IIf statement does not work because it evaluates both results before deciding which one to act on. For example, when the denominator is 0, this returns an error:
=IIf ( Fields!Denominator.Value = 0 ,NOTHING ,Fields!Numerator.Value / Fields!Denominator.Value )
(Actually, it either returns “#Error”, “Infinity”, “-Infinity”, or “NaN” depending on the data types involved. My next blog post will provide more detail on that. Let’s assume in this blog that the numerator and denominator are both DECIMAL, which will result in “#Error” when the denominator is zero.)
Pros and Cons
Here are the pros and cons of the different techniques. If you have some more insight on one of these, or know of another technique, let me know in the comments.
Technique #1 – Nested IIf Statement
The first and most commonly used workaround is the nested IIf statement:
=IIf ( Fields!Denominator.Value = 0 ,NOTHING ,Fields!Numerator.Value / IIf ( Fields!Denominator.Value = 0 ,1 ,Fields!Denominator.Value ) )
As you can see, this works because when the FALSE condition of the first IIf is evaluated, it does not try to divide by zero.
Technique #2 – Math Trick
=IIf ( Fields!Denominator.Value = 0 ,NOTHING ,Fields!Numerator.Value * (Fields!Denominator.Value ^ -1) )
As you know, in algebra:
X * (Y ^ -1) = X * (1 / Y) = X / Y
So it is the same as dividing by the denominator. But why does it work when the denominator is 0? This works because (0 ^ -1) evaluates to “Infinity” in SSRS regardless of the data type. Be aware this does not work in other places, like T-SQL:
Or the plain ol’ Windows calculator:
Or the TI-83 calculator:
Interestingly, however, it does work in PowerShell:
I would never expect this to work because it doesn’t really make mathematical sense! Dividing by zero and raising 0 to the power of -1 are supposed to be the same thing. If you are a stickler for math, you may be a little hesitant to use this trick in your reports.
Technique #3 – Custom Code
You may prefer to return 0 instead of NOTHING. If so, you have the additional option of using custom code:
............................ Public Shared Function SafeDivision(ByVal numerator as Decimal, ByVal denominator As Decimal) As Decimal If denominator = 0 Then SafeDivision = 0 Else SafeDivision = numerator / denominator End If End Function ............................ =Code.SafeDivision(1, 0)
This allows for very compact syntax in your expression. But it cannot return NOTHING. Even when you try to return NOTHING as below, it still returns 0:
............................ Public Shared Function SafeDivision(ByVal numerator as Decimal, ByVal denominator As Decimal) As Decimal If denominator = 0 Then SafeDivision = NOTHING Else SafeDivision = numerator / denominator End If End Function ............................ =Code.SafeDivision(1, 0)
If you have some more insight on one of the techniques above, or know of another technique, please let me know in the comments.
* SSRS behaves the same when dividing by NULL
** You get 2 points if you caught the Chuck Norris joke above