none
Divide by zero error RRS feed

  • Question

  • I have SSRS 2012 and have a report with an expression shown below.  I am getting an error of "[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox133.Paragraphs[0].TextRuns[0]’ contains an error: Attempted to divide by zero."

    Textbox 133 has an expression as shown below and I cannot figure out why I get the error because I thought the IIF would bypass divide by zero. Thanks.

    =IIF(Sum(Fields!PriorQty.Value) = 0.0, 1,(Sum(Fields!CurrentQty.Value) - Sum(Fields!PriorQty.Value)) / Sum(Fields!PriorQty.Value))

    Wednesday, April 15, 2015 7:15 PM

Answers

  • One more question.  Can I do the same calc using Texbox values instead?  I have totals of PriorQty and CurrentQty already on the report but not sure how to reference them. Thanks.

    You can reference them as

    (ReportItems!CurrentQTyTotal.Value-ReportItems!PriorQtyTotal.Value)/IIF(ReportItems!PriorQtyTotal.Value = 0 , 1, ReportItems!PriorQtyTotal.Value)

    Assuming names of textboxes as CurrentQtyTotal and PriorQtyTotal


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by David Chase89 Tuesday, April 21, 2015 10:15 PM
    Tuesday, April 21, 2015 12:23 PM

All replies

  • Hi David,

    Try to change cint(SUM(Fields!PriorQty.Value))=0

    so your expression will be look like below:-

    =IIF(cint(Sum(Fields!PriorQty.Value)) = 0, 1,(Sum(Fields!CurrentQty.Value) - Sum(Fields!PriorQty.Value)) / Sum(Fields!PriorQty.Value))

    Thanks

    Prasad

    Thursday, April 16, 2015 12:59 AM
  • Hi David,

    According to your description, when you use the IIF() function in the report, the error "[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox133.Paragraphs[0].TextRuns[0]’ contains an error: Attempted to divide by zero." is thrown out.

    In your scenario, the issue is caused by that the IIF() function always evaluates both the true part and the false part, even though it returns only one of them. To resolve the issue, you should use a nested IIF() function to avoid the zero-divisor in any rate like below:
    =IIF(Sum(Fields!PriorQty.Value) = 0, 0,(Sum(Fields!CurrentQty.Value) - Sum(Fields!PriorQty.Value)) / IIF(Sum(Fields!PriorQty.Value)=0,1,Sum(Fields!PriorQty.Value))

    For more information, please refer to this article: FAQ: Why does the “Attempted to divide by zero” error still happen?

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Thursday, April 16, 2015 6:11 AM
    Moderator
  • That got rid of the error but gave the wrong result.  When Sum(Fields!PriorQty) = 0 then it should return 1. If I change the first IIF to 1 for false it fails.
    Thursday, April 16, 2015 11:50 AM
  • Tried that and still fails.
    Thursday, April 16, 2015 11:52 AM
  • One more question.  Can I do the same calc using Texbox values instead?  I have totals of PriorQty and CurrentQty already on the report but not sure how to reference them. Thanks.
    Thursday, April 16, 2015 1:00 PM
  • That got rid of the error but gave the wrong result.  When Sum(Fields!PriorQty) = 0 then it should return 1. If I change the first IIF to 1 for false it fails.

    Please try to use the expression below:

    =(Sum(Fields!CurrentQty.Value) - Sum(Fields!PriorQty.Value) )/ IIF(Sum(Fields!PriorQty.Value)=0,1,Sum(Fields!PriorQty.Value))

    If issue persists, please share some sample data for our analysis.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Tuesday, April 21, 2015 12:14 PM
    Moderator
  • One more question.  Can I do the same calc using Texbox values instead?  I have totals of PriorQty and CurrentQty already on the report but not sure how to reference them. Thanks.

    Yes, you can use ReportItems built-in collection. Please refer to this article: ReportItems Collection References (Report Builder and SSRS).

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Tuesday, April 21, 2015 12:17 PM
    Moderator
  • One more question.  Can I do the same calc using Texbox values instead?  I have totals of PriorQty and CurrentQty already on the report but not sure how to reference them. Thanks.

    You can reference them as

    (ReportItems!CurrentQTyTotal.Value-ReportItems!PriorQtyTotal.Value)/IIF(ReportItems!PriorQtyTotal.Value = 0 , 1, ReportItems!PriorQtyTotal.Value)

    Assuming names of textboxes as CurrentQtyTotal and PriorQtyTotal


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by David Chase89 Tuesday, April 21, 2015 10:15 PM
    Tuesday, April 21, 2015 12:23 PM
  • Still not correct.  For example, if PriorQtyTotal = 0 and CurrentQtyTotal is -585 then it returns -58500.0% and it should return 100.0%.
    Tuesday, April 21, 2015 3:33 PM
  • Figured it out by adding another IIF at front e.g.

    IIF(ReportItems!CurrentQtyTotal.Value = 0,1,(ReportItems!CurrentQTyTotal.Value-ReportItems!PriorQtyTotal.Value)/IIF(ReportItems!PriorQtyTotal.Value = 0 , 1, ReportItems!PriorQtyTotal.Value))

    Tuesday, April 21, 2015 10:15 PM