@SANCESTVAL

The @SANCESTVAL calculation function for Essbase returns ancestor-level data based on the shared ancestor value of the current member being calculated.

Syntax

@SANCESTVAL (rootMbr,genLevNum [, mbrName])

Parameters

rootMbr

Defines a member that is used to search for the nearest occurrence of an ancestor of a shared member.

genLevNum

Integer value that defines the absolute generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference.

To use this function or any other ancestor value function in a ragged hierarchy, use generation references instead of level references to avoid unexpected results.

mbrName

Optional. Any valid single member name, or a function that returns a single member.

Notes

You cannot use this function in a FIX statement.
The time required for retrieval and calculation may be significantly longer if this function is in a formula attached to a member tagged as Dynamic Calc.

Example

Marketing expenses are captured at the Product Category levels in a product planning application. The Product categories are defined as ancestors that contain shared members as children. The Marketing Expense data must be allocated down to each Product code based on Sales contribution.

The following Product hierarchy is defined:

Product
   100
        100-10
        100-20
   200
        200-10
        200-20
   Diet ~
        100-10 SHARED
        200-10 SHARED
   Caffeine Free ~
        100-20 SHARED
        200-20 SHARED

                 Sales   Marketing
                 =====   =========               
100-10            300       0
100-20            200       0
100               500       0
200-10            100       0
200-30            400       0
200               900       0
100-10            300       0
200-10            100       0
Diet              400      50
100-20            200       0
200-30            400       0 
Caffeine Free     600      40

The Marketing Expense value is allocated down to each Product code with the following formula:

Marketing = (Sales / @SANCESTVAL(Product, 2, Sales)) * @SANCESTVAL(Product, 2, Marketing);

which produces the following result:

                 Sales    Marketing
                 =====    =========
100-10           300       37.5
100-20           200       13.3
100              500       #MI
200-10           100       12.5
200-30           400       26.7
200              900       #MI
100-10           300       37.5
200-10           100       12.5
Diet             400       50
100-20           200       13.3
200-30           400       26.7
Caffeine Free    600       40

The Marketing expenses can then be reconsolidated across Products and Markets.