Udostępnij za pośrednictwem

End of Amnesia (Avoiding Divide By Zero Errors)

  • Artykuł

People often ask how to avoid divide by zero problems in their Reporting Services reports. Let's say you have a textbox that calculates profit margin via the expression:

=Fields!Price.Value / Fields!Cost.Value

This works fine unless a value in the Cost field is zero (and is not an integer or double), in which case you will get '#error' displayed in your report. This is because a divide by zero with a decimal field will throw an exception. This exception is caught by the report processing engine and #error is displayed. If you would rather have a string like "N/A" instead of #error, you might think about creating a Reporting Services expression using the IIf function:

=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / Fields!Cost.Value)

But when you preview the report, you still see #error in your report. What gives?

If you take a look at the IIf function description in the Visual Basic documentation, you will see the following:

As part of preparing the argument list for the call to IIf, the Visual Basic compiler calls every function in every expression. This means that you cannot rely on a particular function not being called if the other argument is selected by Expression.

This means that even through the value for cost is zero, the error will still be generated. So how do you work around this? You have to force the expression evaluation to avoid the division with a nested IIf:

=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / IIf(Fields!Cost.Value = 0, 1, Fields!Cost.Value))

The nested IIf is evaluated first so that the divide by zero can be avoided but is not used by the outer expression if it is zero.

As an interesting side note, the Arithemtic Operators topic in the Visual Basic documentation seems to say that a DivideByZeroException is thrown for dividing by anything but the Double data type. I have not found this to be the case. Try this simple application in VB:

Dim a, b As Integer
a = 1
b = 0
MsgBox (a / b)

You will get "Infinity", despite what the documentation says. Anyway, the use of the expression above in your report will ensure that you will get the desired results, no matter what data type you are using.

Comments

  • Anonymous
    September 26, 2006
    Impossible, infinity can not be stored in an integer. The reason is that Dim a, b as Integer does not do what you expect. It declares a as Variant and b as Integer :)
  • Anonymous
    September 26, 2006
    Actually, I now realize why I get infinity instead of an exception when dividing integers. There is an implicit cast to a double for the result of integer division.
  • Anonymous
    September 28, 2006
    Actually, "Dim a, b as Integer" does not declare a as Variant. If you do this:
           Dim a, b As Integer
           MsgBox(a.GetType().FullName)
    You will get System.Int32.

    The reason for the Infinity is what I stated in the next comment, there is an implicit cast to a double for integer division.
  • Anonymous
    September 29, 2006
    I end up implementing a little VB function in the Code tab of every report called SafeDivision. It would be great if you folks could just add that to the SSRS object model as a standard function.
  • Anonymous
    October 11, 2006
    Perfect.  This confirms my suspicions and keeps me from fumbling for the answer.  Blogs + Search = Love.
  • Anonymous
    October 31, 2006
    The comment has been removed
  • Anonymous
    March 05, 2007
    Thanks again. I wish I'd done a better search an hour ago!
  • Anonymous
    June 14, 2007
    Thanks a lot!!! This worked like a charm. I was getting really frustrated because it didn't make sense what it was doing.
  • Anonymous
    September 05, 2007
    A True Tenary operator is come with Orcas. View the article about the old IIF and New IF in VB 2008http://www.itgalary.com/forum_posts.asp?TID=586
  • Anonymous
    September 28, 2007
    The nested IIF worked very well, however this construct is very non-intuitive. I was going round and round on avoiding the #error. Seems like this should be the default behavior:=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / Fields!Cost.Value)Otherwise, it's off to google ...
  • Anonymous
    March 23, 2011
    Glad this is still out here.  Saved me a ton of time.  Appreciate it!
  • Anonymous
    April 15, 2011
    Props... This posts resolved a lot of anger :)!!!
  • Anonymous
    April 15, 2011
    Props... This post resolved a lot of anger :)!!!
  • Anonymous
    May 22, 2011
    Thanks a lot!!! This worked like a charm. I was getting really frustrated because it didn't make sense what it was doing. http://www.oddarena.com
  • Anonymous
    May 25, 2012
    =IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / Fields!Cost.Value)Change to=IIf(Fields!Cost.Value = 0, 0,( Fields!Price.Value / Fields!Cost.Value))
  • Anonymous
    August 15, 2012
    still does not work=IIf(Sum(Fields!HOURS.Value,"Group1") = 0.00, "N/A", FormatPercent(Sum(Fields!HOURS.Value, "Details")/Sum(Fields!HOURS.Value,"Group1")))
  • Anonymous
    January 15, 2013
    furmangg: "I end up implementing a little VB function in the Code tab of every report called SafeDivision. It would be great if you folks could just add that to the SSRS object model as a standard function."+1!Something like this would do it: SafeDivision(<division expression>, <return string in case of div by zero>)That would make it flexible enough so that we can still take action in case of div by zero (or just display a "Div by zero" msg if that's what's required).
  • Anonymous
    July 01, 2013
    million thanks! been searching for hours!..thanks.
  • Anonymous
    October 10, 2013
    Much needed article to avoid confusion with "IIF" , Thanks !!!