SSRS Divided by zero error

Question

My table contains 2 columns with numbers:

ORIGINAL_QTY    FINAL_QTY
120               115
1                 0
1                 1
0                 0
3                 1
4                 5

And I need to extract the diference between both numbers as a percentage, so I created the following expresion, taking into account increase o decrease in final quantity (swaping numerator and denominator) or 0 in denominator (changing 0 with 0.00001 will do the job because I round to 2 decimals):

=IIf(Fields!ORIGINAL_QTY.Value=0, (Fields!FINAL_QTY.Value/0.00001)*100, IIf(Fields!ORIGINAL_QTY.Value>=Fields!FINAL_QTY.Value, (Fields!FINAL_QTY.Value/Fields!ORIGINAL_QTY.Value)*100, (Fields!ORIGINAL_QTY.Value/Fields!FINAL_QTY.Value)*100))

But still throws me "error: divided by zero" on rows 2 and 4:

Row 2: Its just 0/1=0, nothing special, shouldn't throw error. Row 4: ORIGINAL_QTY = 0, but I took care of it in the expresion so...

What am I missing? Checked everything and it should work...

If 0 is ever in the denominator you are dividing by zero. In math, you can't divide by 0. That's why it doesn't work. If you are expecting that 0 may be in the denominator, then you have to handle that and not divide in that situation. — Lexi, Oct 24 '17 at 13:21
Possible duplicate of [divide by zero/null workaround in SSRS 2008 report](https://stackoverflow.com/questions/10432714/divide-by-zero-null-workaround-in-ssrs-2008-report) — Jonathon Ogden, Oct 24 '17 at 13:22
But 0 is never in the denominator... that's why I use the expresion, to do not allow that operation... — Piston, Oct 24 '17 at 13:23
@Piston SSRS does not short-circuit, so it is still trying to evaluate 1/0. — C Black, Oct 24 '17 at 13:23
God... I spent hours trying to figure this out and its just "bad-design". Thank you. — Piston, Oct 24 '17 at 13:34

score 1 · Accepted Answer · answered Oct 24 '17 at 21:36

Alternatively, you could use the following custom code in your report and then call it in your report (right click report, report Properties - > Code

Public Shared Function VarPercent(ByVal Standard As Decimal, ByVal Actual As Decimal) As Decimal
If Actual = 0 Then
Return 0
End If
If Standard = 0 Then
Return 0
End If
Return (Standard / Actual )
End Function

Then you can call it like this in your report (where ever you are dividing)

Code.VarPercent (Fields!numerator.Value, Fields!denominator.Value)

SSRS Divided by zero error

1 Answers1