Essbase @MDANCESTVAL

Returns ancestor-level data from multiple dimensions based on the current
member being calculated.

Syntax

@MDANCESTVAL (dimCount, dimName1, genLevNum1. . . dimNameX, genLevNumX [,mbrName])
Parameter Description
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.
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.

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

  • @ANCESTVAL
  • @SANCESTVAL
  • @MDPARENTVAL