SSRS divide by zero error expression

Question

Hope your well. I am working on a report and seem to get a #error. Seems like it is a divide by zero error but I can not work out a solution. The expression:

=( Sum(Fields!Line_Sell.Value) - Sum(Fields!Line_Cost.Value) ) / Sum(Fields!Line_Sell.Value)

I am relatively new to RS but have tried

ISNULL( )

but with no success.

Any help, I would be greatful.

Thanks

Answer 1

Let's say you have an expression set to x / y, where y has the potential to be zero. As you experienced, SSRS will change this value to #ERR (or sometimes NaN). Your first instinct would be to test for zero

=Iif(y = 0, 0, x/y)

Unfortunately, this can result in a debugger warning that says you are attempting to divide by zero. This is because SSRS attempts to evaluate the entire statement during compilation. As a result, we need to provide SSRS with a value that it can evaluate

=x * y ^ -1

This is the same as dividing by your y value, but SSRS sees this as dividing by infinity, and therefore can make an evaluation. As a final step, you will want to make sure your code can properly export to Excel. Under 2012 SSRS (and possibly later, I haven't tested), SSRS has an issue where possible zero values export to Excel as 0.000000000000000 and cause loss of data errors. To correct this, we explicitly output the value of zero

=Iif(x = 0 OR y = 0, 0, x * y ^ -1)

Answer 2

The expression in your comment doesn't look complete, so I can't tell what you tried next that didn't work. You definitely want to test for zero before performing division.

You can try this:

=iif( Sum(Fields!Line_Sell.Value) = 0, 0, (Sum(Fields!Line_Sell.Value) - Sum(Fields!Line_Cost.Value)) / Sum(Fields!Line_Sell.Value))

Or to check for an empty value also, you can try this:

=iif( (Sum(Fields!Line_Sell.Value) = 0 or IsNothing(Sum(Fields!Line_Sell.Value)), 0,Sum(Fields!Line_Sell.Value) - Sum(Fields!Line_Cost.Value)) / Sum(Fields!Line_Sell.Value))

If that's still not yielding results, break down the expression and run the report. Try Sum(Fields!Line_Sell.Value) by itself, run the report and see what you get. To be more efficient create a table with one column for Fields!Line_Sell.Value and another for Fields!Line_Cost.Value. In the table, include detail rows and add a total row so that you get the Sum function with each of those fields individually. You need to look at the detail records to try to extrapolate why the aggregate function isn't working. Bottom line - decompose your expression and test it piece by piece. The answer is in there somewhere.