Returns ancestor-level data from multiple dimensions based on the current
member being calculated.
@MDANCESTVAL (dimCount, dimName1, genLevNum1. . . dimNameX, genLevNumX [,mbrName])
|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
|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.
|mbrName||Optional. Any valid single member name or member combination, or a function
that returns a single member or member combination, from which the ancestor
values are to be returned.
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