none
Erroneous Division by Zero Error?

    Question

  • Can someone explain why I am receiving this error...

    'The Value expression for the textbox 'textbox2' contains an error: Attempted to divide by zero.'

    on this expression...

    =IIF(First(Fields!V2.Value, "MyDataSet") <> 0, Last(Fields!V2.Value, "MyDataSet")/First(Fields!V2.Value, "MyDataSet"), 0) 

    In this case, both values are equal to 0 but  the check should avoid the actual division when the divisor is 0, should it not?

    Thanks in advance for your assistance.

    Anthony Sullivan
    Wednesday, February 18, 2009 7:22 PM

Answers

  • Please note that iif() is a function and therefore all parameters will be evaluated before calling the function. One solution has been posted by Scott. A working solution for A/B using only iif is:

    =iif(B=0, 0, A / iif(B=0, 1, B))

    hth,
    Gerald

    see also this thread: attempt to divide by zero
    Thursday, February 19, 2009 8:32 AM
  • Hi Anthony,

    * the following is for SSRS 2005.

    In theory you are right... your check for 0 should prevent the divisor statement from being evaluated.

    However, (and the following might need to be confirmed)... I believe in SSRS it parses and checks the expression from Right -> Left. 

    This means that it reads and checks the following: 

        Last(Fields!V2.Value, "MyDataSet")/First(Fields!V2.Value, "MyDataSet")

    Before checking your condition:

        First(Fields!V2.Value, "MyDataSet") <> 0

    And thus generates the error before even hitting your "check for 0" condition.


    I found the best way around this was to use a custom code function requiring 2 steps:

    1st) Create a custom code function.
    - go to the menu option "Report -> Report Properties"
    - go to the "Code tab.
    - add code similar to the following in the "Custom Code" section:

    Public Shared Function VarPercent(ByVal ValueA As Decimal, ByVal ValueB As Decimal) As Decimal
      If (ValueB = 0 OR ValueA = 0) Then
        Return 0
      End If
        Return (ValueA / ValueB )
    End Function


    2nd) Use the custom code function in the report.
    - Use the following expression in fields where you're doing divisons (following is for a % field):

    =IIF(IsNothing(Fields!MyValueA.Value),0,
      IIF(IsNothing(Fields!MyValueB.Value),0,
        code.VarPercent(Fields!MyValueA.Value,Fields!MyValueB.Value)
      )
    )

    The above samples A) checks for non-null values in the expression setting nulls to 0 otherwise B) it calls the function that checks for zeros and performs the divison if no zeros exist.

    Yes, it is extra work for what should initially calculate correctly if it wasn't for the way SSRS checks/parses expressions, but once you have the above setup it is rather easy to use and cut and copy into other places.

    Hope this helps.
    Scott
    Thursday, February 19, 2009 1:11 AM

