Hierarchy-Aware Functions

Hierarchy-aware functions allow member formulas to respond dynamically to the structure and metadata of a cube dimension.

Instead of hard-coding logic for individual members, a formula can determine characteristics of the member currently being evaluated, such as:

• Whether it is a leaf member
• Its generation or level
• Its parent
• Whether it is related to another member as a child, descendant, ancestor, parent, or sibling
• Whether it has a particular User-Defined Attribute (UDA)
• Whether it is associated with an attribute member

This allows calculation logic to adapt as the cube model changes.

Hierarchy Functions vs. POV Set Operators

Hierarchy-aware formula functions are different from the member-set operators used in a query POV.

For example, a POV can use:

{"descendants": "Total Product"}

to select descendants of Total Product for a query.

A formula can use:

ISDESC(PRODUCT, 'Total Product')

to test whether the PRODUCT member currently being evaluated is a descendant of Total Product.

The distinction is:

Capability	Purpose
POV set operators	Determine which members are included in a query.
Hierarchy-aware formula functions	Evaluate hierarchy relationships while calculating a formula.

Hierarchy-aware functions do not change the members selected by the POV. They provide information that a formula can use while determining its result.

Member Position Functions

Casabase Cube provides functions for inspecting the current member’s position within a hierarchy.

ISLEAF

ISLEAF(DIMENSION)

Returns TRUE when the current member in the specified dimension is a leaf member.

A leaf member has no children.

For example:

ISLEAF(ACCOUNT)

can be used in conditional logic:

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

GENERATION

GENERATION(DIMENSION)

Returns the generation number of the current member.

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

Conceptually:

Root                Generation 1
  │
  ├── Region        Generation 2
  │     │
  │     └── State   Generation 3
  │           │
  │           └── City
  │                 Generation 4

For example:

GENERATION(GEOGRAPHY)

returns the generation of the current GEOGRAPHY member.

LEVEL

LEVEL(DIMENSION)

Returns the level number of the current member.

Level is counted upward from the bottom of the hierarchy, with leaf members at level 0.

Conceptually:

Total Geography     Level 3
  │
  ├── Region        Level 2
  │     │
  │     └── State   Level 1
  │           │
  │           └── City
  │                 Level 0

For example:

LEVEL(GEOGRAPHY)

returns the level of the current GEOGRAPHY member.

PARENT

PARENT(DIMENSION)

Returns the name of the parent of the current member.

For example:

PARENT(PERIOD)

can be used in conditional logic:

CASE
    WHEN PARENT(PERIOD) = 'Q1' THEN 1
    ELSE 0
END

Testing Generations and Levels

When a formula requires a Boolean test rather than the numeric generation or level, use ISGEN or ISLEV.

ISGEN

ISGEN(DIMENSION, generation)

Returns TRUE when the current member is at the specified generation.

For example:

ISGEN(ACCOUNT, 2)

can be used as:

CASE
    WHEN ISGEN(ACCOUNT, 1) THEN
        AGG_SELF()
    WHEN ISGEN(ACCOUNT, 2) THEN
        {ROW} * 1.05
    ELSE
        {ROW}
END

ISLEV

ISLEV(DIMENSION, level)

Returns TRUE when the current member is at the specified level.

For example:

ISLEV(PRODUCT, 0)

tests whether the current PRODUCT member is at level 0.

Because leaf members are level 0, this can often express logic similar to:

ISLEAF(PRODUCT)

Use the function that most clearly represents the intent of the calculation.

Hierarchy Relationship Predicates

Relationship predicates determine whether the current member has a particular structural relationship to another member.

Casabase Cube supports child, descendant, ancestor, parent, and sibling relationships.

Each relationship is available in both:

• A strict form, which excludes the referenced member itself
• An inclusive form, which also matches the referenced member

Child Relationships

ISCHILD

ISCHILD(DIMENSION, 'member')

Returns TRUE when the current member is a direct child of the specified member.

For example:

ISCHILD(ENTITY, 'Corporate')

tests whether the current ENTITY member is a direct child of Corporate.

The referenced Corporate member itself does not match.

