Answered by:
Divide by zero error
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))
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.ValueReportItems!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
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

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 zerodivisor 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 YuQiuyun Yu
TechNet Community Support 



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 YuQiuyun Yu
TechNet Community Support 
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 builtin 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 YuQiuyun Yu
TechNet Community Support 
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.ValueReportItems!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

