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.

CCBADG parameter.png

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)

CCBADG dynamic grouping

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

CCBADGb report desgin

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s