Sort columns in a different order from the group in SSRS matrix

Question

I have a set of data like this:

WITH t(stud_id, forename, surname, score) AS
(
SELECT 601, 'Ed','Brown',1
UNION ALL SELECT 601, 'Ed','Brown',8
UNION ALL SELECT 602, 'Jon','Green',5
UNION ALL SELECT 603, 'Ali','Adams',5
)
SELECT * FROM t

I want to produce a report in Report Builder with four columns: stud_id, forename, surname, total score. The total score is obtained by adding up the individual scores for each value of stud_id. I know how to do this with a matrix. I end up with three groups: stud_id, forename and surname, and then use SUM to get the total score.

However, I would like to sort the rows by surname followed by forename, and then stud_id. I can do this if I put the surname column first in the table, and then the forename and then the stud_id, but that is not the order I want.

How can I keep the columns in the order I want, but sort the rows by the columns in a different order? The sorting does not have to be interactive. I think that if I could combine the three columns into one group I could do this, but I still want to display three columns and not one.

Harry Harry · Accepted Answer · 2018-02-22T19:50:09

You can do the sorting within the groups themselves. Go to the group properties, then select the sorting. click Add and then add all the columns you want to sort by..

Sort columns in a different order from the group in SSRS matrix

1 Answers