Dynamic Grouping using SQL Report Builder

1 minute read

One of the requirements that came in recently was for some reports to be created, upon investigation the reports were the same but grouped differently. Rather than creating individual reports, I wondered if there was a way to allow the user to select the grouping method within the report itself, which would mean less duplication of reports.

This post will go through a simple report which will allow the user to select whether to group some data on the Make, Model or Year of a car. The post will not cover how to create a group or create a report as it is assumed this is already known.

Data

The data used for this example is below, this has been stored in SQL and the data set query was SELECT * FROM Cars

Parameters

The first step to allow the user to select the grouping method is to create a parameter for them to select from. Below shows the parameters used for this report, the label can be anything, but the value should be the column name. In this example, the parameter has been called GroupBy.

The selected parameter value can then be used in the grouping & name expression. This works as the value of the parameter will be used to find the field value. The grouping expression below can then be used.

=Fields(Parameters!GroupBy.Value).Value

The design of the report looks the same as any other grouping, other than the value being calculated by an expression. The header for the grouping column could be set by using the GroupBy value if needed.

Results

The below images shows the report running with two different grouping methods selected. As you can see, this is a simple but effective method of allowing the user to group the information in a way that they want to see.