Formulas
Formulas are simple assignments of the form:
measure = expression
Measure can be any stored metrics measure (Opening, New, Expand, Churn, Closing, Notes). Virtual measures (Value) are not supported. Multiple assignments can be included in the same formula provided that each assignment targets a different measure.
Expression is a mathematical expression that describes how to calculate the measure. Standard operators and most scalar SQL functions are supported.
Operators
| Type | Operators |
|---|---|
| Mathematical | +, -, *, /, % |
| Logical | and, or, not |
Functions
Most Microsoft SQL scalar functions are supported. Of particular note, iif() and choose() can be used to implement conditional logic.
Function arguments are not verified at compile time. Incorrect function calls in formulas might result in all queries failing for a tenant.
Member References
Dimension members can be referred to in expressions by their name, enclosed in square brackets, i.e., [Revenue]. Members used in the expression must belong to the same hierarchy as the target member being calculated.
Cross references are currently limited only to measures, which must be specified as the second member of a tuple expression enclosed in parentheses, i.e., ([Revenue], [New]).
Comments
MDX/SQL style comments are supported:
- Single-line comments starting with
-- - Multi-line comments enclosed between
/*and*/
Dynamic Execution
When executing dynamic formulas, calculations are done after performing member aggregation. Results of dynamic calculations are not aggregated.
Static Execution (On Demand or Automatic)
Statically executed formulas store results in the database and their results will aggregate at query time. This feature is currently under development.
Example
-- Calculate Gross Margin Percentage
[New] = ([Revenue] - [Cost of Goods Sold]) / nullif([Revenue], 0)
Substitutions/Parametrization
Not currently supported.
Roadmap
- Member references by key, using
&[number]syntax. This will allow support for substitutions. - Runtime substitutions, limited to member keys, e.g.,
[New] = &[{Parameter.Account.0.Key}] + 100 - Support for cross-dimensional references, e.g.,
[New] = ([Rent Expense], [Department].[None]) * [SqFt] / nullif([SqFt], [Department].[*]), 0) - Static execution for formulas