When using any reporting tool, I’m always on the lookout for features that add value/functionality, and are quick/easy to implement. Interactive sorting within SSRS is one of those things that’s so quick and easy to implement at a basic level, that I usually add it to reports without it even being request because it will literally only takes seconds, and I’ve never had anyone come back and say “Hey, we don’t want the ability to sort the data in our report!” With that in mind, let’s take a quick look at how we can implement interactive sorting on a table within SSRS. (This walkthrough does assume some general knowledge of SSRS and how to create a data source and data set)
- Open Report Builder, and create a new blank report
- Create a data source connected to a SQL Server data source (any database will do, we’ll actually be hardcoding our data in the data set)
- Create a data set in report builder and paste in the following SQL into the query window:
SELECT 'Product A' 'Product', 3000 'SalesAmount' UNION SELECT 'Product B', 10000 UNION SELECT 'Product C', 5000 UNION SELECT 'Product D', 1000 UNION SELECT 'Product E', 7000 UNION SELECT 'Product F', 2000
- Add a table to the body of your report
- Drag “Product” and “SalesAmount” from the dataset you created in step 3 to the first two columns of your new table. If you run/preview your report at this point, your table should look like the image below
- Back in the Report Builder design view, on the header row of the table, right click the cell containing the “Sales Amount” title, and then select “Text Box Properties”
- On the left hand side of the “Text Box Properties” window select “Interactive Sorting”
- Place a checkmark in the checkbox labeled “Enable interactive sorting on this text box”
- In the dropdown box labeled “Sort by” select “[SalesAmount]”
- Click “OK”
- Preview your report
If all has gone well, you should now see two up/down triangles in the Sales Amount header as shown below highlighted in yellow.
While the graphic actually looks like two different arrows that you can click, it’s actually just one button that will order the column you configured from low to high (or A-Z) on the first click, and then subsequent clicks will reverse the current order. While having this functionality might not be all that useful when there’s only 6 rows of data, it can be fairly useful to the report user when dealing with a small/medium result set of a few hundred or thousand rows. Note that you do want to be mindful of picking the correct column to sort when you’re on the “Textbox Properties” screen, it would be fairly easy to pick a different column and end up with a sort button on “Sales Amount” that actually sorts the “Product” instead. While this functionality is very easy to add, it does require some additional space lengthwise for the header cell you place these in, so if you’ve got a table that is a tight fit width wise that might be a concern. I will also say that I typically only put these on columns where it makes sense that a user might want to sort. For instance sales amount is a pretty good candidate for sorting, but if I had a table with customer contact info in it, I probably wouldn’t put a sort button on the phone number column, as I doubt anyone would ever want to sort on the phone number. It’s also a good Idea to educate the report viewer on use of sorting as well. It’s my experience that some users will sort a table to try and locate something alphabetically because they’re unaware that SSRS has a “Find” function they can use (The find textbox is literally at the top of every report).
If you look back to “Textbox Properties” screen you’ll also notice that the interactive sorting can be applied to groups within the table as well, along with some additional options I’ll leave you to play with on your own. Until next time, Happy reporting!