Problem
You created a report for your client and the report default sorting is in descending order which was applied at the dataset level. But what if the client wants to see the data in ascending order? Or what if the client wants to be able to switch back and forth as needed? In this tip we look at how to make the sorting option interactive, so after the report has been rendered the user can changed the sort order.
Solution
This tip assumes that you have previous real world work experience building a simple SSRS Report. In this tip I will describe how to add interactive sorting in a Tablix in SQL Server Reporting Services. To demonstrate the solution, I will use the AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Service.
SQL Server Reporting Services comes with out of the box functionality known as interactive sorting. This feature allows users to sort the data at the report level in either ascending or descending order. The user can sort the data accordingly without the need to modify the dataset or report.
Step 1: Create New SSRS Report
Let’s create a sample report for demo purposes. Please change the report name to Interactive_Sorting. You can refer to the below image.
Step 2: Create Data Source and Dataset in SSRS
I have already created a data source connection to the AdventureworksDW2008R2 sample database and let’s create the below dataset for this sample report.
Main Dataset: This dataset will be used for the report body and it returns 4 data fields. I have applied ascending sort order on the YearlyIncome data field.
SELECT FirstName, LastName, Gender, YearlyIncome FROM DimCustomer ORDER BY YearlyIncome ASC
After creating the Main dataset, the Report Data Pane looks like the below image.
Step 3: Add Tablix in Reporting Services
Let’s add a Tablix for data viewing purpose. I will pull all 4 data fields from the Main Dataset into the Tablix. After adding the Tablix, the sample report looks like the below image.
Let’s preview this sample report, as you can see from the below image our sample report displays all the records in ascending order based on the YearlyIncome data field value.
Now a user wants to see YearlyIncome data in descending order. To do this, either the dataset has to be modified with descending sorting on the YearlyIncome data field or you have to change sorting order at the report level on the YearlyIncome data field.
What if a user wants a choice to sort data in either ascending or descending order? To handle this kind of requirement SQL Server Reporting Services comes with out of the box functionality known as interactive sorting. We can provide interactive sorting at the report level on the YearlyIncome field.
Step 4: Adding Interactive Sorting to SSRS
Let’s add interactive sorting on the YearlyIncome column. Right click on the YearlyIncome column and select Text Box Properties, you can refer to the below image.
Once you click on the Text Box Properties, a new Text Box Properties window will open. In the Text Box Properties window click on Interactive Sorting, you can refer to the below image.
Now we have to enable the sorting option for the YearlyIncome textbox. Check the “Enable interactive sorting on this text box” check box as shown below.
Once you enable Interactive Sorting, other options will be enabled. You can apply this interactive sorting on Detail rows or Groups. You have to select this as per your requirement. In our sample report we don’t have any groups, so we want to sort base on detail rows.
Select the Detail rows radio button and select the column we want to sort by. In our case we want to sort on YearlyIncome, so I have selected it in the “Sort by:” drop down option.
If you want to apply this sorting to all groups and data regions then you can check that check box. In our case it is not required, so I am not checking it.
Step 5: Report Preview
We have done all the necessary changes, now let’s preview the report.
For the first time when you will preview the report you will find two signs (up and down) next to the YearlyIncome column. These two signs represent the interactive sorting functionality. If we applied any sorting at dataset level then the report will show the sorted data by default, if no sorting is applied then report will show unsorted data.
As you can see from the below image, the report is sorted based on YearlyIncome by default. It is because we sorted the data at the dataset level.
Users can change the sorting at the report level just by clicking on the interactive sorting icon. Once the user clicks on the interactive sort icon, the interactive sort sign will change and the report will show the sorted data. If the interactive sort sign changed to single arrow and points down then it means the report is sorted in descending order. You can refer to the below image.
If the user clicks on the interactive sort down arrow, the report will be sorted in ascending order.
Next Steps
- For better performance it is recommended to provide default sorting at the dataset level based on the most common usage of the report, just like we have done in this sample report.
- Don’t forget to practice this tip on a Matrix report.
- Check out tips on SQL Server Reporting Services Parameters.
- Check out all of the SQL Server Business Intelligence Tips.
- Check out my all tips here.
Ghanesh Prasad is a Microsoft BI lead developer for an industry leading Indian IT Service Company in Bangalore. He has over 5 years of work experience with Microsoft Business Intelligence and expertise in SQL Server, SQL, T-SQL, SSRS, SSIS, SSAS, and MDX languages. In his organization he is responsible for developing end to end data loading solutions, cube development, report development, migration, testing, deployment and maintenance.