Formula Syntax

Casabase Cube formulas are expressions associated with members in a dimension. When a member with a formula is queried, the formula is evaluated dynamically within the current multidimensional context.

Formula expressions can combine:

• Member references
• Cross-dimensional references
• Dynamic member references
• Arithmetic and comparison operators
• Conditional expressions
• Supported Snowflake SQL functions
• Hierarchy-aware functions
• Time navigation
• References to other calculated members

This page covers the core Casabase Cube formula language. Specialized hierarchy and time functions are covered separately.

Member References

Members can be referenced using either qualified or unqualified syntax.

Qualified Member References

The standard qualified syntax is:

[DIMENSION].[Member]

For example:

[ACCOUNT].[Revenue]

[ACCOUNT].[Cost of Sales]

[SCENARIO].[Actual]

A qualified reference explicitly identifies both the dimension and member.

For example:

[ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]

could be used to calculate Gross Profit.

Member references use member names, not aliases.

Unqualified Member References

Members can also be referenced without explicitly specifying the dimension:

[Member]

For example:

[Revenue]

or:

[Actual]

An unqualified reference is resolved against the cube’s member definitions.

Qualified references are generally clearer when a formula contains references to multiple dimensions or when the same member name could exist in more than one dimension.

Current Multidimensional Context

Every formula is evaluated within a multidimensional context.

For example, assume the current result is being evaluated at:

ACCOUNT  = Gross Profit
ENTITY   = North America
SCENARIO = Actual
YEARS    = FY26
PERIOD   = Jan

A formula reference such as:

[ACCOUNT].[Revenue]

changes the ACCOUNT coordinate to Revenue.

The remaining dimensions retain their current coordinates:

ACCOUNT  = Revenue
ENTITY   = North America
SCENARIO = Actual
YEARS    = FY26
PERIOD   = Jan

This context-preserving behavior is fundamental to Casabase Cube formula evaluation.

A formula does not need to specify every dimension in the cube. It only needs to identify the coordinates that should differ from the current evaluation context.

Cross-Dimensional References

Formulas can override coordinates from multiple dimensions to retrieve a value from another multidimensional intersection.

Casabase Cube supports two forms of cross-dimensional reference:

• Arrow syntax
• Tuple syntax

Arrow Syntax

Use the -> operator to combine member references from multiple dimensions.

For example:

[ACCOUNT].[Revenue]->[SCENARIO].[Actual]

This evaluates the value using:

ACCOUNT  = Revenue
SCENARIO = Actual

while all dimensions not explicitly referenced retain their current evaluation context.

Additional dimensions can be chained:

[ACCOUNT].[Revenue]
  ->[SCENARIO].[Actual]
  ->[VIEW].[Periodic]

Conceptually:

Current Multidimensional Context
             │
             ▼
Override ACCOUNT
with Revenue
             │
             ▼
Override SCENARIO
with Actual
             │
             ▼
Override VIEW
with Periodic
             │
             ▼
Evaluate Referenced Value

This makes cross-dimensional calculations possible without specifying the complete coordinates of the cube.

For example:

[ACCOUNT].[Revenue]->[SCENARIO].[Actual]
-
[ACCOUNT].[Revenue]->[SCENARIO].[Budget]

compares Actual Revenue with Budget Revenue.

If the current context also contains:

ENTITY = North America
YEARS  = FY26
PERIOD = Jan

those coordinates remain unchanged for both references.

Tuple Syntax

Cross-dimensional intersections can also be expressed using tuple syntax.

For example:

(
    [ACCOUNT].[Revenue],
    [SCENARIO].[Actual]
)

identifies the same coordinates as:

[ACCOUNT].[Revenue]->[SCENARIO].[Actual]

Additional dimensions can be included:

(
    [ACCOUNT].[Revenue],
    [SCENARIO].[Actual],
    [VIEW].[Periodic]
)

Both forms specify the coordinates that should override the current multidimensional context.

Arrow syntax is often convenient when an expression naturally reads as a sequence of coordinate shifts:

[ACCOUNT].[Revenue]->[SCENARIO].[Actual]

Tuple syntax is useful when expressing a multidimensional intersection as a grouped set of coordinates:

(
    [ACCOUNT].[Revenue],
    [SCENARIO].[Actual]
)

Dimensions that are not included continue to use the current evaluation context.

Dynamic Member References

Member names do not always have to be fixed literals.

Casabase Cube supports dynamically constructed member references using expressions inside braces.

For example:

[ICP].[{'ICP_' || ENTITY}]

The expression:

{'ICP_' || ENTITY}

constructs a member name dynamically from the current value of ENTITY.

If:

ENTITY = 100

the expression could resolve to:

ICP_100

which is then used as the member coordinate for the ICP dimension.

Dynamic references can also participate in cross-dimensional expressions:

[DATATYPE].[Adjusted]->[ICP].[{'ICP_' || ENTITY}]

This capability is useful when corresponding members in different dimensions follow a predictable naming convention.

Dynamic member expressions must resolve to valid member names at query time.

Current Cell: {ROW}

Use:

