none
Divide by Zero Error Test by Datatype RRS feed

  • Question

  • Hello,

    I'm using Visual Studio 2005/SQL Server 2005. While building a report I kept getting a "divide by zero" error even though I was checking for that using an iif statement. Via these message boards I learned that SSRS evaluates the equations within an iif statement first before it evaluates the iif clause. One suggestion, which I am now using to prevent the divide by zero error, is what I refer to as a "double iif". Here's an example:

    Divide by zero error when Hours = 0:   =iif(Fields!Hours.Value > 0, Fields!NetSales.Value/Fields!Hours.Value, 0)

    Revised: =iif(Fields!Hours.Value > 0, Fields!NetSales.Value / iif(Fields!Hours.Value > 0, Fields!Hours.Value, 1), 0)

    With that said, a co-worker stated he doesn't use a "double iif", or any other workaround, and he doesn't get a divide by zero error. We concluded that the difference in our code is that he usually uses integer datatypes and in this case I was using a decimal datatype. Therefore, it appears the divide by zero error doesn't occur when using integer datatypes. But, why?

    I ran a test and found that in SSRS dividing by zero as an integer datatype produces an erroneous large number. I know this as "Infinity" is displayed on my report instead of "#Error" which is displayed when dividing by zero as a decimal datatype and the intended divide by zero error. The only conclusion I can come to is this is a bug in SSRS.

    Does anyone know why dividing by zero as an integer produces an incorrect result and/or if there has been or will be a fix implemented for this?

    Thanks, teledebbie

     

    Tuesday, December 7, 2010 6:00 PM

Answers

  • I have always found it convenient to use a custom code for divide by zero checks as you dont have to mess up your expressions and make it unreadable with long iif statements. Incidentally, I found a comment also in the header (which is given below) which hints about some bug in 2005. Never noticed it as I would have still used the custom code if the bug wasnt there, much more convenient

    1) Add the function below in the Code sectio nof the report. (right click on report body outside the white area, select properties and go to Code tab)

      Public Function Divide(ByVal a As Double, ByVal b As Double)
            'this is used due to a bug in ssrs 2005.
            'the bug occurs when you try to divide a field by a field in a different dataset.
            'ssrs performs the calculation prior to any check for zero.  thus an error is thrown.

            Dim result As Double

            If b = 0 Then
                result = Nothing
            Else
                result = a / b
            End If

            Return result

        End Function

    2) Instead of the iif statement, just use the expression

    =Code.Divide(Fields!NetSales.Value,Fields!Hours.Value)


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

     

    • Marked as answer by teledebbie Wednesday, December 8, 2010 3:05 PM
    Wednesday, December 8, 2010 12:28 PM
    Moderator

All replies

  • As your expressions aren't referencing anything at a group/aggregate level, you would be better off handling this in your data source. Assuming you are using a sql source. This will be faster than evaluating it in the report layer. Add an additional column to your query as:

    CASE 
     WHEN [Hours] > 0 THEN NetSales/[Hours] 
     ELSE 0 
    END AS MyNewColumn,
    

    My Blog "Karl Beran's BI Mumble"
    Wednesday, December 8, 2010 10:57 AM
  • I have always found it convenient to use a custom code for divide by zero checks as you dont have to mess up your expressions and make it unreadable with long iif statements. Incidentally, I found a comment also in the header (which is given below) which hints about some bug in 2005. Never noticed it as I would have still used the custom code if the bug wasnt there, much more convenient

    1) Add the function below in the Code sectio nof the report. (right click on report body outside the white area, select properties and go to Code tab)

      Public Function Divide(ByVal a As Double, ByVal b As Double)
            'this is used due to a bug in ssrs 2005.
            'the bug occurs when you try to divide a field by a field in a different dataset.
            'ssrs performs the calculation prior to any check for zero.  thus an error is thrown.

            Dim result As Double

            If b = 0 Then
                result = Nothing
            Else
                result = a / b
            End If

            Return result

        End Function

    2) Instead of the iif statement, just use the expression

    =Code.Divide(Fields!NetSales.Value,Fields!Hours.Value)


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

     

    • Marked as answer by teledebbie Wednesday, December 8, 2010 3:05 PM
    Wednesday, December 8, 2010 12:28 PM
    Moderator
  • Thanks Jason! This works for me and will be a much more convenient workaround than using multiple iif statements.

    I would still like to understand why dividing by zero as an integer, in SSRS, doesn't produce a divide by zero error but rather an erroneous large number. Any thoughts on that specifically?

    Wednesday, December 8, 2010 3:09 PM
  •  

    Thanks Karl. I am using a SQL data source and I understand that it would make sense to handle most of the calculations at that level however I'm using a stored procedure created by someone else (and I'm fairly new to this) so I need to avoid modifications to that data source. Also, I am using expresions at the group/aggregate level, which I'm sure can also be done at the data source level, I just didn't reference those in my post.

    Any thoughts specifically on why SSRS, when dividing by zero as an integer, produces an actual (although erroneous) result instead of a divide by error?

    Thanks again.

    Wednesday, December 8, 2010 3:25 PM
  • Fair point if you are using a stored proc that may have other dependencies. Actually if you need this calculation for an hourly rate at an aggregate level (say by Person or Customer) you would need the expression in SSRS, as aggregating the percentage calculations (if created in t-SQL) would be different to calculating the percentage of the aggregates -as Σ(NetSales/Hours) is not the same as ΣNetSales/ΣHours
    My Blog "Karl Beran's BI Mumble"
    Thursday, December 9, 2010 1:09 PM
  • hi teledebbie,

    Use this

     

    =iif(Fields!Hours.Value = 0, 0,Fields!NetSales.Value/Fields!Hours.Value)

    hope this will help

     

     

    Amit

    Mark as answer if found useful

    Wednesday, December 15, 2010 2:05 PM