SSRS Expression Divide by Zero Error

Question

I have a tablix box that has a division expression. When dividing by zero or nulls I get #Error displayed in my report. I tried to create an IIF statement and tested with static values. This verified my syntax was correct but I still see the error on my reports.

=IIF(Sum(Fields!CY_Dollars.Value)=0, 0, (Sum(Fields!CY_Dollars.Value) - Sum(Fields!PY_Dollars.Value))/(Sum(Fields!PY_Dollars.Value)))

So I'm taking Current year dollars, subtracting Previous year dollars, and dividing that total by previous year dollars to get the percentage change. Is there a trick to this that I'm not getting?!

Antidote

You mentioned that there are nulls in your data? Try nesting each field call in a NULL replacement function. For example, I would the format IIF(Fields!PY_Dollars.Value = Nothing, 0, Fields!PY_Dollars.Value = Nothing) for each Fields!PY_Dollars.Value in the current formula. Better yet, handle the nulls in the source query instead. – Christophany

The condition in your IIF statement is not checking for zero denominator (you are checking for zero in CY, not PY value). Instead, try: =IIF(Sum(Fields!PY_Dollars.Value)=0, 0, (Sum(Fields!CY_Dollars.Value) - Sum(Fields!PY_Dollars.Value))/(Sum(Fields!PY_Dollars.Value))) – Boole

Answer 1

You can add a function to your report code that handles the divide by zero condition, this makes it a bit easier to implement in multiple cells, e.g.

Public Function Divider (ByVal Dividend As Double, ByVal Divisor As Double)
If IsNothing(Divisor) Or Divisor = 0
  Return 0
Else
  Return Dividend/Divisor
End If
End Function 

You can then call this in a cell like so:

=Code.Divider(Fields!FieldA.Value, Fields!FieldB.Value)

Eagle

can we use Code.Divider() custom function in SQL or MDX within dataset ? Silly but I want something like this to avoid multiline IIF expressions in my huge MDX :( – Masqat

Mi Hero!! This worked out excellent! I didn't really know how to add a function to a report before... so I went but it is sooo easy! 1. In Design view, right-click the design surface outside the border of the report and click Report Properties. 2. Click Code. 3. In Custom code, type the code msdn.microsoft.com/en-us/library/ms156028.aspx – Wig

Why no Float type in ssrs? Getting "There is an error on line n of custom code: [BC30002] Type 'Float' is not defined." – Identical

@GaryThomann handling the dividend isn't necessary as zero or null divided by anything just returns zero, not an error. – Eagle

Greetings from the future! It's 2021 and publishing SSRS reports to PowerBI via the API throws an error when you have shared code. I am right now unwinding a bunch of DivideBy calls to get this to work. – Herculaneum

Hi Nick, is that by design or due to a bug in the API? – Eagle

It seems like a bug because these reports can be published manually from the SSRS editor and run fine inside Power BI. – Herculaneum

So... if anyone has to refactor their shared divide code out, this is an inline method that actually works sqldusty.com/2011/08/01/… . Basically IIF(denominator=0,0,Numerator) / IIF(Denominator=0,1,Denominator) – Herculaneum

Answer 2

I suspect you're running into the issue where SSRS doesn't actually short circuit an IIF statement; even though you're checking for 0, you're still going to hit a divide by zero error.

Try something like:

=IIf(Sum(Fields!PY_Dollars.Value) = 0, 0, Sum(Fields!CY_Dollars.Value) - Sum(Fields!PY_Dollars.Value))
  / IIf(Sum(Fields!PY_Dollars.Value) = 0, 1, Sum(Fields!PY_Dollars.Value))

Using two IIf statements means you avoid the error by using the equation 0/1 when Sum(Fields!PY_Dollars.Value) = 0, thus just returning 0.

Also note that the above expression is checking Sum(Fields!PY_Dollars.Value) = 0, but yours is checking Sum(Fields!CY_Dollars.Value) = 0 - the denominator needs the zero check here.

Cralg

I understand the logic and it should work but SSRS doens't calculate it correctly. I still get the error with this. – Antidote

+1. Here is a post on using double iif statements ntsblog.homedev.com.au/index.php/2010/03/12/… – Anglesite

Answer 3

To avoid the overhead of maintaining code, the solution below feels tidiest to me. It avoids the division by zero in the denominator by adding the smallest possible Double value to it (Double.Epsilon, which is 4.94e-324). This value's way too small to affect the value of the fraction for anything people would use SSRS for. It also avoids nesting IIF functions.

=IIF(Sum(Fields!Denominator.Value)>0, 
     Sum(Fields!Numerator.Value)/(Sum(Fields!Denominator.Value)+Double.Epsilon),
     nothing)

Thermolabile

This is the simplest solution. In fact, by adding the Double.Epsilon to the denominator, you don't even need the iif statement anymore. – Matronage

This has really saved my Friday. Thank you so much for sharing this. – Complicate

Answer 4

Why not just express the values as DOUBLE? This avoids the multiple IIF functions? It should have better performance too.

=IIF(Sum(CDbl(Fields!CY_Dollars.Value))=0, 0, (Sum(CDbl(Fields!CY_Dollars.Value)) - 
    Sum(CDbl(Fields!PY_Dollars.Value)))/(Sum(CDbl(Fields!PY_Dollars.Value))))

Prepay