The @ANCESTVAL calculation function for Essbase returns ancestor values.

This function returns the ancestor data values of the specified member combination.


@ANCESTVAL (dimName, genLevNum [, mbrName])



A single dimension name that defines the focus dimension of ancestor values.


Integer value that defines the 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.

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.


Optional. Any valid single member name or member combination (or a function that returns a single member or member combination).


In this example, SKU Share is derived by taking Sales in each SKU as a percentage of its product family. Families are at generation 2; therefore, each descendant of family is calculated as a percentage its respective ancestor. Consolidated results must be calculated for Sales by Product before the SKU Share calculation occurs.

"SKU Share" = Sales % @ANCESTVAL(Product,2,Sales);

This example produces the following report:

              Sales   SKU Share
              =====   =========  
SKU101          510        26.0
SKU102          520        26.5
  Group01      1030        52.5
SKU120          430        21.9
SKU123          500        25.5
  Group02       930        47.4
    Family1    1960      100.00