@LANCESTORS
The @LANCESTORS calculation function for Essbase returns a member list's ancestors, optionally up to a certain generation or level.
This function excludes from the output the members specified in the input list.
You can use this function as a parameter of another function, where the function requires a list of members.
Syntax
@LANCESTORS ((memberSetFunction) [,genLevNum])
Parameters
- memberSetFunction
-
A member set function that returns a list of members.
How the @LANCESTORS function is used determines which member set functions are allowed. Follow these guidelines:
-
If the @LANCESTORS function is used alone (not within a FIX statement), you must use the @LIST function and specify member names. For example:
@LIST(mbr1,mbr2,...)
-
If the @LANCESTORS function is used within a FIX statement, you can use member set functions such as @UDA and @ATTRIBUTE. For example:
@UDA(dimName,uda)
@ATTRIBUTE (attMbrName)
In this case, you can choose whether to use @LIST. For example, both of the following statements are valid, and the statements return the same results.
Example using only @ATTRIBUTE:
FIX(@LANCESTORS(@ATTRIBUTE(Caffeinated_True),@ATTRIBUTE(Ounces_12),"200-40")) ... ENDFIX;
Example using @LIST and @ATTRIBUTE:
FIX(@LANCESTORS(@LIST(@ATTRIBUTE(Caffeinated_True),@ATTRIBUTE(Ounces_12),"200-40"))) ... ENDFIX;
Caution:
All members of the specified member list must be from the same dimension.
-
- genLevNum
-
Optional. The integer value that defines the absolute generation or level number up to which to select members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.
Example
All examples are from the Sample.Basic database.
@LANCESTORS(@LIST("100–10","200–20"),2)
Returns 100 (the ancestor of 100-10); and 200 (the ancestor of 200-20). Excludes Product because it is at generation 1.
@LANCESTORS(@LIST("100","100–10"))
Returns Product (the ancestor of 100); and 100 (the ancestor of 100-10). The result does not contain duplicate members.
@LANCESTORS(@LIST("100","Product","200"))
Returns Product (the ancestor of 100 and 200). The result does not contain duplicate members.
FIX(@LANCESTORS(@UDA(Market,"New Market")),2)
...
ENDFIX;
Returns West, South, and Central (the ancestors, to generation 2, for the members in the Market dimension that are associated with the New Market attribute).
FIX(@LANCESTORS(@ATTRIBUTE(Caffeinated_True),@ATTRIBUTE(Ounces_12),"200–40"))
...
ENDFIX;
Returns 100, 200, 300, and Product (the ancestors of 100-10, 100-20, 200-10, 300-30—caffeinated, 12-ounce drinks, and 200-40).
See Also