@MDANCESTVAL
The @MDANCESTVAL calculation function for Essbase returns ancestor-level data from multiple dimensions based on the current member being calculated.
Syntax
@MDANCESTVAL (dimCount, dimName1, genLevNum1 ... dimNameX, genLevNumX [,mbrName])
Parameters
- dimCount
-
Integer value that defines the number of dimensions from which ancestor values are being returned.
- dimName1, . . . dimNameX
-
Defines the dimension names from which the ancestor values are to be returned. You must specify a genLevNum for every dimName.
- genLevNum, . . . genLevNumX
-
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. You must specify a dimName for every genLevNum.
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. See Hierarchy Shapes.
- mbrName
-
Optional. Any valid single member name, or a function that returns a single member.
Example
Marketing expenses are captured at the Product Family and Region level in a product planning application. The Marketing Expense data must be allocated down to each Product code and State level based on Sales contribution. Data is captured as follows:
Sales Marketing
===== =========
New York 100-10 300 N/A
100-20 200 N/A
100 500 N/A
Boston 100-10 100 N/A
100-20 400 N/A
100 500 N/A
East 100-10 400 N/A
100-20 600 N/A
100 1000 200
The Marketing Expense value of 200 at East and Product code 100 is allocated down to each Product code and State with the following formula:
Marketing = (Sales / @MDANCESTVAL(2, Market, 2, Product, 2, Sales)) * @MDANCESTVAL(2, Market, 2, Product, 2, Marketing);
which produces the following result:
Sales Marketing
===== =========
New York 100-10 300 60
100-20 200 40
100 500 100
Boston 100-10 100 20
100-20 400 80
100 500 100
East 100-10 400 80
100-20 600 120
100 1000 200
The Marketing expenses can then be reconsolidated across Products and Markets.
See Also