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.
The data used for this example is below, this has been stored in SQL and the data set query was SELECT * FROM Cars
Data that has been used for the report
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.
Parameters matching column names
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 image below shows the grouping expression.
The expression used for grouping and for group text
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.
The report in design mode
Below 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.