@SPARENTVAL

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

Syntax

@SPARENTVAL(RootMbr [, mbrName])

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 member combination, or a function that returns a single member or member combination, from which the parent values are returned.

Notes

You cannot use the @SPARENTVAL function in a FIX statement.

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.

See Also

@PARENTVAL
@MDPARENTVAL
@SANCESTVAL

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 member combination, or a function that returns a single member or member combination, from which the parent values are returned.