SSRS – Implement Interactive Sorting

A sort expression controls the order in which data appears in a data region. Sort expressions are created automatically as you build the report layout. By default, a sort expression for a group is set to the same value as the group expression. You can customize a sort expression after it is created. 
You can also add an interactive sort button to a table or matrix cell to enable a user to interactively change the row sort order for groups or rows within groups.

You can add interactive sort buttons to enable a user to toggle between ascending and descending order for rows in a table or for rows and columns in a matrix. The most common use of interactive sort is to add a sort button to every column header. The user can then choose which column to sort by.

What is an interactive sort?

What to sort: Rows or columns?

What to sort by: A field that is displayed in a table column? A field that is not displayed?

What context to sort in: For example, you can sort on rows associated with row groups; columns associated with column groups; detail rows; child groups within a parent group; or parent and child group together.

Which text box to add the sort button to: In the column header or in the group row header?

Whether to synchronize the sort for multiple data regions: You can design a report so that when the user toggles the sort order, other data regions with the same ancestor also sort.

Types of Interactive Sorting

Sorting via the Tablix properties window sets the initial sort order of the Tablix.

Sorting via the Group properties window will override the Tablix sort order for sorting groups, but will retain the Tablix sort order within groups.

Sorting via the Details properties window will override the Tablix sort order for sorting within groups.

The following table summarizes the effects, you can achieve by using interactive sort buttons.

Action	What to sort	Where to add the sort button	What to sort on	Sort scope
Sort detail rows for a table with no groups	Details	Column header	Dataset field bound to this column	Data region
Sort top-level group instances for a matrix	Groups	Column header	Group expression for parent group	Data region
Sort detail rows for a child group in a table	Details	Child group header row	Dataset field to sort by	Child group
Sort rows for multiple row groups and detail rows in a table	Groups, but you must redefine the group expression	Column header	Aggregate of dataset field to sort by	Data region

To understand the implementation of interactive sorting in SSRS report, we are use the following data to implement the sorting feature in the report-

ItemCategoryName	ItemNum	Sales
1/4" DATA CARTR	SON SLR58GB	14650.80
1/4" DATA CARTR	MAX 089710	21720.00
1/4" DATA CARTR	IMN 11892	15329.40
3480 TAPE CARTR	IBM 4479753	11583.00
3480 TAPE CARTR	IMN 40213	110603.18
3480 TAPE CARTR	MRI CT34COMP	19998.00
3490 CARTRIDGES	IBM 09G4494	16279.20
3490 CARTRIDGES	IMN 42473	385083.08
3490 CARTRIDGES	IMN 46223	27204.00
3570/SD3 CARTRI	IBM 08L6663	30298.10
3570/SD3 CARTRI	IBM 05H2462	73243.00
3570/SD3 CARTRI	IBM 08L6187	40271.22

We need to display the above information in our SSRS report and Report header should be like as “Categorized Item Sales Summary” and report will be look like as shown below:

After click on the sorting button in Item Num (Sort detail rows for a child group in a table), report will display sorting data as shown below- 

After click on the sorting button in Item Category Name (Sort top-level group instances for a matrix), report will display sorting data as shown below- 

To Implement Interactive sorting in the report, we will follow the following steps as shown below:
1) In our existing report server project, we will add a new report as “Categorized Item Sales Summary” as shown below:

2) Now, we need to create a dataset for the report as shown below:

3) Add Dataset will launch Dataset properties window. We need to set the name as InetractiveSorting for the Dataset and choose a dataset embedded in our report and choose Query Type Text as shown below :

select ItemCategoryName, ItemNum, Sales from CategoryItemSales order by ItemCategoryName

4) After click on the OK button, a dataset has been added in the report data with parameters also as shown below:

5) Now right click on the report, add report header to the report and done the needful formatting as shown below-

6) After adding the report header, we need to add the Table to the report by the help of right click on the report as shown below :

7) Now, we need to add the dataset to the newly added Tablix as shown below:

8) Now, we need to add Item Category Name group to the report. For this, right click on the row details and add parent row group as shown below: 

9) In the Tablix group window, fill Item Category Name in drop down list and Click OK button as shown below:

10) Now we need to fill other column to the report and do the needful formatting setting as shown below:

11) Preview the report as shown below:

12) Now we need to Implement details level interactive sorting. For this, we need to right click on second header column Item Num and choose Text Box Properties as shown below:

13) Select Text Box Properties Window, choose Interactive Sorting, checked Enable Interactive Sorting on this text box check-box where details is by default selected and in Sort By List choose Item Num. Click OK button as shown below:

14) Now we need to Implement Group level interactive sorting. For this, we need to right click on First Group header column Item Category Name and choose Text Box Properties as shown below::

15) Select Text Box Properties Window, choose Interactive Sorting, checked Enable Interactive Sorting on this text box check-box and select Groups to fill the Group Item Category Name and in Sort By also have Item Category Name. Click OK button as shown below:

Preview again the report as shown below: as shown below:

After click on the sorting button in Item Num (Sort detail rows for a child group in a table), report will display sorting data as shown below- 

After click on the sorting button in Item Category Name (Sort top-level group instances for a matrix), report will display sorting data as shown below- 

This completes the tutorial section of the implementation of Interactive Sorting in SSRS Reports.

Other Drill Reports in SSRS

1) To learn the Row Drill Down Report, Click here.

2) To learn the Row Drill Through Report, Click here.

3) To learn the Column based Drill Down Report, Click here.

4) To learn the Chart based Drill Down Report, Click here.

How can you create your first sub report. Click here.

You can view the demo of the implementation Interactive Sorting