Dynamics Ax provide 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. Some times Report level parameter is not work, then you have to define this parameter at Data contract class. And used it as report level parameter.
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)
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.
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
