Dimensional Functions

ancestor
Returns the ancestor of "member" at "level" or at "integer" number of levels above "member". Note: The result is not guaranteed to be consistent when there is more than one such ancestor.
ancestors
Returns all the ancestors of "member" at "level" or "index" distance above the member. (Most data sources support only one ancestor at a specified level. If the data source supports more than one ancestor, the result is a member set.)
bottomCount
Sorts a set according to the value of "numeric_expression" evaluated at each of the members of "set_expression" and returns the bottom "index_expression" members.
bottomPercent
Sorts the set specified in "set_expression" in ascending order and returns the bottommost elements from the sorted set whose cumulative percentage of the total is greater than or equal to "percentage".
bottomSum
Sorts the set specified in "set_expression" in ascending order and returns the bottommost elements from the sorted set whose cumulative total is greater than or equal to "value".
caption
Returns the caption values of "level", "member", or "set_expression". The caption is the string display name for an element and does not necessarily match the unique identifier used to generate the business key or member unique name (MUN) for the element. The caption is not necessarily unique; for example, the caption for a month may return the month name without further year details to make the value unique.
children
Returns the set of children of a specified member.
closingPeriod
Returns the last sibling member among the descendants of a member at "level". This function is typically used with a time dimension.
cousin
Returns the child member of "member2" with the same relative position as "member1" to its parent. This function appears in the Revenue by GO Subsidiary 2005 sample report in the GO Data Warehouse (analysis) package.
completeTuple
Identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. However, completeTuple () implicitly includes the default member from all dimensions not otherwise specified in the arguments, rather than the current member. CompleteTuple will use the default measure rather than the currentMeasure in the query if the measure is not defined in the completetuple function. This function appears in the Planned Headcount sample report in the GO Data Warehouse (analysis) package.
currentMember
Returns the current member of the hierarchy during an iteration. If "hierarchy" is not present in the context in which the expression is being evaluated, its default member is assumed. This function appears in the Rolling and Moving Averages interactive sample report.
defaultMember
Returns the default member of "hierarchy".
descendants
Returns the set of descendants of "member" or "set_expression" at "level" (qualified name) or "distance" (integer 0..n) from the root. Multiple options may be specified (separated by a space) to determine which members are returned. self: Only the members at the specified level are included in the final set (this is the default behaviour in the absence of any options). before: If there are any intermediate levels between the member's level and the one specified, members from those levels are included. If the level specified is the same as the member upon which the function is applied, the member is included in the final set. beforewithmember: If there are any intermediate levels between the member's level and the one specified, members from those levels are included. The member upon which the function is applied is also included in the final set. after: If other levels exist after the specified level, members from those levels are included in the final set. This function appears in the Sales Commissions for Central Europe sample report in the GO Data Warehouse (analysis) package.
except
Returns the members of "set_expression1" that are not also in "set_expression2". Duplicates are retained only if the optional keyword "all" is supplied as the third argument.
filter
Returns the set resulting from filtering a specified set based on the Boolean condition. Each member is included in the result if and only if the corresponding value of "Boolean_expression" is true.
firstChild
Returns the first child of "member".
firstSibling
Returns the first child of the parent of "member".
_format
Associates a format with the expression. The format_keyword can be PERCENTAGE_0, PERCENTAGE_1, or PERCENTAGE_2. PERCENTAGE_1 returns a percentage with one digit to the right of the decimal point, PERCENTAGE_2 returns a percentage with two digits to the right of the decimal point, and PERCENTAGE_3 returns a percentage value out of one with three digits to the right of the decimal point (for example, 0.965).
emptySet
Returns an empty member set for "hierarchy". This is most often used as a placeholder during development or with dynamic report design (either with the IBM® Cognos® Software Development Kit or via report design). By creating a data item that contains the emptyset function, it is possible to build complex expressions that can later be revised by redefining the emptyset data item.
generate
Evaluates "set_expression2" for each member of "set_expression1" and joins the resulting sets by union. The result retains duplicates only when the optional keyword "all" is supplied as the third argument.
head
Returns the first "index_expression" elements of "set_expression". The default for "index_expression" is 1.
hierarchize
Orders the members of "set_expression" in a hierarchy. Members in a level are sorted in their natural order. This is the default ordering of the members along a dimension when no other sort conditions are specified.
hierarchy
Returns the hierarchy that contains "level", "member", or "set_expression".
item
Returns a member from the "index" location within "set_expression". The index into the set is zero based.
intersect
Returns the intersection of "set_expression1" and "set_expression2". The result retains duplicates only when the optional keyword "all" is supplied as the third argument.
lag
Returns the sibling member that is "index_expression" number of positions prior to "member".
lastChild
Returns the last child of a specified member.
lastPeriods
Returns the set of members from the same level that ends with "member". The number of members returned is the absolute value of "integer_expression". If "integer_expression" is negative, members following and including the specified member are returned. Typically used with a time dimension. This function appears in the Rolling and Moving Averages interactive sample report.
lastSibling
Returns the last child of the parent of a specified member.
lead
Returns the sibling member that is "index_expression" number of positions after "member". If "index_expression" is negative, returns the sibling member that is "index_expression" number of positions before "member".
level
Returns the level of "member".
levels
Returns the level in "hierarchy" whose distance from the root is specified by "index".
linkMember
Returns the corresponding member in "level" or "hierarchy" (of the same dimension). For level-based hierarchies, a level must be specified as the second argument, and for parent-child hierarchies, a hierarchy must be specified. An exception is thrown when the second parameter does not resolve to a hierarchy of the member's dimension. Note that calculated members are not supported as the first argument.
members
Returns the set of members in "hierarchy" or "level". In the case of a hierarchy, the order of the members in the result is not guaranteed. If a predictable order is required, an explicit ordering function (such as hierarchize) must be used.
nextMember
Returns the next member in the "member" level.
openingPeriod
Returns the first sibling member among the descendants of a member at "level". This function is typically used with a time dimension.
order
Arranges the members of "set_expression" according to their "value_expression" and the third parameter. ASC and DESC arrange members in ascending or descending order, respectively, according to their position in the set hierarchy. Then the children of each member are arranged according to "value_expression". BASC and BDESC arrange members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default.
ordinal
Returns the zero-based ordinal value (distance from the root level) of "level".
parallelPeriod
Returns a member from a prior period in the same relative position as "member". This function is similar to the cousin function, but is more closely related to time series. It takes the ancestor of "member" at "level" (called "ancestor") and the sibling of "ancestor" that lags by "integer_expression" positions, and returns the parallel period of "member" among the descendants of that sibling. When unspecified, "integer_expression" defaults to 1 and "member" defaults to the current member.
parent
Returns the member that is the parent of "member" or "measure".
periodsToDate
Returns a set of sibling members from the same level as "member", as constrained by "level". It locates the ancestor of "member" at "level" and returns that ancestor's descendants at the same level as "member" (up to and including "member"). Typically used with a time dimension. This function appears in the Rolling and Moving Averages interactive sample report.
prevMember
Returns the member that immediately precedes "member" in the same level. This function appears in the Sales Growth Year Over Year sample report in the GO Data Warehouse (analysis) package.
member
Defines a member based on "value_expression" in "hierarchy". "String1" identifies the member created by this function. It must be unique in the query and different from any other member in the same hierarchy. "String2" is the caption of the member; if it is absent, the caption is empty. To ensure predictable results, it is recommended that you supply the "hierarchy". Note: All calculations used as grouping items whose sibling items are other calculations or member sets should be explicitly assigned to a hierarchy using this function. The results are not predictable otherwise. The only exception is where the calculation involves only members of the same hierarchy as the siblings. In this case, the calculation is assumed to belong to that hierarchy.
nestedSet
This function is intended for use only by Analysis Studio
set
Returns the list of members defined in the expression. The members must belong to the same hierarchy.
siblings
Returns the children of the parent of the specified member.
tail
Returns the last "index_expression" elements of "set expression". The default for "index_expression" is 1.
topCount
Sorts a set according to the values of "numeric_expression" evaluated at each of the members of "set_expression" and returns the top "index_expression" members.
topPercent
Sorts the set specified in "set_expression" in descending order and returns the topmost elements from the sorted set whose cumulative percentage of the total is greater than or equal to "percentage".
topSum
Sorts the set specified in "set_expression" in descending order and returns the topmost elements from the sorted set whose cumulative total is greater than or equal to "value".
tuple
Identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. This function implicitly includes the current member from all dimensions that are not otherwise specified in the arguments. The current member of any dimension not specified in the evaluating context is assumed to be the default member of that dimension. The value of this cell can be obtained with the "value" function.
union
Returns data for "set_expression1" and "set_expression2". The result retains duplicates only when the optional keyword "all" is supplied as the third argument.
roleValue
Returns the value of the attribute that is associated with the role whose name is specified by "string" within the specified context. "Member" or "set_expression" is optional only in a number of limited circumstances, where it can be derived from another context. Applications can be made portable across different data sources and models by accessing attributes by role rather than by query item ID. For dimensionally-modeled relational (DMR) data sources, assignment of roles is the modeler's responsibility. Intrinsic roles that are defined for members of all data source types include: '_businessKey', '_memberCaption', '_memberDescription', '_memberUniqueName'. Additional roles can be defined in Framework Manager for each level in a hierarchy. For example, a Product type level may have an attribute column called "Type Shipping Container", and the Product level may have a "Product Shipping Container" attribute. Each of these could be assigned a custom role in Framework Manager called "Container". The property could then be referenced independently of the actual column name by using the roleValue function.
rootMember
Returns the root member of a single-root hierarchy. This function appears in the Promotion Success sample report in the GO Data Warehouse (analysis) package.
rootMembers
Returns the root members of a hierarchy.
subset
Returns a subset of members in "set_expression" starting at "index_expression1" from the beginning. If the count "index_expression2" is specified, that many members are returned (if available). Otherwise, all remaining members are returned.
unique
Removes all duplicates from "set_expression". The remaining members retain their original order.
value
Returns the value of the cell identified by "tuple". Note that the default member of the Measures dimension is the Default Measure.

Parent topic: Using the expression editor