ISICHILD

ISICHILD(DIMENSION, 'member')

Performs the same direct-child test but also returns TRUE when the current member is the referenced member itself.

Descendant Relationships

ISDESC

ISDESC(DIMENSION, 'member')

Returns TRUE when the current member is a descendant of the specified member at any depth.

For example:

ISDESC(ACCOUNT, 'Operating Expenses')

matches members beneath Operating Expenses, but does not match Operating Expenses itself.

ISIDESC

ISIDESC(DIMENSION, 'member')

Returns TRUE when the current member is either:

• A descendant of the specified member, or
• The specified member itself

For example:

ISIDESC(ACCOUNT, 'Operating Expenses')

matches both Operating Expenses and the members beneath it.

Ancestor Relationships

ISANCEST

ISANCEST(DIMENSION, 'member')

Returns TRUE when the current member is an ancestor of the specified member.

For example:

ISANCEST(ENTITY, 'Entity 100')

tests whether the current ENTITY member is above Entity 100 in the hierarchy.

The referenced Entity 100 member itself does not match.

ISIANCEST

ISIANCEST(DIMENSION, 'member')

Returns TRUE when the current member is either:

• An ancestor of the specified member, or
• The specified member itself

Parent Relationships

ISPARENT

ISPARENT(DIMENSION, 'member')

Returns TRUE when the current member is the direct parent of the specified member.

For example:

ISPARENT(PERIOD, 'Jan')

tests whether the current PERIOD member is the parent of Jan.

This is different from:

PARENT(PERIOD)

PARENT() returns the parent of the current member, while ISPARENT() performs a Boolean relationship test against another member.

ISIPARENT

ISIPARENT(DIMENSION, 'member')

Returns TRUE when the current member is either:

• The direct parent of the specified member, or
• The specified member itself

Sibling Relationships

ISSIBLING

ISSIBLING(DIMENSION, 'member')

Returns TRUE when the current member shares the same parent as the specified member.

The referenced member itself is excluded.

For example:

ISSIBLING(PERIOD, 'Jan')

can match other members having the same parent as Jan, but does not match Jan itself.

ISISIBLING

ISISIBLING(DIMENSION, 'member')

Performs the same sibling test but also includes the referenced member itself.

Strict and Inclusive Relationships

The complete set of relationship predicates is:

Relationship	Strict	Inclusive
Child	ISCHILD(dim, 'member')	ISICHILD(dim, 'member')
Descendant	ISDESC(dim, 'member')	ISIDESC(dim, 'member')
Ancestor	ISANCEST(dim, 'member')	ISIANCEST(dim, 'member')
Parent	ISPARENT(dim, 'member')	ISIPARENT(dim, 'member')
Sibling	ISSIBLING(dim, 'member')	ISISIBLING(dim, 'member')

For example:

ISDESC(ACCOUNT, 'Operating Expenses')

is strict, while:

ISIDESC(ACCOUNT, 'Operating Expenses')

is inclusive.

Conceptually:

                    ISDESC     ISIDESC

Operating Expenses   FALSE       TRUE
│
├── Salaries          TRUE       TRUE
├── Rent              TRUE       TRUE
└── Utilities         TRUE       TRUE

Use the inclusive form when the business rule should apply to both the referenced member and members having the specified relationship to it.

Dynamic Relationship Tests

The referenced member does not have to be a fixed literal.

Hierarchy predicates can use dynamically constructed member references.

For example:

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

The expression:

{'ICP_' || ENTITY}

constructs a member name using the current ENTITY member.

If the current ENTITY is:

100

the expression can resolve to:

ICP_100

The hierarchy predicate then tests the current ICP member relative to that dynamically resolved member.

This pattern is useful when dimensions contain related members that follow consistent naming conventions.

See Formula Syntax for dynamic member references.

User-Defined Attributes

User-Defined Attributes, or UDAs, provide metadata associated with hierarchy members.

Use:

HASUDA(DIMENSION, 'UDA')

to determine whether the current member has a particular UDA.

For example:

HASUDA(ACCOUNT, 'EXPENSE')

can be used in conditional logic:

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

