SSRS Sorting Matrix with manualy added column

0
votes

I have preview of matrix like this:

enter image description here

Columns "Report#" and "Headline Indicator" are added manually, "Report Month" is number of month that is used for row grouping, "1" and "3" are column group fields (department_key_id). Design on the picture below:

enter image description here

I want to sort data by first column "Report#". I tried to configure some interactive for that column but it was not worked for me(

How can I sort rows with data in the table in this way - 2, 2, 3, 3, 4, 4, 5, 5?

thanks!

1
matrixreporting-servicesssrs-2016
You want to allow the user to sort the values as in the preview? Or you want the data sorted at report execution? – Steve-o169
@Steve-o169 Hi! At report execution is a better solution for me. – A. Anatolii
I don't see how you would be able to sort the ones that are hard-coded as Val2, Val3, Val4... into Val2, Val2, Val3, Val3... the way that you have the table set up. You would need to set up the Row Grouping to Group By the Report# and then the Month. I'm not sure how your data is set up to where you have the Report# in different fields tho. I hate to say it (since it usually doesn't help) but some sample data would help. – Hannover Fist

1 Answers

1
votes

So it looks to me like you might need a couple of changes to get the set-up you want here. First, I'm going to recommend adding an ORDER BY [report_month_num] to your query and you can do away with the current row grouping on report_month_num.

You'll need to add a Detail grouping row for each of your four rows. So right click in any textbox, navigate to Add Group > Row Group > Adjacent below. Make sure to select Show detail data for each of the four groups you make. You'll need to copy the data into the new rows and you should be all set.

This should ensure the ordering you are expecting.