This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

Dynamic Grouping Based on Parameters

Dynamic grouping based on report's parameters allows users to change the grouping criteria of data in shared/published/embedded reports. This enables users to create more personalized and context-specific reports by selecting how they want data to be grouped - without having to create many similar reports (for each of these grouping options).

How dynamic grouping works in a report:

A user changes a special parameter ("Group By"), it affects the report's grouping criteria.
A pivot report uses a special dimension that is configured for dynamic grouping criteria (that depends on the "Group By" parameter). This dimension can resolve its "Label" and "Format" at the run-time and they can depend on the "Group By" selection too.
As a result, report's data grouping can be changed even in published reports/dashboards.

How to configure a dimension with dynamic grouping criteria

SQL DB

Add a report parameter "param_group_by" for grouping criteria selection:
Add a report parameter "param_group_by_sql" that calculates dimension's SQL (hidden):
Expression:
```
Sql.Raw(
  new dictionary{
    {"Year", "EXTRACT(YEAR FROM order_date)"},
    {"Month", "EXTRACT(MONTH FROM order_date)"},
    {"Day", "EXTRACT(DAY FROM order_date)"}
  }[ IfNull(Parameter["param_group_by"], "Year")]  )
```
Sql.Raw function is used to insert the value into SQL query 'as-is' (not as a constant).
new dictionary{} defines SQL expressions for allowed "param_group_by" choices and also guarantees that user-entered value is not inserted into SQL directly (to prevent a possibility of SQL-injections).
Optional Different grouping criteria may require dynamic format specified. If this is the case, add a report parameter "param_group_by_fmt" that calculates dimension's Format (hidden):
Expression:
```
new dictionary{
    {"Year","{0}"},
    {"Month","{MMM}"},
    {"Day", "{0}"}
  }[ IfNull(Parameter["param_group_by"], "Year")]
```
Add a dimenion "dynamic_group_by" based on parameters defined above:

Label = @param_group_by which means that it is resolved with parameter's value. Format = @param_group_by_fmt (keep it empty if format doesn't depend on the user's grouping criteria selection). Parameters = @param_group_by_sql which defines a custom SQL expression for this dimension that is resolved from the parameter's value.
Use "dynamic_group_by" in reports that should allow end-users to change a grouping criteria via "Group By" report parameter.