November 3, 2014 at 12:32 pm
Using SSRS, how can I create grouping expressions to be added to a bar chart that would look like this?
0-5 days
6-10 days
11-15 days
16+ day
How do I write the expression?
Should I even be a grouping expression or something else?
Thanks in advance.
November 3, 2014 at 1:24 pm
You could do it by creating a calculated field in your dataset. Something like this:
=IIF(Fields!EnrollWeek.Value<=5,"0-5 days",IIF(Fields!EnrollWeek.Value<=10,"6-10 days",IIF(Fields!EnrollWeek.Value<=15,"11-15 days","16+ days")))
I had a dataset with numbers (EnrollWeek), so that that's what I used for the example.
November 3, 2014 at 1:50 pm
Great. This works. Now I have the issue of how to sort. I tried sorting within the category group properties using A to Z. Is there an expression I can use to sort?
This give me 0-5, 11-15, 16+, 6-10 days respectfully. Is there another way to do this to sort it in order of days?
Thanks,
November 3, 2014 at 2:55 pm
If you create another calculated value in your dataset, you could do this:
=IIF(Fields!EnrollWeek.Value<=5,0
,IIF(Fields!EnrollWeek.Value<=10,6
,IIF(Fields!EnrollWeek.Value<=15,"11-15 days",16))
Because this returns an integer, you can sort on it (and it will work).
The only difference is that I returned just the first value in the range and as an integer, not a string (that's why no quotes).
November 4, 2014 at 11:32 am
In your dataset, return a field that identifies the "DayGroup". Then group on that.
Here's the formula to use on the SQL side: round((MyDaysCountValue - 1)/5, 0) as [DayGroup]
You can also do it on the SSRS side by grouping on the VB equivalent to the above formula. No need for IIF/SWITCH/CASE statements.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply