Saturday, July 22, 2023

SSRS Dynamic Visualization - How to dynamically change Row grouping & Column Grouping of Matrix in SSRS Report

If there is a requirement of dynamically changing of Row grouping & Column grouping of Matrix then it can be achive by using  report parameter & SSRS expression. 

Scanario:
Below is the sample report which shows Region & year wise sales information with
1) Region is in Row
2) Year is in Column

We can change this dynamically to below:
1) Year is in Row
2) Region is in Column
 




Following are the steps to convert Row grouping to Column Grouping or vice versa:
1. Create a parameter named RowGrouping and put the value as Region & Year
     


2) For Row Grouping & its sorting - use the below expression:
=IIF(Parameters!RowGrouping.Value = "Region",  Fields!Region.Value,Fields!Year.Value)
  

3) Change the same expression for Textbox value & color in Row Grouping
    



4) For Column Grouping & sorting - use the below expression:
=IIF(Parameters!RowGrouping.Value = "Region", Fields!Year.Value,Fields!Region.Value)
  

5) Change the same expression for Textbox value & color in Column Grouping
    



6) Now Run the report. select RowGrouping as Region and preview:
    


7) Now Run the report. select RowGrouping as Year and preview:
     
Conclusion: We can dynamically change Row & column grouping in SSRS using expression.

No comments:

Post a Comment