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:

How to configure a dimension with dynamic grouping criteria

  1. Add a report parameter "param_group_by" for grouping criteria selection:
    Dynamic grouping setup: add param_group_by parameter
  2. Add a report parameter "param_group_by_sql" that calculates dimension's SQL (hidden):
    Dynamic grouping setup: add param_group_by_sql parameter Expression:
      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).
  3. 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):
    Dynamic grouping setup: add param_group_by_fmt parameter Expression:
    new dictionary{
        {"Day", "{0}"}
      }[ IfNull(Parameter["param_group_by"], "Year")]
  4. Add a dimenion "dynamic_group_by" based on parameters defined above:
    Dynamic grouping setup: add dynamic_group_by dimension
    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.
  5. Use "dynamic_group_by" in reports that should allow end-users to change a grouping criteria via "Group By" report parameter.