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

Dynamic Measures Based on Parameters

Dynamic measures can calculate different metrics depending on the report's user-defined parameters. With dynamic measures you can avoid same report duplication when the only difference is a metric (or its calculation). A primary use cases for dynamic measures are:

How dynamic measures works in a report:

How to configure a dynamic measure

  1. Add a report parameter "param_dynamic_metric" for metric's selection:
    Dynamic measure setup: add 'param_dynamic_metric' parameter
  2. Add a report parameter "param_dynamic_metric_sql" that calculates measure's SQL (hidden):
    Dynamic measure setup: add 'param_dynamic_metric_sql' parameter Expression:
      new dictionary{
        {"Sales Amount", "unit_price*quantity"},
        {"Items Delivered", "quantity"}
      }[ IfNull(Parameter["param_dynamic_metric"], "Sales Amount")] )
    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_dynamic_metric" choices and also guarantees that user-entered value is not inserted into SQL directly (to prevent a possibility of SQL-injections).
  3. Add a measure "dynamic_metric" based on parameters defined above:
    Dynamic measure setup: add 'dynamic_metric' measure
    Label = @param_dynamic_metric which means it is resolved with parameter's value. Type = Sum if all dynamic metrics have the same aggregate function but different columns. This can be FirstValue otherwise. Parameters = @param_dynamic_metric_sql which resolves a column name (or custom SQL expression) dynamically.