0

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!!!

Kim Jones
  • 123
  • 1
  • 6
  • 15

1 Answers1

0

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.

stubaker
  • 1,928
  • 1
  • 23
  • 25
  • thanks! I got it to not throw an error on the syntax, but I'm still getting #error as the value and the following upon clicking on Preview: "(rsRuntimeErrorInExpression): the Value expression for the textrun Textbox 12 contains an error: Attempt to divide by zero". ? – Kim Jones Dec 24 '13 at 17:25
  • I actually created the Public Function noted in answer #4 on this post: [link](http://stackoverflow.com/questions/10432714/divide-by-zero-null-workaround-in-ssrs-2008-report?rq=1) and it works great. :) – Kim Jones Dec 24 '13 at 19:40