@ANCEST

Returns the ancestor at the specified generation or level of the current
member being calculated in the specified dimension. If you specify the optional mbrName, that ancestor is combined with the specified
member.

This member set function can be used as a parameter of another function,
where that parameter is a member or list of members.

Syntax

@ANCEST (dimName, genLevNum [, mbrName])
Parameter Description

dimName

Single dimension name specification.

genLevNum

An integer value that defines the generation or level number from which
the ancestor value is returned. A positive integer defines a generation number.
A value of 0 or a negative integer defines a level number.

mbrName

Optional. Any valid single member name or member combination, or a function
that returns a single member or member combination, that is crossed with the
ancestor returned.

Notes

  • You cannot use the @ANCEST function in a FIX statement.

  • You can use the @ANCEST function on both the left-hand and
    right-hand sides of a formula. If you use this function on the left-hand side
    of a formula in a calculation script, associate it with a member. For example:

    Sales(@ANCEST(Product) = 5;);
  • In some cases, the @ANCEST function is equivalent to the @ANCESTVAL
    function, except in terms of calculation performance. For example, the following
    two formulas are equivalent:

    Sales = @ANCEST(Product,2);
    Sales = @ANCESTVAL(Product,2);

    In this case, using the latter formula results in better calculation
    performance. In general, use @ANCEST as a member rather than as an implied
    value of a cell. For example:

    Sales = @AVG(SKIPMISSING, @ISIBLINGS(@ANCEST(Product,2)));
  • The time required for retrieval and calculation may be significantly
    longer if this function is in a formula attached to a member tagged as Dynamic Calc or Dynamic Calc and Store.

Example

In the Sample Basic database:

Function                        Generated List
 
@ANCEST(Product,2,Sales)        Colas->Sales, if the current member of
                                Product being calculated is Diet Cola.
 
@ANCEST(Measures,3,East)        Total Expenses->East, if the current member of
                                Measures being calculated is Payroll.