How to Enable Interactive Sorting in a SSRS Matrix

Interactive sorting is one of my favorite features and I think it is under-used. I add it to all of my reports, but the other day I made a discovery. Enabling interactive sorting in a SSRS matrix is a bit different from the steps you would take in a SSRS table. In this post, I show you the three extra steps needed to successfully enable interactive sorting in a SSRS matrix.

I already published a post, How to Leverage the Interactive Sorting Feature in SCCM Reports, about how to set it up correctly in a table. By following those steps and tweaking it a bit for a matrix, now you won’t run into any problems!

Background

I added the interactive sorting feature to a new SSRS report I was working on for Enhansoft Reporting. I did what I always do. Normally, I don’t test this feature because it should work. This time, however, I did. Well, imagine my surprise when I found out that it wasn’t working correctly. Why? What was different about this report? Then I remembered. I don’t always use matrixes, but in this case I was using one instead of a table. It was time to investigate.

Interactive Sorting

Interactive Sorting in a SSRS Matrix - Interactive Icons

Here’s how the interactive sorting feature works. In the above screenshot of the Locate Computers by Username report, the five purple arrows point to the interactive icon (black arrows with up and down points). When you first click on the icon, the icon appears on a white background with an up arrow. This sorts the column from oldest (null first) to youngest. If you click on the icon a second time, the icon (still on a white background) now displays a down arrow. This sorts the column from youngest to oldest (null last).

Isn’t that a handy feature?

Interactive Sorting in a SSRS Matrix

You can start the process by following this guide, How to Leverage the Interactive Sorting Feature in SCCM Reports. Below I will show you what you need to do differently if you are using a matrix in place of a table.

Interactive Sorting in a SSRS Matrix - Detail Rows

Let’s look at the Text Box Properties for one of the columns in my matrix where I enabled the interactive sorting feature. By default, the column is sorted by Detail rows. Unfortunately, this pre-set means that the interactive sorting feature doesn’t work correctly on any report.

Interactive Sorting in a SSRS Matrix - Groups

Instead you need to select Groups and then the table group from the drop-down menu. In my case table1_Group1 is the one that I want.

Interactive Sorting in a SSRS Matrix - Updated Sort Options

Back at the Text Box Properties window, click OK to apply these new settings. This process needs to be repeated on all columns where you want the interactive sorting feature enabled.

Final Output

Interactive Sorting in a SSRS Matrix - Native Sorting Order

Now back within the report, I see that the SQL query native sorting order is by computer name.

Interactive Sorting in a SSRS Matrix - Oldest to Recent Dates

After I click on the Last Console Date interactive sorting arrow, I see that the rows are sorted from the oldest date to the most recent date. By the way, the interactive sorting icon changed its look. Did you see that too?

Interactive Sorting in a SSRS Matrix - Recent to Oldest Dates

When I click on the interactive sorting icon, again, as expected I see the rows sorted from the most recent console date to the oldest console date.

I can finally say, “Problem solved!”

Are you looking for more of my tips about SCCM, Microsoft Intune, SSRS, or Power BI? Then, sign-up for the Enhansoft newsletter!

Interactive Sorting in a SSRS Matrix - Subscribe Button

If you have any questions about how to enable interactive sorting in a SSRS matrix, please feel free to contact me at @GarthMJ.

Leave a Comment