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

SSRS - KPI Reports (Indicators and Gauges)

SSRS reports are designed to display graphs, charts and KPI’s (Key Performance Indicators) very beautifully in the different kinds of the dashboards. A Key Performance Indicators (KPI) is any measurable value in our report that has business significance, for example, Total Sales. If we want to display the current state of a KPI's on a report, we can use one of three different approaches: 

• Highlight cell values using a background color that shows the state of the KPI. 
• Replace cell values with an image that shows the state of the KPI. 
• Insert a gauge.

Key Performance Indicators are typically displayed in a scorecard with directional, Symbols, shapes or rating indicators which are mostly either red, yellow or green icons. We are going to use Gauge in KPI's report also.

For example; Sales admin wants to see the KPI performance of his sales team. To meet the requirement, we will use the sales amount against target to get the KPI's values for each sales person. To generate the KPI's Sales Person Report, we will use the following data in the KPI Report as shown below:

SalesPerson	SalesPersonName	Sales	SalesTarget
1505	Caroline Reynolds	8391451.95	10000000.00
421	Garrick McPherson	6591451.95	10000000.00
3203	George Goeddeke	2063468.23	10000000.00
325	Jim Mollencop	3727894.74	10000000.00
3301	John Prymak	7391451.95	10000000.00
408	Leslie Roberts	2283584.43	10000000.00
324	Linda Barrett	7891451.95	10000000.00
2403	Lori Murphy	2168793.69	10000000.00
302	Martin Montano	2120988.66	10000000.00
1404	Michael Moore	6391451.95	10000000.00
2207	Sally Oldach	8285374.40	10000000.00

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

After click on the preview button, report will display KPI as shown below-

To create List Item based Drill Down report, we will follow the following steps as shown below:

1) In our existing report server project, we will add a new report as “Sales Person KPI” 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 SalesPersonKPI for the Dataset and choose a dataset embedded in our report and choose Query Type Text as shown below:

select SalesPerson, SalesPersonName, Sales, SalesTarget, KPI= Case when Sales>8000000 then 9                                   when Sales between 6000000 and 8000000 then 7                                   else 5 end from KPISalesPersion order by SalesPersonName GO

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, fill the Sales Person name, Sales Amount and Sales Target to the report as shown below: 

9) Click on the preview button to view the report and report will show the following data after do the needful formatting as shown below:

10) Now we need to insert four blank columns between sales and target to put the indicator and gauge as shown below:

11) Preview the report as shown below:

12) Now we need to add our first flag indicator to our report. For this, we need to right click on our first blank column and choose indicator from insert as shown below:

13) Select Indicator Type Window, choose flags from Symbols and click OK button as shown below:

14) Now, right click on the flag indicator and click on Indicator Properties as shown below:

15) In Indicator Properties window, fill values KPI and click OK button as shown below (this value is coming from our database):

16) Preview again the report as shown below:

17) Now right click on the second blank column of table and choose indicator again. In indicator Type window, choose five stars from Ratings and click OK button as shown below:

18) Now, right click on the star indicator and click on Indicator Properties. In Indicator Properties window, fill values KPI and click OK button as shown below :

19) Preview again the report as shown below:

20) Now, right click on the third blank column of table and choose indicator again. In indicator Type window, choose 3 Up/Down Tringles from Directional and click OK button as shown below

21) Now, right click on the directional indicator and click on Indicator Properties. In Indicator Properties window, fill values KPI and click OK button as shown below ::

22) Preview again the report. In our report has three indicator such as Symbols – Flags, Ratting – 5 Stars and Directional – 3 Up and Down Triangles as shown below: 

23) Now, we need to add the Gauge in our report. For this, we need to right click on the forth blank column of the table and choose Gauge as shown below: 

24) In Gauge Type window, choose 90 Degrees Northeast gauge and click OK button as shown below:

25) Right click on 90 Degrees Northeast gauge and click on KPI in unspecified as shown below:

26) In Gauge Data, right click on RedialPointer1 and click on Scale Properties from Gauge panel as shown below:

27) In Redial Scale Properties, we need to set the Maximum value. For this, click on the function of Maximum field as shown below:

28) In function expression window, set the following expression as shown below: 

=Max(Fields!KPI.Value, "SalesPersonKPI")

 29) Click OK button on the expression and Redial Scale Properties also as shown below:

30) After doing the needful formatting settings for Indicator and Gauge, preview of the report as shown below:

This completes the tutorial section of the simple KPI's report.

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.

To view the live implementation of KPI in SSRS, please visit at