Businesses are looking for more complex and interactive reports because they help gain valuable insights. One common report requirement is dynamic grouping which allows the users to choose which fields to group their data around.
In SSRS, dynamic grouping can be set up using the following steps.
Define the grouping parameters
To enable a dynamic grouping on a set of fields, create a parameter. List the fields you want to use for grouping and add labels. If you want grouping to be optional, also include null as a value (with a label like “None”).
- Click on Parameters, and then Add Parameter. In the Report Parameter Properties window, set the parameter properties.
- Select Available Values in the Report Parameter Properties window and add label and value pairs for all groups.
- Select Default Values in the Report Parameter Properties window and enter the desired default report settings.
- Click OK.
Repeat these steps for each group of fields that you wish to enable dynamic grouping.
Create the report with grouping
Create a table report with the dynamic groups you set up. Groups like month, year and category are very common.
Set the group expression
Open the Group Properties window and Add the desired group expression.
Here is an sample expression where we perform some basic checks on our Month, Year and Category grouping and set the default display.
iif (Parameters!Group1.Value is Nothing,1,Fields(iif(Parameters!Group1.Value is Nothing, “Year”,Parameters!Group1.Value)).Value)
Change the grouping display value (label) on basis of selected report parameter
The final step is to change the group label depending on the display value.
In this example, we are setting the Null value to display “Year” as the label in order to match the behaviour in the previous expression.
=Fields(if(Parameters!Group1.Value is Nothing “Year”, Parameters!Group1Value)).Value
And that is how you set up a very basic dynamic grouping. SSRS allows you to create some complex reports with sets of dynamic groupings
To learn more about OptimusBI and SSRS,
- visit our case study pages,
- comment on the article, or
- contact us with your questions.
