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,

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

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

Report preview

Report preview

Step 3

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

Create

Set Available value = none.

Set the default value null, as shown below:

values

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

Parameter

Set Available value = none

Set the default Parameter, as shown below:

Parameter

Step 4 - Go to Row group properties.

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)

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

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 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 an customer.

Result

Descending

Descending

Ascending

Ascending