End of Amnesia (Avoiding Divide By Zero Errors)


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 (19)

  1. Anonymous says:

    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 :)

  2. bwelcker says:

    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.

  3. bwelcker says:

    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.

  4. furmangg says:

    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.

  5. B.J. says:

    Perfect.  This confirms my suspicions and keeps me from fumbling for the answer.  Blogs + Search = Love.

  6. MLee says:

    So it wasn’t just me doinbg something stupid then? I feel a whole lot better now. God bless google! Thanks Brian.

  7. John says:

    Thanks again. I wish I’d done a better search an hour ago!

  8. vraven says:

    Thanks a lot!!! This worked like a charm. I was getting really frustrated because it didn’t make sense what it was doing.

  9. itgalary says:

    A True Tenary operator is come with Orcas. View the article about the old IIF and New IF in VB 2008

    http://www.itgalary.com/forum_posts.asp?TID=586

  10. north2light says:

    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 …

  11. Glad this is still out here.  Saved me a ton of time.  Appreciate it!

  12. Sonny Pawchuk says:

    Props… This posts resolved a lot of anger :)!!!

  13. Sonny Pawchuk says:

    Props… This post resolved a lot of anger :)!!!

  14. Awais says:

    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

  15. =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))

  16. Gash says:

    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")))

  17. Valentino Vranken says:

    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).

  18. faiza says:

    million thanks! been searching for hours!..thanks.

  19. Much needed article to avoid confusion with "IIF" , Thanks !!!