Need help correcting a divide by zero error

0
votes

In one of the reports we are getting a divide by zero error. What we want is if the Prior Year value is 0 then Percent Difference should be 100%. I’ve tried all kinds of ways to get this to work with no success.

Below is the script that is causing the problem. Do you know how to correct this?

=Iif(Sum(Fields!LYSUMAMOUNT.Value, "Group3") <> 0, ((Sum(Fields!SUMAMOUNT.Value, "Group3") - Sum(Fields!LYSUMAMOUNT.Value, "Group3")) / Sum(Fields!LYSUMAMOUNT.Value, "Group3")) * 100, 0)

Thank you in advance!

1
sql-serverssrs-2008

1 Answers

1
votes

This is a bug in SSRS or the way Microsoft implemented IIF evaluation. It tries to evaluate both true and false expression irrespective of IIF condition. You can try as below to resolve your issue. Here I have added IIF condition to division as well and if it is zero use 1 as divisor. 

=Iif(Sum(Fields!LYSUMAMOUNT.Value, "Group3") <> 0, ((Sum(Fields!SUMAMOUNT.Value, "Group3") - Sum(Fields!LYSUMAMOUNT.Value, "Group3")) / Iif(Sum(Fields!LYSUMAMOUNT.Value, "Group3") <> 0, Sum(Fields!LYSUMAMOUNT.Value, "Group3"),1)) * 100, 0)