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.

Nathan Griffiths · Answer 1 · 2012-05-04T02:19:02.410

26

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)

edited May 04 '12 at 02:19

answered May 04 '12 at 01:28

Nathan Griffiths

12,277
2
34
51

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. – BilliD Feb 24 '17 at 14:37

score 16 · Accepted Answer · edited May 23 '17 at 12:17

16

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.

edited May 23 '17 at 12:17

Community

1
1

answered May 03 '12 at 16:39

Jamie F

23,189
5
61
77

1

It may not apply here, but what about cases where the divisor can be negative? – Homer Jul 10 '13 at 14:52
2

Replace both occurences of `> 0` with `<> 0` and you should be fine for negative divisors. – Jamie F Jul 10 '13 at 15:06

score 1 · Answer 3 · answered May 03 '12 at 15:13

1

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:

enter image description here

Your problem is probably somewhere else

answered May 03 '12 at 15:13

Diego

34,802
21
91
134

score 1 · Answer 4 · edited Jan 30 '13 at 14:39

1

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.

edited Jan 30 '13 at 14:39

strah

6,702
4
33
45

answered Jan 30 '13 at 14:20

TheBigYinJames

11
1

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. – workabyte Sep 21 '15 at 22:30

score 0 · Answer 5 · answered Nov 14 '22 at 10:51

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

divide by zero/null workaround in SSRS 2008 report

5 Answers5

Linked