SSRS - Interactive Sorting On Matrix Column Group

Introduction

SSRS Report Developer faces a common problem in implementing Interactive sorting on a column group in a matrix. Whenever we add interactive sorting on the column group, a sorting symbol will appear but does not make the change until we click it. This is one of the limitations of SSRS.

Now, we can implement Interactive sorting on the column group with the help of the following steps.

Step 1

Create a script as shown below to retrieve the Customer Name, Product Name, and Sales Amount.

Note. I use AdventureWorksLT2008R2 Database,

Select Top 50 D.CompanyName, C.Name,Sum(B.UnitPrice) as SalAmount  
From SalesLT.SalesOrderHeader As A   
inner join SalesLT.SalesOrderDetail As B on (A.SalesOrderID=B.SalesOrderID)  
inner join SalesLT.Product As C on (B.ProductID= C.ProductID)  
inner join SalesLT.Customer As D on (D.CustomerID=A.CustomerID)  
group by  
D.CompanyName,C.Name  

Step 2

Now, I will develop a report which shows product-wise sales details for a single customer.

Create one data source pointing to AdventureWorksLT2008R2 and one dataset named SALES_DATA with the query written in step 1.

Report preview

Step 3

Create a Parameter Named as Sel_Cal (This parameter stores the selected column name),

Set Available value = none.

Set the default value to null, as shown below:

Create another Parameter Named Sort_by (This parameter stores the Sorting type and is named as Asc/ Desc)

Set Available value = none

Step 4. Go to Row group properties. Set the default Parameter as shown below: