Report Solution Patterns and Recipes - Professional SQL Server 2005 Reporting Services - page 305

Figure 7-43

Dynamic Grouping
I get called in to consulting client sites often to rebuild a set of existing Crystal or Access reports in
Reporting Services. They will typically have several reports for each data entity or table that are
grouped, sorted, and filtered a little differently. This solution allows you to consolidate data groups for
the same table into one report. The following example consolidates the reports Product Sales by
Territory, Product Sales by Category, and Product Sales by Subcategory into one report with user-
selectable field grouping.

What you'll need:

❑ A table presenting a columnar set of grouped values.

❑ A report parameter with a list of fields in the data set for sorting.

❑ A group expression using the report parameter to resolve the grouping field name.

❑ Heading and summary expressions referring to the dynamic group expression.

This works best with simple columnar reports that have one group. The dynamic grouping is based on a
single parameter selection that returns the name of a field from the report's main data set. Figure 7-44
shows the settings for the GroupBy parameter. Note that the Value column for the parameter list con-
tains the actual field names for selected fields in the data set.