Endpoint Insights
How to Leverage the Interactive Sorting Feature in SCCM Reports
Topics: Endpoint Insights
Have you ever watched someone use one of your reports/dashboards? I have and it is always fascinating to see how someone will review the data. If they have prior experience using other reports or the default SCCM reports, they will always try using the same helpful features. One of those helpful attributes in SCCM reports is the interactive sorting feature. This is one of those little gems that when you leverage it, it can help take your reports to the next level.
What Does the Interactive Sorting Feature Do?
The interactive sorting feature allows you to select a column, sort that column, and in doing so, it will re-arrange all of the table rows so that the selected column is sorted either alphabetically or numerically. This feature is useful if you want to see something different from the default sort.
How Do I Know if the Interactive Sorting Feature is Enabled?
Basically if you see arrows at the top of a column then you know that the interactive sorting feature is enabled.
In the example above, I flagged the columns which allow for interactive sorting. Notice the up and down arrows? They signify that the column is using the default sort listed within the SQL Server query.
What if you want to see the table listed from the smallest number to the largest? Simply click on the up and down arrows. The arrow will change to an up arrow. Since this is the default order within my SQL Server query only the arrow changed its appearance.
Click on the arrow again and this time it will sort the table from the largest number of devices to the least amount.
How Can I Enable the Interactive Sorting Feature?
Start by opening your SQL Server Reporting Services (SSRS) report in SQL Server Data Tools BI (SSDT-BI). Right-click on the Table/Matrix column header box where you want to enable the interactive sorting feature. In this example, I’m using the PC Type column. Next, select the Text Box Properties… option.
On the Interactive Sorting node, select Enable interactive sorting on this text box.
Next, select the appropriate column in the drop-down box.
Finally, click on the OK button and you are done!
Repeat these steps for each column where you want the interactive sorting feature to be available.
Congrats! Enabling this feature takes your report to the next level.
Interactive Sorting Feature Tip
On the Text Box Properties page, did you notice that any column could be used for sorting? Keep in mind that these are the columns within your SQL Server query, so they are different columns than simply the columns displayed within the table itself.
Why is this important? It is important for a couple of reasons.
First, it allows you to sort any column within your SQL Server query and not be restricted to only the displayed columns in the Table/Matrix. What do I mean? Look at the query results above. They are sorted by computer name. At the moment, this list isn’t that helpful to me because ultimately I want to put it in numerical order to see what computers need the most patches first.
In the screenshot above, I’ve clicking on the interactive sorting feature on the Status column. Why does Needs 10 Patches come before Needs 2 Patches? At first it doesn’t make sense, but the answer is that this column is NOT a list of integers. The Status column is a made up of strings.
To put this another way, the interactive sorting feature in this column compares each character in a string one at a time. The first unique character in this example appears two characters after the word Needs (Character #7). The seventh character is sorted from 0-9 and A-Z. The same process is repeated for the eighth character and so on. Therefore the Needs 1… results will get arranged before the Needs 2… results are sorted. The entire process is repeated until all of the strings are arranged.
How can you sort this data numerically? You need to go back to your original SQL Server query.
Look at the screenshot above for Dataset1. Below it you’ll see that I have a column called sort. This column, within my SQL Server query, gives me the numerical value of the number of software updates needed for each computer. Since I have that numerical information in a column, I can use it to sort the rows of my table. I’ll use the sort column in place of the Status column for sorting. See the results in the next screenshot.
In the screenshot above I changed the interactive sorting feature to use the sort column from my DataSet1. When I leveraged the interactive sorting feature on the Status column everything sorted numerically.
Keep in mind, then, when designing reports that you can leverage the interactive sorting feature by using another column, such as the sort column in my example, to help arrange the results in a more logical manor.
Now for the second reason why this tip is important. I only touched on it briefly in the previous example, but there are some very complex things that you can do with the interactive sorting feature. Using the expressions feature within SSRS helps with this, but this topic is too far advanced for this blog post. Maybe I will talk about it in a future post or maybe in a presentation at MMSMOA.
If you have any questions about how to leverage or enable the interactive sorting feature, please feel free to contact me @GarthMJ.