{ROW}

to reference the current cell’s natural value before the member formula is applied.

This is the value produced from stored data and normal hierarchy roll-up at the current multidimensional coordinate.

For example:

{ROW} * 1.05

applies a 5% adjustment to the current value.

{ROW} is particularly useful when the formula should modify or evaluate the value that would otherwise be returned for the current cell.

For example:

CASE
    WHEN {ROW} < 0 THEN 0
    ELSE {ROW}
END

returns zero for negative values while preserving positive values.

Note

{ROW} refers to the current cell’s stored or rolled-up value. It is not a reference to an entire query-result row.

Natural Hierarchy Aggregation with AGG_SELF()

Use:

AGG_SELF()

to retrieve the natural hierarchy aggregation for the member carrying the formula.

AGG_SELF() evaluates the member’s normal operator-weighted roll-up without evaluating that member’s formula.

This is useful when a formula should behave differently at certain levels of a hierarchy while preserving normal aggregation elsewhere.

For example:

CASE
    WHEN ISLEAF(PERIOD) THEN
        [VIEW].[Periodic]
    ELSE
        AGG_SELF()
END

At a leaf member, the formula uses the Periodic value.

At an upper-level member, it returns the normal hierarchy aggregation.

Conceptually:

Current Formula Member
        │
        ▼
    AGG_SELF()
        │
        ▼
Normal Child Roll-Up
        │
        ▼
Operator-Weighted
 Aggregated Value

AGG_SELF() is different from referencing another member. It specifically requests the natural aggregation of the member to which the formula is attached.

Arithmetic Expressions

Standard arithmetic operators can be used in formulas.

Examples:

[ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]

[ACCOUNT].[Gross Profit]
/
NULLIF([ACCOUNT].[Revenue], 0)

(
    [SCENARIO].[Actual] - [SCENARIO].[Budget]
)
/
NULLIF([SCENARIO].[Budget], 0)
* 100

Parentheses can be used to control expression evaluation:

(
    [ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]
)
/
NULLIF([ACCOUNT].[Revenue], 0)
* 100

Comparison Operators

Comparison operators can be used in conditional expressions.

Supported comparison forms include:

=
<>
>
>=
<
<=

For example:

[ACCOUNT].[Revenue] > 0

or:

SCENARIO = 'Actual'

Comparison expressions are commonly used with CASE, IIF, and hierarchy-aware functions.

CASE Expressions

Use SQL-style CASE expressions for conditional calculations.

For example:

CASE
    WHEN [ACCOUNT].[Revenue] = 0 THEN NULL
    ELSE
        [ACCOUNT].[Gross Profit]
        /
        NULLIF([ACCOUNT].[Revenue], 0)
        * 100
END

Multiple conditions can be evaluated:

CASE
    WHEN [ACCOUNT].[Revenue] < 0 THEN -1
    WHEN [ACCOUNT].[Revenue] = 0 THEN 0
    ELSE 1
END

Nested CASE expressions can also be used for more complex calculation logic.

IIF Expressions

IIF provides a compact alternative for simple conditional expressions.

The general form is:

IIF(condition, value_if_true, value_if_false)

For example:

IIF([ACCOUNT].[Revenue] > 0, 1, 0)

Use CASE when several conditions or branches are required and IIF when a simple true/false expression is sufficient.

Supported Math and Value Functions

Casabase Cube supports a defined set of math and value functions in native member formulas.

Function	Purpose
ABS(x)	Absolute value
ROUND(x [, digits])	Round to the requested number of decimal places
TRUNC(x [, digits])	Truncate toward zero
CEILING(x) / CEIL(x)	Round up to the next integer
FLOOR(x)	Round down to the previous integer
MOD(x, y)	Remainder of x divided by y
POWER(x, y)	Raise x to the power y
SQRT(x)	Square root
EXP(x)	e raised to the power x
LN(x)	Natural logarithm
LOG(base, x)	Logarithm using the specified base
GREATEST(a, b, ...)	Largest argument
LEAST(a, b, ...)	Smallest argument
NULLIF(a, b)	NULL when the arguments are equal
COALESCE(a, b, ...)	First non-NULL argument
IIF(condition, true_value, false_value)	Inline conditional

Only functions explicitly documented as supported should be used in native Casabase Cube member formulas.

Preventing Division by Zero

Formulas that divide one value by another should account for a denominator of zero.

Instead of:

[ACCOUNT].[Gross Profit]
/
[ACCOUNT].[Revenue]
* 100

use:

[ACCOUNT].[Gross Profit]
/
NULLIF([ACCOUNT].[Revenue], 0)
* 100

If Revenue is zero, NULLIF resolves the denominator to NULL rather than causing a divide-by-zero error.

This pattern is especially important for ratios, percentages, rates, and growth calculations.

Referencing Other Calculated Members

A formula can reference another member that also has a formula.

For example, if Gross Profit is defined as:

[ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]

another calculated member can reference it:

[ACCOUNT].[Gross Profit]
/
NULLIF([ACCOUNT].[Revenue], 0)
* 100

