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 and it will retrieve the Customer Name, Product Name and Sales Amount.

Note - I use AdventureWorksLT2008R2 Data Base,

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 am going to develop a report which shows product wise sales details for a single customer.

Create one data source pointing to AdventureWorksLT2008R2 and one dataset named as SALES_DATA with 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 null, as shown below:



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



Set Available value = none

Set the default Parameter, as shown below:



Step 4 - Go to Row group properties.



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 place holder name and 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 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 an customer.



Descending



Ascending