Common Formula Patterns

This page provides common patterns for building member formulas in Casabase Cube.

The examples are intended as starting points. Dimension and member names should be adapted to match your cube.

For detailed syntax and behavior, see:

• Formula Syntax
• Hierarchy-Aware Functions
• Time Navigation
• Solve Order & Dependencies

Variance

A variance calculates the difference between two members.

For example, Actual versus Budget:

[SCENARIO].[Actual] - [SCENARIO].[Budget]

Dimensions that are not explicitly referenced retain their current evaluation context.

For example, if the current query context includes:

ACCOUNT = Revenue
ENTITY  = East
PERIOD  = Jan

the formula compares Actual and Budget at that same Account, Entity, and Period intersection.

Depending on the business definition, the variance direction can be reversed:

[SCENARIO].[Budget] - [SCENARIO].[Actual]

Variance Percentage

A variance percentage divides the variance by the comparison value.

For example:

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

Conceptually:

Actual - Budget
─────────────── × 100
     Budget

NULLIF prevents a zero comparison value from causing a divide-by-zero error.

Ratios and Percentages

Ratios commonly divide one member by another.

For example:

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

For a percentage:

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

The dimensions not explicitly referenced remain at their current coordinates.

Gross Margin %

Gross Margin can be calculated directly:

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

If Gross Profit already exists as a calculated member, it can be reused:

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

This creates a calculated-member dependency:

Revenue ───────────────┐
                      │
Cost of Sales ─────────┤
                      ▼
                 Gross Profit
                      │
                      ▼
                 Gross Margin %

Casabase Cube detects the dependency and determines the required formula evaluation sequence.

Percentage of Another Member

The same pattern can express one member as a percentage of another:

[ACCOUNT].[Operating Expenses]
/
NULLIF([ACCOUNT].[Revenue], 0)
* 100

This pattern can be used for calculations such as:

• Expense as a percentage of revenue
• Cost as a percentage of sales
• Profit as a percentage of revenue
• Units as a percentage of capacity

Cross-Dimensional Calculations

Use cross-dimensional references when a calculation needs to explicitly change more than one dimensional coordinate.

For example:

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

references Revenue at Actual while retaining the current coordinates of dimensions not explicitly referenced.

A formula can compare two cross-dimensional intersections:

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

This explicitly compares Actual Revenue with Budget Revenue while preserving the current Entity, Period, Year, and other query coordinates.

See Formula Syntax for cross-dimensional and tuple syntax.

Conditional Calculations

Use CASE when a calculation depends on one or more conditions.

For example:

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

For a simple true/false condition, IIF can be used:

IIF(
    [ACCOUNT].[Revenue] = 0,
    NULL,
    [ACCOUNT].[Gross Profit] / [ACCOUNT].[Revenue] * 100
)

Sign Reversal

A simple sign reversal can use:

{ROW} * -1

If sign behavior is driven by member metadata, combine the calculation with a hierarchy-aware test.

For example:

CASE
    WHEN HASUDA(ACCOUNT, 'EXPENSE') THEN
        {ROW} * -1
    ELSE
        {ROW}
END

This reverses the sign only for Account members carrying the EXPENSE UDA.

UDA-Driven Calculations

HASUDA can make formula behavior dependent on User-Defined Attributes.

For example:

CASE
    WHEN HASUDA(PRODUCT, 'SPECIAL_RATE') THEN
        {ROW} * 1.05
    ELSE
        {ROW}
END

The formula follows the metadata assigned to each Product member rather than requiring every applicable member to be listed explicitly.

See Hierarchy-Aware Functions for UDA behavior.

Attribute-Driven Calculations

ISATTR can make formula behavior dependent on an attribute association.

The syntax is:

ISATTR(attribute_dimension, 'attribute_member')

For example, assume PRIMARY_UOM is an attribute dimension associated with Product.

A formula could apply a unit conversion only to products whose PRIMARY_UOM is Case:

CASE
    WHEN ISATTR(PRIMARY_UOM, 'Case') THEN
        {ROW} * [ACCOUNT].[Units Per Case]
    ELSE
        {ROW}
END

ISATTR tests the current base member’s attribute association. The attribute dimension does not need to be included in the query POV for the test to work.

Attribute associations are evaluated at leaf grain on the associated base dimension.

See Hierarchy-Aware Functions for attribute behavior and restrictions.

Leaf-Level Calculation with Natural Aggregation

