@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])

Parameters

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 a function that returns a single member. This member 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.