divide by zero/null workaround in SSRS 2008 report

Question

I have a report with a field whose value was the expression:

Fields!TotalPrice.Value/Fields!TotalSlots.Value

Although sometimes TotalSlots was blank and thus I was getting a divide by zero runtime error. So I changed the expression to this:

=IIF(Fields!TotalSlots.Value > 0, Fields!TotalPrice.Value/Fields!TotalSlots.Value,"unknown")

but I'm still getting a divide by zero error. How do I work around this zero divisor issue.

Raskin

Answer 1

The VB IIF evaluates all arguments, so it will throw an error if any argument throws an error:

Your formula can be written as:

=IIF(Fields!TotalSlots.Value > 0,
   Fields!TotalPrice.Value /
   IIF(Fields!TotalSlots.Value > 0,
       Fields!TotalSlots.Value,
       1 ),
   "unknown")

Then even when TotalSlots is zero, the formula still won't encounter a division problem.

Dumbstruck

It may not apply here, but what about cases where the divisor can be negative? – Goodsized

Replace both occurences of > 0 with <> 0 and you should be fine for negative divisors. – Dumbstruck

Answer 2

Jamie F's answer is correct. As a tip, you can add a function to your report code to make the division 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)

Melissiamelita

I'm receiving an error "Native compiler return value Function "Divider' doesn't return a value on all code paths, a null reference exception could occur at run time when the result is used" Any help is appreciated. I figured it out, I had to enter my own return characters after coping and pasting the code into SSRS. If anyone is having this same problem just make sure you code looks exactly like above in your expression window. – Elizebethelizondo

Answer 3

I don't think your error is on the calculation. First of all, SSRS deals automatically with this situation. See my third column. And the forth shows your expression:

Your problem is probably somewhere else

Climb

Answer 4

This only seems to happens when the division is one of the results of an IIF, not if you just write a formula to divide one by the other, e.g.

=IIF(thing=1,10/0,0)

Before it has evaluated thing, it has already tried to calculate both results, causing an error. You can't use IIF in this way to protect from zero, you have to put the IIF on the bottom line of the division, e.g.

=IIF(thing=1, 10/IIF(divisor=0,-99999999999,divisor),0)

This is not satisfactory, since we've introcudes a weird small non zero number as the result, but it may be ok if you just want a non-error.

Technically, the #error is the correct answer.

Rubenstein

i just made it 1 if 0 then let the main IIF replace the entire thing with 0 but this got me on track. though it was going to be something like this going on under the hood. – Ordovician

Answer 5

Function IIF(arg1, arg2, arg3) always calculates all arguments, before returns a result, so your 2nd argument Fields!TotalPrice.Value/Fields!TotalSlots.Value can return #Error.

Try to use IF(arg1, arg2, arg3) function instead IIF.

=IF(Fields!TotalSlots.Value > 0,
    Fields!TotalPrice.Value/Fields!TotalSlots.Value,
    "unknown")

Murrain