Casabase Cube detects calculated-member dependencies automatically and determines the required evaluation sequence.

Circular dependencies are detected and rejected.

See Solve Order & Dependencies for details.

Hierarchy-Aware Expressions

Formulas can inspect the current member’s location, relationships, and metadata within a hierarchy.

Examples include determining whether a member:

• Is a leaf
• Belongs to a particular generation or level
• Is a descendant or ancestor of another member
• Is a child, parent, or sibling of another member
• Has a particular UDA
• Is associated with an attribute

For example:

ISLEAF(ACCOUNT)

or:

ISDESC(ENTITY, 'Total Entity')

Hierarchy-aware functions are covered in detail in Hierarchy-Aware Functions.

Time Navigation

Formulas can navigate relative positions in recognized time dimensions using LAG and LEAD.

For example:

[YEARS].[LAG(YEARS, 1)]

references the previous member in the YEARS sequence.

Time navigation has specific rules about eligible dimensions, member ordering, and boundary conditions.

See Time Navigation for details and Common Formula Patterns for practical time-based calculations.

Native Syntax and Essbase Syntax

Native Casabase Cube formulas and Oracle Essbase formulas are different formula languages.

When an Oracle Essbase or Oracle Cloud EPM cube is migrated, formulas associated with members in the source outline are translated into native Casabase Cube formula syntax.

For example, Essbase-specific constructs such as:

@FUNCTION(...)

are import syntax, not native Casabase Cube formula syntax.

Conceptually:

Essbase Member Formula
        │
        ▼
Formula Translation
        │
        ▼
Native Casabase Cube Formula
        │
        ▼
Query-Time Evaluation

Users creating formulas directly in Casabase Cube should use native Casabase Cube syntax.

Syntax Restrictions

Formula expressions are evaluated by the Casabase Cube calculation engine within Snowflake, but a member formula is not an unrestricted SQL statement.

A formula defines the value of a member. It should contain an expression that can be evaluated for the current multidimensional context.

Formula expressions should not be used to perform operations such as:

• Querying arbitrary tables
• Executing DDL or DML
• Creating or modifying Snowflake objects
• Performing member-set selection that belongs in the query POV
• Implementing Dynamic Time Series accumulation such as YTD, QTD, or MTD

The following constructs can parse successfully but are not supported for native member-formula execution:

Unsupported Construct	Use Instead
{TOTAL} or {TOTAL:DIM}	Explicit tuple references pinned to the required top members
{PREV} or {NEXT}	LAG or LEAD
CHILDREN(...), DESCENDANTS(...), LEAVES(...), MEMBERS(...), SIBLINGS(...), CROSSJOIN(...) and corresponding dot-navigation forms	Query POV set selection, AGG_SELF(), or Dynamic Time Series as appropriate
STRTOMBR(...)	Dynamic member syntax such as [DIM].[{expression}]
SUM, AVG, COUNT, MIN, MAX, MEDIAN, STDDEV, or RANK over member sets	AGG_SELF(), Dynamic Time Series, or arithmetic over explicit coordinate references

A formula that passes VALIDATE_FORMULA can still be refused at query time when it uses one of these unsupported execution constructs. Always test the formula with a representative query before production use.

Use the appropriate Casabase Cube capability for operations that do not belong in a member formula.

Formula Syntax Summary

Purpose	Syntax
Qualified member	[DIMENSION].[Member]
Unqualified member	[Member]
Arrow coordinate reference	[DIM1].[Member1]->[DIM2].[Member2]
Tuple coordinate reference	([DIM1].[Member1], [DIM2].[Member2])
Dynamic member	[DIM].[{'prefix_' || OTHER_DIM}]
Current cell value	{ROW}
Natural member aggregation	AGG_SELF()
Arithmetic	+, -, *, /
Comparison	=, <>, >, >=, <, <=
Conditional logic	CASE ... END
Simple conditional	IIF(condition, true, false)
Math / value function	Explicitly documented supported functions
Time navigation	LAG, LEAD
Hierarchy logic	Hierarchy-aware functions

Key Takeaways

• Formulas are evaluated within the current multidimensional context.
• Use [DIMENSION].[Member] for explicit member references.
• Unqualified [Member] references are also supported.
• Referencing a member overrides that dimension’s current coordinate while preserving other dimension coordinates.
• Use -> or tuple syntax to specify multiple coordinates.
• Dynamic member expressions can construct member names at runtime.
• {ROW} returns the current cell’s natural stored or rolled-up value.
• AGG_SELF() returns the formula member’s natural hierarchy aggregation without evaluating its formula.
• Standard arithmetic, comparison, conditional, and supported Snowflake SQL functions can be used in formulas.
• Calculated members can reference other calculated members.
• Formula dependencies and circular references are detected automatically.
• Hierarchy-aware functions and time navigation extend the core formula language.
• Essbase formula syntax is translated during migration and is not the native language used to author Casabase Cube formulas.

Related Topics

• Formula Editor
• Hierarchy-Aware Functions
• Time Navigation
• Solve Order & Dependencies
• Formula Validation
• Common Formula Patterns