All replies

  • For some reason, in my browser your expression just shows up as a scroll bar, so I'll paste it here:

    =IIF(First(Fields!V2.Value, "MyDataSet") <> 0, Last(Fields!V2.Value, "MyDataSet")/First(Fields!V2.Value, "MyDataSet"), 0) 

    Can I ask what version of RS you are running, and where you are viewing the report when it generates that error (In Report Builder, in Report Designer (VS), or on the Reporting Server)?  I tried reproducing this in Report Builder and the viewer correctly handles this case.

    Thanks,

    Paul
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, February 18, 2009 8:33 PM
  • I'm developing in VS 2005.

    This is my RS version.

    Microsoft SQL Server Reporting Services Designers
    Version 9.00.1399.00

    I see this error when I select the preview tab, enter the parameters for the report and click 'View Report'.

    Thanks for your response.

    Anthony
    Wednesday, February 18, 2009 8:47 PM
  • Hi Anthony,

    * the following is for SSRS 2005.

    In theory you are right... your check for 0 should prevent the divisor statement from being evaluated.

    However, (and the following might need to be confirmed)... I believe in SSRS it parses and checks the expression from Right -> Left. 

    This means that it reads and checks the following: 

        Last(Fields!V2.Value, "MyDataSet")/First(Fields!V2.Value, "MyDataSet")

    Before checking your condition:

        First(Fields!V2.Value, "MyDataSet") <> 0

    And thus generates the error before even hitting your "check for 0" condition.


    I found the best way around this was to use a custom code function requiring 2 steps:

    1st) Create a custom code function.
    - go to the menu option "Report -> Report Properties"
    - go to the "Code tab.
    - add code similar to the following in the "Custom Code" section:

    Public Shared Function VarPercent(ByVal ValueA As Decimal, ByVal ValueB As Decimal) As Decimal
      If (ValueB = 0 OR ValueA = 0) Then
        Return 0
      End If
        Return (ValueA / ValueB )
    End Function


    2nd) Use the custom code function in the report.
    - Use the following expression in fields where you're doing divisons (following is for a % field):

    =IIF(IsNothing(Fields!MyValueA.Value),0,
      IIF(IsNothing(Fields!MyValueB.Value),0,
        code.VarPercent(Fields!MyValueA.Value,Fields!MyValueB.Value)
      )
    )

    The above samples A) checks for non-null values in the expression setting nulls to 0 otherwise B) it calls the function that checks for zeros and performs the divison if no zeros exist.

    Yes, it is extra work for what should initially calculate correctly if it wasn't for the way SSRS checks/parses expressions, but once you have the above setup it is rather easy to use and cut and copy into other places.

    Hope this helps.
    Scott
    Thursday, February 19, 2009 1:11 AM
  • Please note that iif() is a function and therefore all parameters will be evaluated before calling the function. One solution has been posted by Scott. A working solution for A/B using only iif is:

    =iif(B=0, 0, A / iif(B=0, 1, B))

    hth,
    Gerald

    see also this thread: attempt to divide by zero
    Thursday, February 19, 2009 8:32 AM
  • Please note that iif() is a function and therefore all parameters will be evaluated before calling the function. One solution has been posted by Scott. A working solution for A/B using only iif is:

    =iif(B=0, 0, A / iif(B=0, 1, B))

    hth,
    Gerald

    see also this thread: attempt to divide by zero


    My case was similar to this and I had to do something slightly more.

    I'm using SSRS 2005 at this momen, if and in the above scenario quoted by Gerald, I'm passing a DECIMAL(18,2) for both my "A" and "B" values.  Because of that, I did the following to make my report not throw and #ERROR, but instead, a zero.

    =iif(B = 0, 0, iif(A = 0, 0, A) / iif(B = 0, 0, B))

    For some reason, SSRS (2005 anyway) does not like a divide by zero with any decimals anywhere in the equation, it seems, which is why (I guess) I'm technically changing the "A" decimal to an int???

    Don't know, but glad it works.  ;-)

    Monday, September 13, 2010 4:31 PM
  • Please note that iif() is a function and therefore all parameters will be evaluated before calling the function. One solution has been posted by Scott. A working solution for A/B using only iif is:

    =iif(B=0, 0, A / iif(B=0, 1, B))

    hth,
    Gerald

    see also this thread: attempt to divide by zero


    My case was similar to this and I had to do something slightly more.

    I'm using SSRS 2005 at this momen, if and in the above scenario quoted by Gerald, I'm passing a DECIMAL(18,2) for both my "A" and "B" values.  Because of that, I did the following to make my report not throw and #ERROR, but instead, a zero.

    =iif(B = 0, 0, iif(A = 0, 0, A) / iif(B = 0, 0, B))

    For some reason, SSRS (2005 anyway) does not like a divide by zero with any decimals anywhere in the equation, it seems, which is why (I guess) I'm technically changing the "A" decimal to an int???

    Don't know, but glad it works.  ;-)


    Ran into even another snafu with this.  So this is what I had to use.

    =iif(B = 0, 0, iif(B = 0, 0, A) / iif(B = 0, 0, B))

    Now this works for all the cases I've run into so far.  :-D

    Monday, September 13, 2010 4:52 PM
  • I think the cause of that ERROR would have been because both A and B were decimals but your zeros and 1 were integers.

    I presume the following (using Gerald's example) should work because you're always using the same data types:

    =iif(B=0.0, 0.0, A / iif(B = 0.0, 1.0, B))

    Cheers.

     

    Friday, October 08, 2010 4:06 AM
  • =iif(B=0, 0, A / iif(B=0, 1, B)) this helped me.

    Thanks



    Saturday, March 01, 2014 3:53 AM