CODEWITHSUNDEEP
reportingservices-2005
SidC
SidC

Reputation: 3203

Sorting Matrix Report Columns in Specified Order

I've written the following TSQL to use in a report:

SELECT     patcnty, CASE WHEN CAST(age_yrs AS int) <= '5' THEN 'Ages 5 and Younger' WHEN CAST(age_yrs AS int) BETWEEN '6' AND 
                  '17' THEN 'Ages 6 to 17' WHEN CAST(age_yrs AS int) BETWEEN '18' AND '44' THEN 'Ages 18 to 44' WHEN CAST(age_yrs AS int) BETWEEN '45' AND 
                  '64' THEN 'Ages 45 to 64' WHEN CAST(age_yrs AS int) >= '65' THEN 'Ages 65 and Older' END AS AgeGroup, 
                  CASE WHEN patcnty = '54' THEN 'Tulare' WHEN patcnty = '16' THEN 'Kings' WHEN patcnty = '15' THEN 'Kern' WHEN patcnty = '10' THEN 'Fresno' END
                   AS County, oshpd_id, age_yrs
FROM         OSHPD2009
WHERE     (patcnty IN ('10', '15', '16', '54')) AND (age_yrs <> ' ')

I've created a SSRS 2005 Matrix report and have placed AgeGroup as my column and County as my row. When the report is displayed, the order of the columns are: Ages 18 to 44, Ages 45 to 64, Ages 5 and Younger, Ages 6 to 17 and Ages 65 and Older. This makes sense in that I set the sort order for the group to ascending.

How can I change the group sort order for the matrix column to sort in this manner: Age 5 and Younger, Ages 6 to 17, Ages 18 to 44, Ages 45 to 64 and Ages 65 and Older?

Thanks much for your help!

Upvotes: 3

Views: 4745

Answers (2)

Yene Shewaneh
Yene Shewaneh

Reputation: 31

Or you can modify your source table to include a new column for AGe_Group order. And on the SSRS you can do the sorting based on the new column order. To clarify:

Newcolumn  int 

Set NewColumn = 1
where AgeGroup = "Ages 5 and Younger"

Set NewColumn = 2
where AgeGroup = ="Ages 6 to 17"

Set NewColumn = 3
where AgeGroup = Ages 18 to 44"

Upvotes: 3

SidC
SidC

Reputation: 3203

After a little digging, I decided that SSRS needed to be told in what order to sort the groups. So, in the column grouping sort screen, I added an expression of

=iif(Fields!AgeGroup.Value="Ages 5 and Younger","1",
iif(Fields!AgeGroup.Value="Ages 6 to 17","2",
iif(Fields!AgeGroup.Value="Ages 18 to 44","3",
iif(Fields!AgeGroup.Value="Ages 45 to 64","4",
iif(Fields!AgeGroup.Value="Ages 65 and Older","5","Other")))))

This basically forces SSRS to sort the groups in the order I specify by assigning Ages 5 and Younger a value of 1, so that it's the first sort group etc. My report now works like a charm!

Upvotes: 4

Related Questions