Introduction

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
Image showing the data used in the report (Make, Model, Year, Colour & Price of a car)
Data that has been used for the report

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.
Image showing the parameter settings
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.

Image showing the report in design mode
The report in design mode

Result

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.

Image showing the report grouped by Make
Report grouped by Make
Image showing the report grouped by Year
Report grouped by Year

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Close Menu
%d bloggers like this: