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?!

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

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

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

Comments

[Eric Hauenstein]

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.

[rpyzh]

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))‌​)

[d90]

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

[Aditya]

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 :(

[Nathan Griffiths]

This function can only be used in the report design surface, not in a SQL or MDX query. There are a number of approaches for handling divide-by-zero calculations in MDX, e.g. bidn.com/blogs/dustinryan/ssis/465/…, but if you have SQL Server 2012 or later you can use the new Divide() function which does a similar job to the above code.

[Aditya]

Divide function works like what I want. Thanks alot @Nathan. +1 to comment :)

[Bobby]

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

[Emil]

dont you have to define a return type? Is it not necessary?

[LittleTreeX]

I wish I could upvote this like 1000 times. Thanks man, perfect solution.

[Gus]

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

[rodrigo.rodriguez]

´help me in my problem =Fields!Saldo.Value*100/Fields!CostoNeto.Value´

[Gary Thomann]

Great. But you want to hit the Dividend as well with the checks. ie: either the Dividend or Divisor are null or zero then return zero

[Gary Thomann]

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

[Nathan Griffiths]

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

[Nick.McDermaid]

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.

[Nathan Griffiths]

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

[Nick.McDermaid]

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

[Nick.McDermaid]

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)