@SPARENTVAL

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

Syntax

@SPARENTVAL (RootMbr [, mbrName])

Parameters

RootMbr: Defines a member that is used to search for the nearest occurrence of a parent of a shared member.
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 or Dynamic Calc and Store.

Example

Marketing expenses are captured at the Product Category levels in a product planning application. The Product categories are defined as parents 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 / @SPARENTVAL(Product, Sales)) * @SPARENTVAL(Product, Marketing);

which produces the following result:

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

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