Member Set Functions

Member set calculation functions in Essbase return a list of one or more members. The list returned depends on the input member you supply. For example, @CHILDREN(Year) may return months. To input more than one member to a member set function, you can use range operators and cross dimensional expressions.

When a member set function is called as part of a formula, the list of members is generated before the calculation begins. The list never varies, because it is based on the specified member and is independent of the current member.

If a member set function (for example, @CHILDREN or @SIBLINGS) is used to specify the list of members to calculate in a calculation script, Essbase bypasses the calculation of any Dynamic Calc or Dynamic Calc and Store members in the resulting list.

Only the @ATTRIBUTE and @WITHATTR functions can use attribute members or members of the Attribute Calculations dimension as parameters in member set functions.

You can use cross-dimension expressions such as ("1998":"2001" -> @Levmbrs (Year, 0)). The cross-dimensional operator is associative (x -> y) -> z=x -> (y -> z), but not commutative because x -> y = y -> x is a set, but the order of elements is different.

You can use range operators with member set functions. The : operator returns level ranges, and the :: operator returns generation ranges.

Table 3-8 Member Set Functions

Function	Return Value
@ALLANCESTORS	All ancestors of member, including ancestors of shared member.
@ANCEST	Ancestor at distance from the current member or an explicitly specified member.
@ANCESTORS	All ancestors of member, or those ancestors up to a specified distance.
@ATTRIBUTE	All base members associated with attribute member name.
@BETWEEN	All members whose name string value fall between, and are inclusive of, two specified string tokens.
@CHILDREN	Children of member.
@CURRMBR	Member currently being calculated in the specified dimension.
@DESCENDANTS	All descendants of member, or those descendants down to a specified distance.
@EQUAL	Member names that match the specified token name.
@EXPAND	Expands a member search by calling a member set function for each member in a member list.
@GENMBRS	Members of dimension that are at generation.
@IALLANCESTORS	Member and ancestors of member, including ancestors of shared member.
@IANCESTORS	Member, and either all member ancestors or those ancestors up to a specified distance.
@ICHILDREN	Member and its children.
@IDESCENDANTS	Member, and either all member descendants or those descendants down to a specified distance.
@ILANCESTORS	Members of the specified list of members, and either all ancestors of the specified list of members or those ancestors up to a specified distance.
@ILDESCENDANTS	Members of the specified list of members, and either all descendants of the specified list of members or those descendants down to a specified distance.
@ILSIBLINGS	Member and its left siblings.
@INTERSECT	Members that are at the intersection of two specified lists of members.
@IRSIBLINGS	Member and its right siblings.
@IRDESCENDANTS	Member and all its descendants, or those descendants down to a specified distance, including descendants of shared member.
@ISIBLINGS	Member and its siblings.
@LANCESTORS	All ancestors of the specified list of members, or those ancestors up to a specified distance.
@LDESCENDANTS	All descendants of the specified list of members, or those descendants down to a specified distance.
@LEVMBRS	Members of dimension that are at level.
@LIST	A single list compiled from arguments, and can be used for functions requiring an expression list, a member list, or a range list.
@LSIBLINGS	Left siblings of member.
@MATCH	Members that match a pattern search performed over a generation, a level, or a member and its descendants.
@MBRCOMPARE	Member names that match the comparison criteria.
@MBRPARENT	Parent of the specified member.
@MEMBER	Member with name string.
@MEMBERAT	Member at the specified location in a list.
@MERGE	Merged list from two lists.
@NEXTSIBLING	Next, or right-most, sibling of member.
@NOTEQUAL	Member names that do not match the specified token name.
@PARENT	Parent of the current member being calculated in dimension, optionally crossed with another member.
@PREVSIBLING	Previous, or left-most, sibling of member.
@RANGE	Member list that crosses a member from one dimension with a range from another dimension.
@RDESCENDANTS	All descendants of member, or those down to a specified distance, including descendants of shared member.
@RELATIVE	All members that are at distance from member.
@REMOVE	List1, with anything that is also in list2 removed.
@RSIBLINGS	Right siblings of member.
@SHIFTSIBLING	Sibling at specified distance from member.
@SIBLINGS	Siblings of member.
@UDA	Members of dimension that have UDA.
@WITHATTR	Base members from dimension that are associated with an attribute meeting a condition.
@XRANGE	Range of members between (and inclusive of) two members at the same level.

Generation and Level Range Operators for Member Set Functions

The operators : and :: can be used with member set functions, which return a list of members. The : operator returns level-based ranges and the :: operator returns generation-based ranges. For example, Jan:Dec and Jan::Dec both return all members between and inclusive of Jan and Dec.

The difference is that Jan:Dec returns all members at the same level and Jan::Dec returns all members at the same generation.

For example, if we have the outline:

  Q1 - Jan
       Feb
       Mar
  Q2 - Apr
       May
       Jun
  Q3
  Q4 - Oct 
       Nov
       Dec

The function @MOVAVG(Sales, 3, Jan:Dec) computes @MOVAVG(Sales, 3, Jan, Feb, Mar, Apr, May, Jun, Q3, Oct, Nov, Dec).

The function @MOVAVG(Sales, 3, Jan::Dec) computes @MOVAVG(Sales, 3, Jan, Feb, Mar, Apr, May, Jun, Oct, Nov, Dec).