@MDANCESTVAL

Function

The @MDANCESTVAL() function returns ancestor-level data from multiple dimensions based on the current member being calculated.

Syntax

@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 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     N/A
Boston      100-10     100     20
            100-20     400     80
              100      500     N/A
East        100-10     400     N/A
            100-20     600     N/A
              100     1000     N/A

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

See Also

@ANCESTVAL
@SANCESTVAL
@MDPARENTVAL

Copyright (c)1991-2000 Hyperion Solutions Corporation. All rights reserved.