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

3 thoughts on “How to Handle Divide-by-Zero in SSRS

    1. JimiBonham Post author

      Thanks for the suggestion. I don’t think this will work because NULL/0.0 returns “NaN.” That is when the denominator, 0.0, is a DECIMAL data type.

      Stay tuned. I will provide more details on the influence of data types in divide-by-zero scenarios in my next article.

  1. SmithBI consulting

    I was actually working on this issue at work and decided to calculate the result within SQL before moving onto SSRS – when you control the output numbers within SQL, you don’t need to calculate between columns in SSRS which makes creating a report much easier.

    I used CASE statement in SQL to make the result to come out as zero when “divided by” 0 zero:

    CASE WHEN (the number you are trying to divide “by”) = 0 THEN 0
    ELSE (main number you are trying to divide) / whatever numbers
    END AS [FinalResultForSSRScolumn]

    You will just get the result of 0 zero (instead of an error) for all the rows that were trying to divide by zero.

Leave a Reply