Variables

Variables (also known as substitutions) are an integral part of the query mechanism that allows users and developers to create generic queries that can run in a variety of contexts and can be parametrized.

Predefined Variables

The following variables are available in all contexts and can be used as {variable_name}:

Variable	Description
Username	Username of the user executing the query, e.g., joe@unasoft.app
Tenant	Name of the current tenant, e.g., Core Demo

Dimension/Model Variables

The following variables are available when executing model queries (usually SQL model queries). Use as {variable_name}:

Dimension Names

Variable	Description	Example
Dimension.Account	Name of the account dimension	KPI
Dimension.Scenario	Name of the scenario dimension	Scenario
Dimension.Stage	Name of the stage dimension	Stage
Dimension.Time	Name of the time dimension	Period

Dimension Tables

Variable	Description	Example
DimTable.Account	Account dimension main hierarchy table	[dbo].[Dim_PC_KPI]
DimTable.Scenario	Scenario dimension main hierarchy table	[dbo].[Dim_PC_Scenario]
DimTable.Stage	Stage dimension main hierarchy table	[dbo].[Dim_PC_Stage]
DimTable.Time	Time dimension main hierarchy table	[dbo].[Dim_PC_Period]

Model Tables

Variable	Description	Example
Model.BTable	Diagnostic table for the query model	[dbo].[B_Main]
Model.CTable	Cohort table for the query model	[dbo].[C_Main]
Model.CBTable	Customer balances table for the query model	[dbo].[CB_Main]
Model.MTable	Metrics table for the query model	[dbo].[M_Main]
Model.STable	Stats table for the query model	[dbo].[S_Main]
Model.TTable	Transaction table for the query model	[dbo].[T_Main]

Global Variables

Global variables are defined in the [dbo].[App_GlobalVariables] table in the tenant database, so their availability and value depend on the current tenant.

To prevent conflicts with other variables, global variables must be referenced as {Global.variable_name}.

Parameters

Parameters are available in reports and dashboards.

Dashboard Parameters

Parameters that refer to a dimension can be referenced as:

{Parameter.dimension_name.filter_set.Key}

Examples:

• {Parameter.Scenario.0.Key}
• {Parameter.{Dimension.Time}.1.Key}

Report Parameters

Parameters can be referenced as {Parameter.parameter_name}.

Functions

Functions are similar to variables. A construct {function(parameters)} will be replaced with the result of the function call.

DimensionFilter

DimensionFilter(tableAlias [, filterSetNo [, excludeField ...]])

Available when running SQL model queries for a widget. Produces SQL code that will filter the table identified by tableAlias with the associated set of filters for the widget.

Parameters:

• tableAlias: The query alias used for the table
• filterSetNo: The dashboard filter set number (defaults to 0 for the first set)
• excludeField: A list of fields that should not be included in the filter

Example:

SELECT *
FROM {Model.MTable} t
WHERE t.[ScenarioId] = 1
  AND t.[PeriodId] BETWEEN 202401 AND 202412
  {DimensionFilter(t, 0, ScenarioId, PeriodId)}

This will execute something like:

SELECT *
FROM [dbo].[M_Main] t
WHERE t.[ScenarioId] = 1
  AND t.[PeriodId] BETWEEN 202401 AND 202412
  AND (4 = 0 OR [t].[KpiId] = 4)         -- current selected KPI
  AND (12 = 0 OR t.[S_OwnerId] = 12)     -- current selected Owner
  AND (2 = 0 OR t.[S_MarketId] = 2)      -- current selected market

note

DimensionFilter does not currently support non-leaf, non-root member selection for filters.

Order of Execution

1. Variable substitution is performed first, recursively up to 5 times (to allow variables containing other variables)
2. Function substitution is performed after variable substitution, only once

caution

Function results cannot contain variables or other function calls.