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.