SSRS Report – Dynamic Grouping at Report Level

I found myself in trouble when some users would like to view the total amount grouped by the employee while some users would like to view the total amount by the shipping method or even by the vendor!!! Then, dynamic grouping came across my mind, which could be used to easily achieve this design.

HOW???

1. Create a Parameter (named as GroupingType), set 3 values in this parameter = Employee, Vendor and Shipping.

2. At report level, in the row group’s properties, set the group on expression as shown below:

=Switch(Parameters!GroupingType.Value = “E”, Fields!FirstName.Value, Parameters!GroupingType.Value = “V”, Fields!vendor.Value, Parameters!GroupingType.Value = “S”, Fields!shipname.Value)

By doing that, the report could be grouped by 3 levels depending on the selected Parameter. Easy and Cool!!!

Any side effect???

Yes, it has a painful impact on report loading time. However, if your data is aggregated or relatively small in volume, then don’t bother! Else, I would suggest you avoid the dynamic grouping at report level and I have shared this in another post – SSRS Performance Improvement III – Avoid Dynamic Grouping at Report Level

Please feel me to email me via Contact if you would like to get my sample.

Just try it yourself!!!