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:

Given below is the expression in the group sorting.

A to Z Sorting

=iif(Parameters!Sort_by.Value="ASC",Sum(iif(Fields!Name.Value=Parameters!Sel_Col.Value,Cint(Fields!SalAmount.Value),0)),0)

Z to A Sorting

=iif(Parameters!Sort_by.Value="DESC",Sum(iif(Fields!Name.Value=Parameters!Sel_Col.Value,Cint(Fields!SalAmount.Value),0)),0)

Step 5. Now add one placeholder in the column group field [Name],

Provide the placeholder name and the below expression in the value.

=iif(Fields!Name.Value=Parameters!Sel_Col.Value,switch(Parameters!Sort_by.Value="ASC",Chrw(104),Parameters!Sort_by.Value="DESC",Chrw(105)),Chrw(69))

Go to Font properties ->Change the font to Wingdings 3,

Go to Action properties -> Configure as shown below. Map Sel_col Parameter with the name field.

Give below is the expression in the Sort_By Parameter value:

Step 6. Now drag and drop the two parameters inside the report designer page like below to know how parameter value changes on run time.

Result

Now I achieve an interactive sorting on the column group filed [Name] (i.e.) product Name so the end user can sort the individual product ascending and descending based on the sales amount of a customer.

Descending

Ascending

Summary

This article taught us about Interactive Sorting On Matrix Column Group in SSRS.