Introduction

One of the important factors to be considered and taken care in SSRS report designing is Ordered display of report: 

  •  Order of rows in tables 
  •  Order of columns in matrices
  •  Order of labels in X-axis of charts
  •  Order of series groups in stacked charts

In this article, let us see examples for

  • How to sort rows in Tables
  • How to sort columns in Matrices
  • How to sort rows in Tables (Interactive sorting)
  • How to sort rows in Tables (Dynamic sorting - by getting column name as input from user based on that column sorting has to be performed)
  • How to sort labels in X-axis (Horizontal axis) of charts
  • How to sort series groups in stacked charts

How to sort rows in Tables

Consider sample Dataset query for this example as shown below:

DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
INSERT @Tmp SELECT 1,'C'
INSERT @Tmp SELECT 2,'B'
INSERT @Tmp SELECT 3,'A'
SELECT * FROM @Tmp

Create Table report based on above Dataset and when the report is previewed, you will notice the report getting displayed as shown below:
(with default sorting of rows as returned from Dataset)



If we want the report rows to be displayed in the ascending sort order based on Name column, we can achieve that in two ways :

i) By adding ORDER BY clause in the Dataset as shown below:

DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
INSERT @Tmp SELECT 1,'C'
INSERT @Tmp SELECT 2,'B'
INSERT @Tmp SELECT 3,'A'
SELECT * FROM @Tmp
ORDER BY Name

ii) Select the entire row in the Tablix, right-click - > Tablix Properties ... - >  Sorting - > Add - > mention Sort By = Column and Order = Ascending/Descending







After adding Sort option, when we preview the report again, we will notice sorted output as shown below:

How to sort columns in Matrices

Consider sample Dataset query for this example as shown below:

DECLARE @SalesbyMonth TABLE (Product VARCHAR(20),Months VARCHAR(20),MonthNo INT,Sales INT)
INSERT @SalesbyMonth SELECT 'A','Oct',10,1000
INSERT @SalesbyMonth SELECT 'A','Nov',11,2000
INSERT @SalesbyMonth SELECT 'A','Dec',12,3000
INSERT @SalesbyMonth SELECT 'B','Oct',10,1000
INSERT @SalesbyMonth SELECT 'B','Nov',11,2000
INSERT @SalesbyMonth SELECT 'B','Dec',12,3000
SELECT * FROM @SalesbyMonth

Create Matrix report based on above Dataset and then if the report is previewed , you will notice the report getting displayed as shown below :
(with default sorting of Columns):




If we want the report columns to be displayed in the ascending sort order based on MonthNo column, we can achieve this by:

Click on Months column - > right-click - > Column Group - > Column Groups Properties ... - >  Sorting - > Add - > mention Sort By = Column and Order = Ascending/Descending
or
At the bottom of the Design pane , Column Groups - > Column Groups Properties ... - >  Sorting - > Add - > mention Sort By = Column and Order = Ascending/Descending



After adding Sort option, when we preview the report again, we will notice sorted output as shown below:





How to sort rows in Tables (Interactive sorting)

Interactive Sort :

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.
This feature is supported only in rendering formats that support user interaction, such as HTML.

Example 1 : Interactive Sort option for Column headers of table

Consider sample Dataset query for this example as shown below :

DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
INSERT @Tmp SELECT 1,'C'
INSERT @Tmp SELECT 2,'B'
INSERT @Tmp SELECT 3,'A'
SELECT * FROM @Tmp

Create Table report based on the above Dataset.

To enable Interactive sorting for Column header Id ,
In the Design pane , click on the Column header Id - > right-click - > TextBox Properties - > Interactive Sorting

Similarly do for the Column header Name .

After adding interactive sort option, when we preview the report again, we will notice Up/down arrows in the column headers which will allow the users to perform interactive sorting as shown below :




Example 2 : Interactive Sort option for Column headers of table with group.

Consider sample Dataset query for this example is as shown below :

DECLARE @Tmp TABLE (Country VARCHAR(20) , State VARCHAR(20))
INSERT @Tmp SELECT 'US','Texas'
INSERT @Tmp SELECT 'US','New York'
INSERT @Tmp SELECT 'US','California'
INSERT @Tmp SELECT 'India','Mumbai'
INSERT @Tmp SELECT 'India','Kolkata'
INSERT @Tmp SELECT 'India','Chennai'
SELECT * FROM @Tmp

Create Table report based on above Dataset and then Create Row group on Country column and then if we Preview the report, we will notice as shown below :



Enable Interactive Sorting option for Column header Country as shown below :



Enable Interactive Sorting option for Column header State as shown below :



After adding interactive sort option, if we again preview the report,
Clicking arrow symbol on Country column header will sort the entire group
Clicking arrow symbol on state column header will sort rows within the group



How to sort rows in Tables (Dynamic sorting - by getting column name as input from user based on that column sorting has to be performed)

Dynamic sorting - ( by getting column name as input from user based on that column sorting has to be performed ) can be done in two ways :

Approach 1 : Using ORDER BY clause with CASE statement

Consider sample Dataset Stored procedure for this example is as shown below :

CREATE PROCEDURE DynmaicSort
@Sortby NVARCHAR(10)
AS
BEGIN
DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
INSERT @Tmp SELECT 1,'C'
INSERT @Tmp SELECT 2,'B'
INSERT @Tmp SELECT 3,'A'
 
SELECT * FROM @Tmp
ORDER BY CASE WHEN @Sortby = 'Name' THEN Name
              ELSE Id END
 
END

Dataset Properties is as shown below  :

Create Table report and then if we Preview the report , users will be prompted for input column name based on which report rows will be sorted .



Approach 2 : Writing expression for Sorting

Select the entire row in the Tablix , right-click - > Tablix Properties ... - >  Sorting - > Add - > write expression for sorting .

Refer to the answer provided by Heidi-Duan in this thread .

How to sort labels in X-axis (Horizontal axis) of Charts

Consider sample Dataset query for this example is as shown below :

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth

Create Column chart report based on above Dataset .



To display all labels on the X-axis .

If we Preview the report , Month labels on the X-axis will not be in proper order .



To sort labels in X-axis of charts , Under Chart Data - > Category Groups - > Category Group Properties ... -> Sorting



After adding sort option, if we again Preview the report, we will notice proper order of labels in X-axis



How to sort series groups in stacked charts

Consider sample Dataset query for this example is as shown below :

DECLARE @SalesbyMonth TABLE (Product VARCHAR(20),Months VARCHAR(20),MonthNo INT,Sales INT)
INSERT @SalesbyMonth SELECT 'A','Oct',10,1000
INSERT @SalesbyMonth SELECT 'A','Nov',11,2000
INSERT @SalesbyMonth SELECT 'A','Dec',12,3000
INSERT @SalesbyMonth SELECT 'B','Oct',10,1000
INSERT @SalesbyMonth SELECT 'B','Nov',11,2000
INSERT @SalesbyMonth SELECT 'B','Dec',12,3000
SELECT * FROM @SalesbyMonth

Create Stacked Column chart report based on above Dataset .



If we Preview the report , we will notice the series groups in default sorted order .




To sort series groups in stacked charts , Under Chart Data - > Series Groups - > Series Group Properties ... -> Sorting



After adding sort option, if we again preview the report, we will notice the
report with series groups sorted in the order we mentioned :



 Additional Resources

 See Also