SSRS Category Grouping

Question

I'm trying to create a grouped category on a bar chart in SSRS.

My query is:

select distinct filedate, transaction_date, duedate, event_instance, event_name, eventstatus 
(datediff(DD, duedate, filedate)+1) as DaysAged,
-(DATEDIFF(WK, duedate, filedate) *2) 
-(case when DATENAME(DW, duedate) = 'sunday' then 1 else 0 end)
-(case when DATENAME(DW, FileDate) = 'saturday' then 1 else 0 end) as wkends
from MY TABLE where filedate =
    (select MAX(filedate) from MY TABLE)
and FileDate > duedate

In Reporting Services I created a calculated field to sum up "Daysaged" less the "wkends" value. This gives me how many days past the due date. On my bar chart, I want to have a bar for 0-5 days, 5-10 days, 11-15 days and 16+ days.

I've tried the following expression, but I'm not able to sort the columns or bars on the graph.

=IIf(Fields!Total_Aged_Days.Value<=5,"0-5 Days", IIF(Fields!Total_Aged_Days.Value<=10,"6-10 Days", IIF(Fields!Total_Aged_Days.Value<=15,"11-15 Days", "16+ Days")))

Thanks in advance for your help. I'm using SSRS 2008.

Answer 1

You're on the right track. The expression makes sense; I have tweaked it slightly to use a Switch:

=Switch(
  Fields!Total_Aged_Days.Value<=5, "0-5 Days"
  , Fields!Total_Aged_Days.Value<=10, "6-10 Days"
  , Fields!Total_Aged_Days.Value<=15, "11-15 Days"
  , true, "16+ Days"
)

As you've seen, this will be sorted as a string, so will not be ordered like you want. To get around this, add another calculated field which gives the correct ordering and use this as the Sort expression, which will be different from the first Group expression:

=Switch(
  Fields!Total_Aged_Days.Value<=5, 1
  , Fields!Total_Aged_Days.Value<=10, 2
  , Fields!Total_Aged_Days.Value<=15, 3
  , true, 4
)

This will give the expected order for your groups. Add the sort expression to the Category group or wherever is approriate.