December 31, 2015 at 6:49 am
Hi,
I have an expression setup to add a row of percentages that will equal a whole or 100%. If all the cells I'm adding happen to be 0%, I get a NaN value instead of 0% or 0.00%. The expression looks like this:
=CDBL(reportitems!Textbox33.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox34.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox35.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox36.Value) / CDBL(reportitems!Textbox103.Value)
I tried using a replace function on the expression:
=replace(round(((CDBL(reportitems!Textbox33.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox34.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox35.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox36.Value) / CDBL(reportitems!Textbox103.Value))*100),2),"NaN","0")+"%"
But am getting the error below:
Warning1[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox204.Paragraphs[0].TextRuns[0]’ contains an error: Input string was not in a correct format.
Any thoughts?
Your assistance is greatly appreciated.
December 31, 2015 at 8:29 am
Are you getting that because reportitems!Textbox103.Value is zero? You should us IIf to make sure that value is not zero before doing the calculation do you don't divide by zero.
December 31, 2015 at 8:38 am
Yes that is because it equals zero
December 31, 2015 at 9:01 am
Then just wrap it with IIf as so
=IIf(reportitems!Textbox103.Value = 0, 0, CDBL(reportitems!Textbox33.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox34.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox35.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox36.Value) / CDBL(reportitems!Textbox103.Value))
Untested syntax but I think correct, It puts 0 is the divisor is 0, you can change that to whatever you want it to be.
Scott
December 31, 2015 at 9:02 am
With that I get a #Error in that field.
Warning1[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox204.Paragraphs[0].TextRuns[0]’ contains an error: Input string was not in a correct format.
December 31, 2015 at 9:10 am
.
Scratch that.
December 31, 2015 at 9:24 am
Maybe it really isn't 0 in that textbox. You might have to use IsNothing instead of comparing to zero. Just adjust the IIf based on what is really in that textbox you want to divide by so that you don't try the division if it is not a non-zero number.
December 31, 2015 at 9:38 am
It's trying to add 4 textboxes that will equal 100%.
It works great if I use the expression below, except when the value is zero. Then it comes up with NaN
=CDBL(reportitems!Textbox33.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox34.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox35.Value) / CDBL(reportitems!Textbox103.Value)
+CDBL(reportitems!Textbox36.Value) / CDBL(reportitems!Textbox103.Value)
These are the numerators and the expression each one has.
Textbox33 Expression:
=SUM(IIF(Fields!DEFAULT_SIZE.Value = "Small" or Fields!DEFAULT_SIZE.Value = "Foreign/Other"
OR Fields!DEFAULT_SIZE.Value = "Non-Profit", 0.00, CDBL(Fields!CST_AMOUNT.Value)))
Textbox34 Expression:
=SUM(IIF(Fields!DEFAULT_SIZE.Value = "Large" or Fields!DEFAULT_SIZE.Value = "Foreign/Other"
OR Fields!DEFAULT_SIZE.Value = "Non-Profit", 0.00, CDBL(Fields!CST_AMOUNT.Value)))
Textbox35 Expression:
=SUM(IIF(Fields!DEFAULT_SIZE.Value = "Small" or Fields!DEFAULT_SIZE.Value = "Large"
OR Fields!DEFAULT_SIZE.Value = "Non-Profit", 0.00, CDBL(Fields!CST_AMOUNT.Value)))
Textbox36 Expression:
=SUM(IIF(Fields!DEFAULT_SIZE.Value = "Small" or Fields!DEFAULT_SIZE.Value = "Foreign/Other"
OR Fields!DEFAULT_SIZE.Value = "Large", 0.00, CDBL(Fields!CST_AMOUNT.Value)))
December 31, 2015 at 10:54 am
Issue is resolved. Typo on my end with one of report item text boxes. Thanks guys and gals. Happy New Year.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply