SSRS 2008 divide by zero error and “NaN” value

Question

hoping to get some help here. I have a report that shows 4 fields: current YTD sales, previous YTD sales, the difference between the 2 in dollars, and the difference between the 2 in percent. I'm running into a divide by 0 error and the value of "NaN" as the value for the percent field. I get the divide by 0 error when I have a value in the current YTD ("OrderInfoConstruction") but 0 in the previous YTD ("OrderInfoClosedConstruction"), since my expression for the % field is:

=(Sum(Fields!PRICE_EXT.Value, "OrderInfoConstruction") - 
  Sum(Fields!PRICE_EXT.Value, "OrderInfoClosedConstruction")) / 
  Sum(Fields!PRICE_EXT.Value, "OrderInfoClosedConstruction")

and the value of "Sum(Fields!PRICE_EXT.Value, "OrderInfoClosedConstruction") is 0 (the previous YTD value). For the NaN value issue, it's the same expression, but in this case, BOTH current and previous YTD's are 0. How can I have it NOT divide if the value is 0 to solve the divide by 0 error and what is a NaN and how can I have it just show "0" instead? I've found some help on this but have NO idea how to take the IIF statement below and adapt it for my statement above?

=IIf(Fields!SomeField.Value = 0, 0, Fields!SomeOtherField.Value / IIf(Fields!SomeField.Value = 0, 1, Fields!SomeField.Value))

thanks in advance for the help!!!

stubaker stubaker · Accepted Answer · 2013-12-24T04:06:32

If you want to display 0 for both 0/0 and #/0, you just need to check the denominator value for zero. Basically IIf(PrevYTD = 0, 0, (CurrYTD - PrevYTD) / PrevYTD), or with your actual fields:

=IIf(Sum(Fields!PRICE_EXT.Value, "OrderInfoClosedConstruction") = 0, 0, 
  (Sum(Fields!PRICE_EXT.Value, "OrderInfoConstruction") - 
  Sum(Fields!PRICE_EXT.Value, "OrderInfoClosedConstruction")) / 
  Sum(Fields!PRICE_EXT.Value, "OrderInfoClosedConstruction"))

Also, NaN stands for not a number, and 0/0 is one operation that produces it.

SSRS 2008 divide by zero error and “NaN” value

1 Answers