Some calculations should be evaluated at leaf members while upper-level members should use the hierarchy’s natural roll-up.

For example:

CASE
    WHEN ISLEAF(ACCOUNT) THEN
        [ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]
    ELSE
        AGG_SELF()
END

Conceptually:

Leaf Member
     │
     ▼
Evaluate Formula


Upper-Level Member
     │
     ▼
Natural Hierarchy Roll-Up

AGG_SELF() returns the member’s natural operator-weighted aggregation without evaluating the member’s formula.

This pattern is useful when recalculating the expression independently at an upper-level member would not represent the intended business behavior.

Hierarchy Branch Calculation

Use a hierarchy relationship predicate when a calculation applies to a structural branch.

For example:

CASE
    WHEN ISDESC(ACCOUNT, 'Operating Expenses') THEN
        {ROW} * -1
    ELSE
        {ROW}
END

This applies the calculation to descendants of Operating Expenses.

Because ISDESC is strict, it does not include Operating Expenses itself. Use the corresponding inclusive relationship predicate when the referenced member should also match.

Parent-Based Logic

Use PARENT when behavior depends on the current member’s immediate parent.

For example:

CASE
    WHEN PARENT(PERIOD) = 'Q1' THEN
        {ROW} * 1.05
    ELSE
        {ROW}
END

If the formula only needs a Boolean relationship test, ISCHILD can express the same type of condition directly:

ISCHILD(PERIOD, 'Q1')

Generation-Based Logic

Use ISGEN when a business rule applies to a particular hierarchy generation.

For example:

CASE
    WHEN ISGEN(GEOGRAPHY, 2) THEN
        {ROW} * 1.05
    ELSE
        {ROW}
END

Generation is counted from the top of the hierarchy, with the root at generation 1.

Level-Based Logic

Use ISLEV when a calculation depends on a specific hierarchy level.

For example:

CASE
    WHEN ISLEV(PRODUCT, 0) THEN
        {ROW} * 1.05
    ELSE
        AGG_SELF()
END

Level 0 represents leaf members.

When the intended condition is simply “is this a leaf member?”, ISLEAF(PRODUCT) expresses that intent more directly.

Prior-Period Variance

Use LAG to reference the previous member in a recognized time dimension.

For example:

{ROW} - [PERIOD].[LAG(PERIOD, 1)]

Conceptually:

Current Period - Previous Period

For an ordered sequence:

Jan
Feb
Mar
Apr

the formula behaves relatively:

Feb → compares with Jan
Mar → compares with Feb
Apr → compares with Mar

See Time Navigation for time-dimension recognition and boundary behavior.

Period-over-Period Growth %

A period-over-period growth percentage can use the previous period as both the comparison value and denominator:

(
    {ROW} - [PERIOD].[LAG(PERIOD, 1)]
)
/
NULLIF([PERIOD].[LAG(PERIOD, 1)], 0)
* 100

Conceptually:

Current Period - Previous Period
──────────────────────────────── × 100
         Previous Period

At the first available period, a previous member may not exist, so boundary behavior should be considered when testing the formula.

Prior-Year Variance

When YEARS is recognized for time navigation:

{ROW} - [YEARS].[LAG(YEARS, 1)]

produces a relative prior-year comparison.

For example:

FY24 → compares with FY23
FY25 → compares with FY24
FY26 → compares with FY25

The formula does not need to be changed when the current year advances.

Year-over-Year Growth %

The same approach can calculate year-over-year growth:

(
    {ROW} - [YEARS].[LAG(YEARS, 1)]
)
/
NULLIF([YEARS].[LAG(YEARS, 1)], 0)
* 100

Time Navigation at a Specific Coordinate

Relative time navigation can be combined with another coordinate shift.

For example:

[PERIOD].[LAG(PERIOD, 1)]
  ->[SCENARIO].[Actual]

references:

PERIOD   = Previous Period
SCENARIO = Actual

while retaining the current coordinates for the other dimensions.

This pattern is useful when both a relative time reference and another explicit dimensional coordinate are required.

Referencing Another Calculated Member

A calculated member can build on another calculated member.

For example:

Gross Profit
=
[ACCOUNT].[Revenue] - [ACCOUNT].[Cost of Sales]

followed by:

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

Casabase Cube detects the dependency and resolves the formulas in the required order.

See Solve Order & Dependencies for dependency behavior and circular-reference handling.

Null-Safe Division

