How to Set Up Dynamic Grouping in SSRS

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”).

  1. Click on Parameters, and then Add Parameter. In the Report Parameter Properties window, set the parameter properties.
    SSRS Add Parameter
  2. Select Available Values in the Report Parameter Properties window and add label and value pairs for all groups.
    SSRS Set Available Values
  3. Select Default Values in the Report Parameter Properties window and enter the desired default report settings.
    SSRS Default Values
  4. 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.

SSRS Report Grouping

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)

The outer iif checks for empty parameters and displays the entire group if this condition is met.
Otherwise, the second iif checks if the parameter value is Nothing which means none is selected from the drop-down and then it will return the value of the Year field without using it for any sorting or grouping.

SSRS Group Properties

 

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

SSRS Group Display

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,

The post How to Set Up Dynamic Grouping in SSRS appeared first on OptimusBI.