The calculation follows the UDA metadata assigned to the Account members.

If additional members receive the EXPENSE UDA, the formula recognizes them without requiring the formula to be rewritten.

A member can have multiple UDAs, and UDA matching is case-insensitive.

Attribute-Based Logic

ISATTR tests whether the current member of a base dimension carries a particular attribute association.

The syntax is:

ISATTR(attribute_dimension, 'attribute_member')

The first argument is the attribute dimension, not the base dimension.

For example, assume PRODUCT has an attribute dimension named PRIMARY_UOM.

A formula can test whether the current Product has the Case attribute:

ISATTR(PRIMARY_UOM, 'Case')

This can be used in conditional logic:

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

ISATTR evaluates the attribute association of the current base member.

The attribute dimension does not need to be included in the query POV for the test to work.

Upper-level attribute members can also be tested. When an upper-level attribute member is referenced, base members associated with attribute members beneath that upper-level member can match the test.

Caution

Attribute associations exist at the leaf level of the associated base dimension. ISATTR is meaningful only when that base dimension is evaluated at leaf grain. Casabase Cube refuses unsupported aggregate-grain usage rather than returning a misleading result.

Hierarchy-Aware Logic and AGG_SELF()

Hierarchy-aware conditions are often combined with AGG_SELF() when the formula should behave differently at detail and aggregate levels.

For example:

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

This establishes two behaviors:

Leaf Member
     │
     ▼
Evaluate Formula Logic


Upper-Level Member
     │
     ▼
Natural Hierarchy Roll-Up

AGG_SELF() returns the natural operator-weighted roll-up for the formula member without evaluating that member’s formula.

See Formula Syntax for additional information about AGG_SELF().

Function Summary

Function	Purpose
ISLEAF(dim)	Tests whether the current member is a leaf.
GENERATION(dim)	Returns the current member’s generation.
LEVEL(dim)	Returns the current member’s level.
PARENT(dim)	Returns the current member’s parent.
ISGEN(dim, n)	Tests whether the current member is at generation n.
ISLEV(dim, n)	Tests whether the current member is at level n.
ISCHILD(dim, 'member')	Tests a strict direct-child relationship.
ISICHILD(dim, 'member')	Tests a direct-child relationship including the referenced member.
ISDESC(dim, 'member')	Tests a strict descendant relationship.
ISIDESC(dim, 'member')	Tests a descendant relationship including the referenced member.
ISANCEST(dim, 'member')	Tests a strict ancestor relationship.
ISIANCEST(dim, 'member')	Tests an ancestor relationship including the referenced member.
ISPARENT(dim, 'member')	Tests a strict parent relationship.
ISIPARENT(dim, 'member')	Tests a parent relationship including the referenced member.
ISSIBLING(dim, 'member')	Tests a sibling relationship excluding the referenced member itself.
ISISIBLING(dim, 'member')	Tests a sibling relationship including the referenced member itself.
HASUDA(dim, 'uda')	Tests whether the current member has a UDA.
ISATTR(attribute_dim, 'attribute_member')	Tests an attribute association on the current base member.

Key Takeaways

• Hierarchy-aware functions evaluate cube structure and metadata while a formula is being calculated.
• They are different from POV set operators, which determine which members are included in a query.
• ISLEAF, GENERATION, LEVEL, and PARENT inspect the current member’s hierarchy position.
• ISGEN and ISLEV provide Boolean generation and level tests.
• Child, descendant, ancestor, parent, and sibling relationships have strict and inclusive predicates.
• The IS... relationship predicates exclude the referenced member itself.
• The corresponding ISI... predicates include the referenced member.
• Hierarchy predicates can use dynamically constructed member references.
• HASUDA allows formula behavior to be driven by User-Defined Attributes.
• ISATTR evaluates attribute associations using the attribute dimension and is meaningful at leaf grain on the associated base dimension.
• AGG_SELF() can be combined with hierarchy-aware logic when upper-level members should retain their natural hierarchy aggregation.

Related Topics

• Formula Syntax
• Common Formula Patterns
• Formula Validation
• Best Practices