Member-Related Functions

Use Essbase member functions in formulas to specify member lists and ranges, generate member lists, manipulate member names, and work with member combinations across dimensions.

This section discusses creating formulas that refer to members.

• Specify Member Lists and Ranges

• Generate Member Lists

• Manipulate Member Names

• Work with Member Combinations Across Dimensions

Specify Member Lists and Ranges

When using some Essbase calc functions, you may need to specify multiple members (a mbrlist), or you may need to specify a range of members.

For example, the @ISMBR function tests to see if a member that is currently being calculated matches any of a list or range of specified members.

The following table lists the syntax for specifying members:

Table 18-8 Syntax for Specifying Member Lists and Ranges

Member List or Range	Syntax
One member	The member name. For example: Mar2001
A list of members	A comma-delimited (,) list of member names. For example: Mar2001, Apr2001, May2001
A range of all members at the same level, between and including the two defining members	The two defining member names separated by a colon (:). For example: Jan2000:Dec2000
A range of all members in the same generation, between and including the two defining members	The two defining member names separated by two colons (::). For example: Q1_2000::Q4_2000
A function-generated list of members or a range of members	For a list of member list contents and corresponding functions, see Generate Member Lists.
A combination of ranges and list	Separate each range, list, and function with a comma (,). For example: Q1_97::Q4_98, FY99, FY2000 or @SIBLINGS(Dept01), Dept65:Dept73, Total_Dept

If you do not specify a list of members or a range of members in a function that requires either, Essbase uses the level 0 members of the dimension tagged as time. If no dimension is tagged as time, Essbase displays an error message.

Generate Member Lists

Essbase member set functions allow you to generate member lists that are based on a specified member or member list.

Table 18-9 List of Member Set Functions

Function	Contents of Member List
@ALLANCESTORS	All ancestors of the specified member, including ancestors of the specified member as a shared member. This function does not include the specified member.
@IALLANCESTORS	All ancestors of the specified member, including ancestors of the specified member as a shared member. This function includes the specified member.
@ANCEST	The ancestor of the specified member at the specified generation or level
@ANCESTORS	All ancestors of the specified member (optionally, up to the specified generation or level), but not the specified member
@IANCESTORS	All ancestors of the specified member (optionally, up to the specified generation or level), including the specified member
@LANCESTORS	All ancestors of the specified list of members (optionally, up to the specified generation or level), but not including the specified members
@ILANCESTORS	All ancestors of the specified list of members (optionally, up to the specified generation or level), including the specified members
@ATTRIBUTE	All base-dimension members that are associated with the specified attribute-dimension member
@WITHATTR	All base members that are associated with attributes that satisfy the specified conditions
@BETWEEN	All members whose name string value fall between, and are inclusive of, two specified string tokens
@CHILDREN	All children of the specified member, but not including the specified member
@ICHILDREN	All children of the specified member, including the specified member
@CURRMBR	The current member being calculated for the specified dimension
@DESCENDANTS	All descendants of the specified member (optionally, up to the specified generation or level), but not the specified member nor descendants of shared members
@IDESCENDANTS	All descendants of the specified member (optionally, up to the specified generation or level), including the specified member, but not descendants of shared members
@LDESCENDANTS	All descendants of the specified list of members (optionally, down to the specified generation or level), but not including the specified members
@ILDESCENDANTS	All descendants of the specified list of members (optionally, down to the specified generation or level), including the specified members
@RDESCENDANTS	All descendants of the specified member (optionally, up to the specified generation or level), including descendants of shared members, but not the specified member
@IRDESCENDANTS	All descendants of the specified member (optionally, up to the specified generation or level), including the specified member and descendants of shared members
@EQUAL	Member names that match the specified token name
@NOTEQUAL	Member names that do not match the specified token name
@EXPAND	Expands a member search by calling a member set function for each member in a member list
@GENMBRS	All members of the specified generation in the specified dimension
@LEVMBRS	All members of the specified level in the specified dimension
@LIKE	Member names that match the specified pattern.
@LIST	Separate lists of members to be processed by functions that require multiple list arguments
@MATCH	All members that match the specified wildcard selection
@MBRCOMPARE	Member names that match the comparison criteria
@MBRCOMPARE	Parent of the specified member
@MEMBER	The member with the name that is provided as a character string
@MERGE	A merged list of two member lists to be processed by another function
@PARENT	The parent of the current member being calculated in the specified dimension
@RANGE	A member list that crosses the specified member from one dimension with the specified member range from another dimension
@REMOVE	A list of members from which some members have been removed
@RELATIVE	All members of the specified generation or level that are above or below the specified member
@SHARE	A member list that identifies all shared members among the specified members
@SIBLINGS	All siblings of the specified member, but not the specified member
@ISIBLINGS	All siblings of the specified member, including the specified member
@LSIBLINGS	All siblings that precede the specified member in the database outline, but not the specified member
@RSIBLINGS	All siblings that follow the specified member in the database outline, but not the specified member
@ILSIBLINGS	All siblings that precede the specified member in the database outline, including the specified member
@IRSIBLINGS	All siblings that follow the specified member in the database outline, including the specified member
@SHIFTSIBLING	The sibling at the specified distance from the member
@NEXTSIBLING	The next, or right-most, sibling of the member
@PREVSIBLING	The previous, or left-most, sibling of the member
@UDA	All members that have a common UDA defined on Essbase Server
@XRANGE	A member list that identifies the range of members between (and inclusive of) two specified single or cross-dimensional members at the same level

Manipulate Member Names

You can work with Essbase member names as character strings in your block storage formulas and calc scripts.

The following table lists character string manipulation functions:

Table 18-10 List of Character String Manipulation Functions

Function	Character String Manipulation
@CONCATENATE	Create a character string that is the result of appending a member name or specified character string to another member name or character string
@NAME	Return a member name as a string
@SUBSTRING	Return a substring of characters from another character string or from a member name

Work with Member Combinations Across Dimensions

Use the cross-dimensional operator to point to data values of specific member combinations. Create the cross-dimensional operator using a hyphen (-) and a greater-than symbol (>). Do not include a space between the cross-dimensional operator and members.

Below is a simplified illustration of a multidimensional cube, in which Jan is the first column on the X axis, Sales is the fourth and top-most row on the Y axis, and Actual is the first row on the Z axis. In this example, Sales -> Jan -> Actual is the intersection of a single data value.

Figure 18-2 Specifying a Single Data Value

The following example, which allocates miscellaneous expenses to each product in each market, illustrates how to use the cross-dimensional operator. The value of Misc_Expenses for all products in all markets is known. The formula allocates a percentage of the total Misc_Expenses value to each Product -> Market combination. The allocation is based on the value of Sales for each product in each market.

Misc_Expenses = Misc_Expenses -> Market -> Product * 

   (Sales / ( Sales -> Market -> Product));

Essbase cycles through the database, performing these calculations:

1. Divides the Sales value for the current member combination by the total Sales value for all markets and all products (Sales -> Market -> Product).

2. Multiplies the value calculated in step 1 by the Misc_Expenses value for all markets and all products (Misc_Expenses -> Market -> Product).

3. Allocates the result to Misc_Expenses for the current member combination.

Using the cross-dimensional operator can have significant performance implications. For optimization guidelines, see Cross-Dimensional Operators in Member Formulas.