February 26, 2009 at 10:11 am
All,
I am having a problem sorting a field that is supposed to be a percent value. In my data there is a field called ProbabilityDesc and it is varchar(64).
The values in that field are 100%, 95%, 75%, 50%, 25%, and 0%. In my stored procedure, I have done the following in an effort to put this into a format that can be sorted numerically:
Probability = CAST(CAST(REPLACE(ProbabilityDesc, '%', '') AS decimal(10,0))/100 AS decimal(10,2))
The value that come into my report are 1.00, 0.95, etc. I am then writing an expression on the actual field:
= FormatPercent(Fields!Probability.Value, 0)
When I do this, the field does display as 100%, 95%, etc. However, when I edit the group to sort by the same value (I'm including the FormatPercent part), it doesn't sort it right. It goes 95, 75, 50, 25, 100, 0. It looks at the 1 in the 100, not the entire number.
Does anyone know how to make this work? I can't sort the data in my procedure as this table isn't the only one in the report, and both use the same proc.
February 26, 2009 at 11:22 am
Appyling the FormatPercent turns that back into a varchar. Which is fine for display purposes, but as you've already discovered - no good for sorting. So - you want to sort on the numeric value (what you have BEFORE the FormatPercent is applied).
If need be - you may need to maintain both fields in your report def, but only display one of them.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2009 at 12:04 pm
Duh. Seems so obvious! Thanks!! I probably tried every variation of everything except that one!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply