April 9, 2014 at 7:31 am
I am trying to modify an existing report to show 2 decimal places for a percentage, no matter what the value is. For example, if the calculation in the expression comes to 3.765, then the field will show 3.77%, as expected. However, if the calculation comes to 3, then the field is only showing 3%, and not 3.00%. The expression is written as follows:
=replace(round(((Sum(Fields!FIELD1.Value))/(Sum(Fields!FIELD2.Value))*100),2), "NaN", "0.00")+"%"
In the Placeholder Properties > Number, I had set it to Percentage with 2 decimal places. Since that did not work, I then tried Custom, with a format of 0.00%. This also did not work. I tried to change that format to N2, based off of some research that I did, but that did not work, either.
Does anyone have any suggestions on how I can get a value of 3% to show as 3.00%, and same with those that might be 3.6% to be 3.60%, so essentially, always force 2 decimal places, even if those decimal places are represented by 0's?
April 9, 2014 at 11:58 am
What formatting have you specified for the TextBox under the Number tab?
Gerald Britton, Pluralsight courses
April 9, 2014 at 12:24 pm
For the Text Box Properties > Number, it is listed under the Category of Percentage with 2 Decimal Places.
April 9, 2014 at 12:45 pm
miles_lesperance (4/9/2014)
I am trying to modify an existing report to show 2 decimal places for a percentage, no matter what the value is. For example, if the calculation in the expression comes to 3.765, then the field will show 3.77%, as expected. However, if the calculation comes to 3, then the field is only showing 3%, and not 3.00%. The expression is written as follows:=replace(round(((Sum(Fields!FIELD1.Value))/(Sum(Fields!FIELD2.Value))*100),2), "NaN", "0.00")+"%"
In the Placeholder Properties > Number, I had set it to Percentage with 2 decimal places. Since that did not work, I then tried Custom, with a format of 0.00%. This also did not work. I tried to change that format to N2, based off of some research that I did, but that did not work, either.
Does anyone have any suggestions on how I can get a value of 3% to show as 3.00%, and same with those that might be 3.6% to be 3.60%, so essentially, always force 2 decimal places, even if those decimal places are represented by 0's?
Your expression is returning a string. What if you just return a numeric value and let SSRS formatting handle the rest? (True, you'll have to handle the possible 0-divisor differently.
Something like this perhaps:
=iif(cstr((Sum(Fields!FIELD1.Value/Sum(Fields!FIELD2.Value)) = "Infinity", 0, Sum(Fields!FIELD1.Value/Sum(Fields!FIELD2.Value)
Gerald Britton, Pluralsight courses
April 9, 2014 at 3:29 pm
I got it to go by adding CDec to the expression as follows:
=replace(round(((Sum(CDec(Fields!FIELD1.Value)))/(Sum(CDec(Fields!FIELD2.Value)))*100),2), "NaN", "0.00")+"%"
Gotta love expressions!!
April 9, 2014 at 4:14 pm
You're still rendering it directly as text. I think its better to return a float and let SSRS format it according to the formatting you set up. Also, have you tested with divide by 0? I think that your expression may fail, since the division returns "Infinity" in my test, not "NaN".
Anyway, glad you got it working!
April 11, 2014 at 10:17 am
Yeah, I am running into an issue with a divide by zero error with the following:
=replace(round(((Sum(CDec(Fields!Field1.Value)))/(Sum(CDec(Fields!Field2.Value)))*100),2), "NaN", "0.00")+"%"
I tried to add an IIf statement, but I get an error stating that there are too may arguments. Do you have any suggestions on how to rewrite that, so that it can do the arithmetic equation; convert that to a decimal with 2 places, even if the trailing numbers are zeroes; and check for a divide by zero error, all in one expression?
April 11, 2014 at 10:30 am
use this expression
=IIF(Sum(CDec(Fields!Field2.Value))=0,0,Sum(CDec(Fields!Field1.Value))/IIF(Sum(CDec(Fields!Field2.Value))=0,1,Sum(CDec(Fields!Field2.Value))))
and set the format to P2
*Edited to include CDec
You should change your code to not use strings for numeric input
Far away is close at hand in the images of elsewhere.
Anon.
April 11, 2014 at 10:59 am
That worked!! That expression was written by a previous developer, and I was trying to modify it to get rid of the divide by zero error that would occur. I am rather new at report expressions, and was trying to get a better feel for how they work. They are quite different than SSIS expressions, which I understand to a greater degree.
I would agree that since the field is numeric, it should not be converted to a string. Where can I find that aspect, so that if this were to occur to another report, I can pinpoint where the expression is incorrectly formatted?
I appreciate all of your help in this!
April 11, 2014 at 12:26 pm
See my Post #1560145 above
April 12, 2014 at 5:57 am
miles_lesperance (4/11/2014)
That worked!! That expression was written by a previous developer, and I was trying to modify it to get rid of the divide by zero error that would occur. I am rather new at report expressions, and was trying to get a better feel for how they work. They are quite different than SSIS expressions, which I understand to a greater degree.
Yes SSRS expressions are a bit strange sometimes and the real problem is it's IIF processing does not 'short circuit' hence the convoluted solution.
Where can I find that aspect
Not sure what you mean by this. You could checking all your reports for divisions to see if they need changing.
If you mean the string conversion then I would start with the dataset query/procedure to see what it does and see if it could be changed.
Sometimes this is not possible so you may have to live with using CDec.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply