# 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.

# 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 DecimalIf denominator = 0 ThenSafeDivision = 0ElseSafeDivision = numerator / denominatorEnd IfEnd 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 DecimalIf denominator = 0 ThenSafeDivision = NOTHINGElseSafeDivision = numerator / denominatorEnd IfEnd 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*

Richard Tien ChenAnother solution, make numerator NULL or NOTHING when denominator is 0.

JimiBonhamPost authorThanks 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.

SmithBI consultingI 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.