Any formula that performs division should consider what happens when the denominator is zero.

Instead of:

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

use:

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

or another supported expression that produces the intended behavior for a zero denominator.

This pattern is commonly used in:

• Ratios
• Percentages
• Growth rates
• Margins
• Per-unit calculations

Defaulting NULL Values

Use COALESCE when the business rule requires a default for a NULL value.

For example:

COALESCE([ACCOUNT].[Adjustment], 0)

can be incorporated into a larger calculation:

[ACCOUNT].[Revenue]
+
COALESCE([ACCOUNT].[Adjustment], 0)

This explicitly treats a missing Adjustment as zero.

NULL and zero can represent different business conditions, so apply a default only when that behavior is appropriate.

Absolute Variance

Use ABS when the magnitude of a difference matters but its direction does not.

For example:

ABS(
    [SCENARIO].[Actual]
    -
    [SCENARIO].[Budget]
)

This returns the absolute difference between Actual and Budget.

Minimum and Maximum Boundaries

GREATEST and LEAST can be used to enforce simple boundaries.

To prevent a calculated value from falling below zero:

GREATEST({ROW}, 0)

To cap a value at 100:

LEAST({ROW}, 100)

They can also compare member values:

GREATEST(
    [SCENARIO].[Actual],
    [SCENARIO].[Budget]
)

Rounding

Use ROUND when a result should be returned to a particular number of decimal places.

For example:

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

This returns the calculated percentage rounded to two decimal places.

Dynamic Member References

A dynamic member expression can construct a member name from the current multidimensional context.

For example:

ISDESC(ICP, {'ICP_' || ENTITY})

If the current Entity is:

100

the expression:

{'ICP_' || ENTITY}

can resolve to:

ICP_100

The hierarchy relationship test then uses that dynamically resolved member.

This pattern is useful when related dimensions follow a consistent member-naming convention.

See Formula Syntax for dynamic member syntax.

Combining Multiple Patterns

Real-world formulas can combine several supported capabilities.

For example:

CASE
    WHEN ISLEAF(ACCOUNT) THEN
        [ACCOUNT].[Gross Profit]->[SCENARIO].[Actual]
        /
        NULLIF(
            [ACCOUNT].[Revenue]->[SCENARIO].[Actual],
            0
        )
        * 100
    ELSE
        AGG_SELF()
END

This example combines:

• A hierarchy-aware condition
• A calculated-member reference
• Cross-dimensional coordinate shifts
• Null-safe division
• Natural upper-level aggregation

The formula is evaluated according to the current multidimensional context and the dependencies defined by its referenced calculated members.

Dynamic Time Series for Accumulations

Do not use member formulas to manually reproduce cumulative time calculations such as:

• Year-to-date
• Quarter-to-date
• Month-to-date

These calculations require accumulation across a time range and are handled through Dynamic Time Series.

Use LAG and LEAD when a formula needs a value at another relative point in time.

Use Dynamic Time Series when the requirement is an accumulation through time.

See Time Navigation for the distinction between relative time navigation and time accumulation.

Choosing the Right Pattern

Requirement	Typical Pattern
Difference between two members	Member subtraction
Percentage difference	Variance divided by comparison member
Margin or ratio	Numerator divided by denominator
Cross-dimensional comparison	-> or tuple reference
Conditional calculation	CASE or IIF
Previous period	LAG(PERIOD, 1)
Previous year	LAG(YEARS, 1)
Next period or year	LEAD
Logic based on leaf status	ISLEAF
Logic based on hierarchy branch	ISDESC or another relationship predicate
Logic based on hierarchy depth	ISGEN or ISLEV
Logic based on UDA metadata	HASUDA
Logic based on attribute metadata	ISATTR
Natural upper-level aggregation	AGG_SELF()
Reuse another calculated member	Calculated-member reference
Protect a denominator	NULLIF
Supply a value for NULL	COALESCE
Absolute difference	ABS
Minimum or maximum boundary	GREATEST / LEAST
Rounded result	ROUND
Dynamic related member	[DIM].[{expression}]
YTD / QTD / MTD	Dynamic Time Series

These patterns can be combined when a business calculation requires multiple behaviors.

Related Topics

• Formula Syntax
• Hierarchy-Aware Functions
• Time Navigation
• Solve Order & Dependencies
• Formula Validation
• Best Practices

For Oracle Essbase and Cloud EPM migrations, also review Compatibility Notes for source-formula differences that can require redesign.