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  

SSRS Interactive Sorting

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.

SSRS Interactive Sorting

Report preview

SSRS Interactive Sorting

Step 3

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

SSRS Interactive Sorting

Set Available value = none.

Set the default value to null, as shown below:

SSRS Interactive Sorting

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

SSRS Interactive Sorting

Set Available value = none

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

SSRS Interactive Sorting

 

SSRS Interactive Sorting

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)

 A to Z Sorting

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)

Z to A Sorting

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

add

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))

value

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

properties

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

 Action properties

Give below is the expression in the Sort_By Parameter value:

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.

parameters

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.

Result

Descending

Descending

Ascending

Ascending

Summary

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


Similar Articles