Sorting results in a tabular style report is a common request from business users. Sometimes, they want to see the line item with the maximum amount of value. And sometimes, they want to sort it by the description or item number of the items of a sales invoice. Giving the users the capability to interactively sort by whatever column they wish doesn’t add a tremendous amount to the report itself, but it could make a big difference in usability.
Thankfully, Microsoft has made it pretty simple to sort records using SQL Server Reporting Services. Below are the steps to enable interactive sorting on a tabular column heading.
Step 1: RIGHT CLICK on the column heading you want to add interactive sorting for and choose the “Text Box Properties…” selection.
Step 2: Select the “Interactive Sorting” tab as shown in Figure 1.
Step 3: Select the option to “Enable interactive sorting on this text box”
Step 4: Choose what column you want the results to be sorted by and CLICK the OK button.
For this example, I am showing how one would sort the details section. As can be seen in figure 1, you could also choose to have the results sorted for groups as well.
Figure 1: Text Box Properties Dialog
You could also enter an expression to use to filter the results column. This would be handy if you wanted to sort the column based on a value returned by the query or a parameter. I’ll show you how to do that in another blog.
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting