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:

users want to see the report in slightly different ways
this is an embedded report and its metric depend on the user's business role
report's metric is personalized and should take into account who looks at the report

How dynamic measures works in a report:

A user changes a special parameter ("Show metric") which should affect the report's values.
Pivot report uses a special measure that is configured for dynamic calculation that depends on "Show metric" parameter. This measure can resolve its "Label" and "Format" at run-time and depend on the "Show metric" selection too.
As a result, report's displays values according to the user's choice:

How to configure a dynamic measure

SQL DB

Add a report parameter "param_dynamic_metric" for metric's selection:
Add a report parameter "param_dynamic_metric_sql" that calculates measure's SQL (hidden):
Expression:
```
Sql.Raw(
  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).
Add a measure "dynamic_metric" based on parameters defined above:

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.