Category Archives: SSRS

How to Handle Divide-by-Zero in SSRS

The Error(s)

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.

DivideByZero_ProsAndCons

 

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:

DivideByZero_TSQL

Or the plain ol’ Windows calculator:

DivideByZero_Calc

Or the TI-83 calculator:

DivideByZero_ChuckNorris

Interestingly, however, it does work in PowerShell:

DivideByZero_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)

 

Comments?

If you have some more insight on one of the techniques above, or know of another technique, please let me know in the comments.

 

Notes:
* SSRS behaves the same when dividing by NULL
** You get 2 points if you caught the Chuck Norris joke above