Tuesday, August 18, 2015

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



2 comments:

  1. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work. best surface pro

    ReplyDelete