Functions by Return Value

This section contains a listing of query functions by return value. The following return values are possible:

Functions that Return a Member

The following functions return a member or a member value expression.

Ancestor Returns a member that is an ancestor of the specified member, at a specified generation or level.
ClosingPeriod Returns the last descendant of a layer, or the last child of the Time dimension.
Cousin Returns a child member at a matching outline level and location as a member from another parent.
CurrentMember Returns the current member in the input dimension. Current is in the context of query execution mechanics. Use in combination with iterative functions such as Filter.
DefaultMember Returns the default member in the input dimension.
FirstChild Returns the first child of the input member.
FirstSibling Returns the first child of the input member's parent.
Lag Using the default order of members in a database outline, returns a member that is n steps behind the input member.
LastChild Returns the last child of the input member.
LastSibling Returns the last child of the input member's parent.
Lead Using the default order of members in a database outline, returns a member that is n steps past the input member.
NextMember Returns the member (in the same layer) that is one step past the input member.
OpeningPeriod Returns the first descendant of a layer, or the first child of the Time dimension.
ParallelPeriod Returns a member from a prior time period as the specified or default time member.
Parent Returns a member's parent.
PrevMember Returns the member (in the same layer) that is one step prior to the input member.

Functions that Return a Set

The following categories of functions return a set or a set value expression.

Pure Set Functions

Functions in this category derive their results without getting any further information from the cube.

CrossJoin Returns a cross-section of two sets from different dimensions.
Distinct Deletes duplicate tuples from a set.
Except Returns a subset containing the differences between two sets.
Generate For each tuple in set1, return set2.
Head Returns the first n members or tuples present in a set.
Intersect Returns the intersection of two input sets.
Subset Returns a subset from a set, in which the subset is a numerically specified range of tuples.
Tail Returns the last n members or tuples present in a set.
Union Returns the union of two input sets.

Metadata-based Set Functions

Functions in this category derive their results using metadata information from the cube.

Ancestors Returns a set of ancestors up to a specified layer or distance.
Attribute Returns all base members that are associated with the specified attribute member.
Children Returns all child members of the input member.
Descendants Returns the set of descendants of a member at specified layers.
DrilldownByLayer Drills down members of a set that are at a specified layer.
DrilldownMember Drills down on any members or tuples of <set1> that are also found in <set2>.
DrillupByLayer Drills up the members of a set that are below a specified layer.
DrillupMember Tests two sets for common ancestors, and drills up members in the first set to the layer of the ancestors which are present in the second set.
Extract Returns a subset containing only the tuples of a specified dimensionality.
Hierarchize Sorts members according to the default member ordering as represented in the database outline.
LastPeriods Returns a set of members ending either at the specified member or at the current member in the time dimension.
MemberRange Returns the range of members positioned between two input members (inclusive) at the same generation or level.
Members Returns a set of all members of a given dimension, hierarchy, or layer.
PeriodsToDate Returns a set of dynamic-time-series members from the beginning of a given layer up to a given member in that layer (or up to the default member); or, returns members up to the current member of the Time dimension.
RelMemberRange Returns a set based on the relative position of the specified member.
Siblings Returns the siblings of the input member.
Uda Returns all members that share a specified user-defined attribute.
WithAttr Returns all base members that are associated with an attribute member of the specified type.
xTD Functions returning period-to-date values.

Data-based Set Functions

Functions in this category derive their results using data values from the cube.

BottomCount Returns a set of n elements ordered from smallest to largest, optionally based on an evaluation.
BottomPercent Returns the smallest possible subset, with elements listed from smallest to largest, of a set for which the total results of a numeric evaluation are at least a given percentage.
BottomSum Returns the smallest possible subset, with elements listed from smallest to largest, of a set for which the total results of a numeric evaluation are at least a given sum.
Case Performs conditional expressions.
Filter Returns those parts of a set which meet the criteria of a search condition.
IIF Performs a conditional test, and returns an appropriate numeric expression or set depending on whether the test evaluates to true or false.
Order Sorts members of a set in order based on an expression.
TopCount Returns a set of n elements ordered from largest to smallest, optionally based on an evaluation.
TopPercent Returns the smallest possible subset, with elements listed from largest to smallest, of a set for which the total results of a numeric evaluation are at least a given percentage.
TopSum Returns the smallest possible subset, with elements listed from largest to smallest, of a set for which the total results of a numeric evaluation are at least a given sum.

Functions that Return a Tuple

The following functions return a tuple.

CurrentTuple Returns the current tuple in a set. Current is in the context of query execution mechanics. Use in combination with iterative functions such as Filter.
Item Extracts a member from a tuple.

Functions that Return a Number

The following functions return a value.

Numeric Value Functions

Numeric-value functions take a set of values as input, and return a single value.
Exception: Todate, which returns value from a string.

Avg Returns the average of values found in the tuples of a set.
CoalesceEmpty Returns the first non #Missing value from the given value expressions.
Case Performs conditional expressions.
Count Returns the count of the number of tuples in a set.
IIF Performs a conditional test, and returns an appropriate numeric expression or set depending on whether the test evaluates to true or false.
Max Returns the maximum of values found in the tuples of a set.
Min Returns the minimum of values found in the tuples of a set.
NonEmptyCount Returns the count of the number of tuples in a set that evaluate to nonempty values.
Sum Returns the sum of values of tuples in a set.
ToDate Converts a date string to a value that is usable in calculations.

Mathematical Functions

Mathematical functions take a single value as input, and return a single value.
Exception: Ordinal, which returns a value from a level reference.

Abs Returns absolute value of an expression.
Exp Returns the exponent of an expression.
Factorial Returns the factorial of an expression.
Int Returns the next lowest integer value of an expression.
Ln Returns the natural logarithm of an expression.
Log Returns the logarithm of an expression to a specified base.
Log10 Returns the base-10 logarithm of an expression.
Mod Returns the modulus (remainder value) of a division operation.
Ordinal Returns a number indicating depth in the hierarchy.
Power Returns the value of the numeric value expression raised to power.
Remainder Returns the remainder value of the numeric value expression.
Round Rounds a numeric value expression to the specified number of digits.
Truncate Removes the fractional part of a numeric value expression, returning the integer.

Functions that Return a Dimension

The following function returns a dimension.

Dimension Returns the dimension that contains the input element.

Functions that Return a Layer

The following functions return a layer. A layer is used to group the members of a dimension by hierarchical depth.

In Essbase, a layer is either a generation or a level, indicated by a name or a number.

Generation Returns the generation of the input member.
Generations Returns the generation specified by the input numerical depth and the input dimension or hierarchy.
Level Returns the level of the input member.
Levels Returns the level specified by the input numerical depth and the input dimension or hierarchy.

Functions that Return a Boolean

The following functions return a Boolean (TRUE or FALSE).

Is Returns TRUE if two members are identical.
IsAccType Returns TRUE if the current member has the associated accounts tag.
IsAncestor Returns TRUE if the first member is an ancestor of the second member.
IsChild Returns TRUE if the first member is a child of the second member.
IsEmpty Returns True if the value of an input numeric-value-expression is #MISSING.
IsGeneration Returns TRUE if the member is in a specified generation.
IsLeaf Returns TRUE if the member is a level-0 member.
IsLevel Returns TRUE if the member is in a specified level.
IsSibling Returns TRUE if the first member is a sibling of the second member.
IsUda Returns TRUE if the member has the associated UDA tag (user-defined attribute).
IsValid Returns TRUE if the specified element validates successfuly.

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com