Formulas vs. Calc Scripts
Formulas calculate one member (for instance an account) wherever it appears, and can run at query time. Calc scripts calculate any slice of the model chosen by their scope, and always store their results. The tables below compare what each one supports.
Structure and Execution
| Formulas | Calc Scripts | |
|---|---|---|
| What is calculated | One member of one dimension, for one or more measures | Any cells selected by scope, through one or more assignments |
| Execution | Dynamic, On Demand, Automatic | On Demand |
| Run from | Queries (Dynamic), workflow tasks, SQL, data saves (Automatic) | Workflow tasks, SQL |
| Scope | Members + relationship per dimension, set in the editor | Set expressions, nested scope blocks, Filter() by property |
| Protecting cells | — | freeze |
| Which cells are calculated | Dynamic: every queried cell in scope. On Demand / Automatic: every base cell in scope | Existing cells only, unless dimensions are set to dense |
| Only aggregated cells | ✅ Applies to: Aggregated cells only (Dynamic) | — |
| Parameters | — (On Demand procedures accept scope arguments) | param |
| Reuse | — | Variables, define / call |
| Writing | Standard writeback | Standard writeback, or set direct_write on for large writes |
| Empty result | Clears the cell's value | Clears the cell's value |
| Notes (text) measure | ✅ | — |
Expressions
| Formulas | Calc Scripts | |
|---|---|---|
| Member references by name / key / tag | ✅ (names without a dimension refer to the calculated dimension) | ✅ (dimension always required) |
| Cross-dimensional tuples | ✅ | ✅ |
Measure on its own, e.g. [@Measure].[Opening] | ✅ (not the assigned measure in Automatic formulas) | ✅ |
CurrentMember and Parent, Ancestor(n), PrevMember, NextMember, Lag(n), Lead(n) | ✅ | ✅ |
Member sets: Children, Descendants, Siblings, First, Last, FirstLeafDescendant, Prev(n), … | — | ✅ (values are added up) |
KeyToMember, StrToName, StrToMember, StrToKey, StrToTag | ✅ (resolved for each cell) | ✅ |
Member property values .Property("Name") | ✅ (type-checked when compiled) | ✅ |
lag(expr, n) / lead(expr, n) | ✅ | — (use .Lag(n) / .Lead(n) on the time member) |
Stored() | ✅ (Dynamic) | — (a reference to the cell being calculated reads its stored value) |
IsActual() | ✅ | ✅ |
Simple_Agg() | — | ✅ |
Operators: + - * / %, = <> < > <= >= like, and or not, is [not] null | ✅ | ✅ |
null, true, false, strings in '…' or "…" | ✅ | ✅ |
| Scalar functions (mathematical, string, conditional, date, conversion, …) | ✅ | ✅ (the same list) |
Choosing
- Use a Dynamic formula for values that must always reflect the latest data (ratios, variances, run-rates) and for anything that should only be calculated on totals.
- Use an Automatic formula for stored values that depend on a few other members and must stay in step with them.
- Use a calc script for allocations, roll-forwards, seeding a plan, or any calculation over many members that is run as a step of a process.