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.

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.

Answer 2

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.

Answer 3

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)

Answer 4

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