MDX functions are used to simplify and broaden the scope
of MDX queries.
DB2® Warehouse Cubing
Services does not support the following MDX functions:
Contains function
EndsWith function
Format function
StartsWith function
Abs function (MDX)
The Abs function
returns the absolute value of a numeric argument.
AddCalculatedMembers function (MDX)
The AddCalculatedMembers function
returns a set that is generated by appending a specified set with
any calculated members that are siblings of the members of the specified
set.
Aggregate function (MDX)
The Aggregate function
applies an appropriate aggregation function to a set and returns
the number that results.
AllMembers dot function (MDX)
The AllMembers dot
function returns a set that contains all calculated and non-calculated
members within a specified hierarchy or level.
Ancestor function (MDX)
The Ancestor function
returns the ancestor of a member at a specified level or distance.
Ancestors function (MDX)
The Ancestors function
returns a set that contains an ancestor of a member at a specified
level or distance.
Ascendants function (MDX)
The Ascendants function
returns a set that includes all the ancestors of a member and the
member itself.
Avg function (MDX)
The Avg function
returns the average of the values of cells in a specified set.
Average function (MDX)
The Average function returns
the average of cells in a specified set.
BottomCount function (MDX)
The BottomCount function
sorts a set and returns the specified number of tuples that have
the lowest values in the set.
BottomPercent function (MDX)
The BottomPercent function
sorts a set and returns the lowest-valued tuples in the set that
sum to a specified percent of the total of the values of the set.
BottomSum function (MDX)
The BottomSum function
sorts a set and returns the lowest-valued tuples in the set that
make up a specified sum.
Caption dot function (MDX)
The Caption function
returns the name of the object. The Caption dot
function is an alias for the Name dot function.
ClosingPeriod function (MDX)
The ClosingPeriod function
returns the last descendant of a member that is at a particular level.
You can use this function to return the last period of a level of
the Time hierarchy.
CoalesceEmpty function (MDX)
The CoalesceEmpty function
helps you to manage null values by replacing empty cell values with
a numeric or string expression that you specify.
Count function (MDX)
The Count function
returns the number of members in a specified set.
Count dot function (MDX)
The Count dot
function returns the number of members in a specified set or the
count of levels in a hierarchy.
Cousin function (MDX)
The Cousin function
returns the descendant of the given ancestor that is at the same level
and holds the same relative position in the hierarchy as the given
member.
Crossjoin function (MDX)
The Crossjoin function
returns the cross product of the members of two sets.
DataMember dot function (MDX)
The DataMember dot
function returns the system-generated data member that is associated
with the context member. You can use this function on nonleaf members
in any hierarchy.
Description dot function (MDX)
The Description dot
function returns the name of the hierarchy, level, or member expression
to which it is affixed.
Dimension dot function (MDX)
The Dimension dot
function returns the hierarchy to which the specified level or member
belongs. You can have multiple hierarchies for one dimension.
Distinct function (MDX)
The Distinct function
evaluates a set of tuples and returns the same set with all duplicates
removed.
Edge function (MDX)
The Edge function returns
the cross product of the members of two sets.
Except function (MDX)
The Except function
takes two sets of tuples and returns a set that consists of the members
of the first set that are not members of the second set.
Extract function (MDX)
For each tuple in a given set, the Extract function
extracts the members of one or more specified hierarchies and returns
those members in a new set of tuples.
Filter function (MDX)
The Filter function
returns all tuples of a set for which a specified logical expression
is true.
Generate function (MDX)
The Generate function
applies a set expression or a string expression to each member of
a specified set. The function either returns the union of the sets
that result, or returns a string that is constructed by concatenating
the strings that result.
Generations function (MDX)
The Generations function returns
one generation of members that are at a particular depth of a hierarchy.
Head function (MDX)
The Head function
returns a specified number of elements from the beginning of a set.
Hierarchy dot function (MDX)
The Hierarchy dot
function returns the hierarchy to which the specified level or member
belongs. You can have multiple hierarchies for one dimension.
IIf function (MDX)
The IIf function
evaluates a logical expression and returns one of two specified values
based on the result.
InStr function (MDX)
The InStr function
finds the starting location of a substring within a specified string.
If the substring is not found within the specified string, the function
returns zero (false).
Int function (MDX)
The Int function returns the integer value
of a numeric argument.
Intersect function (MDX)
The Intersect function
returns a set that consists of the members that two specified sets
have in common.
IsAncestor function (MDX)
The IsAncestor function
determines whether one member is an ancestor of another member.
IsEmpty function (MDX)
The IsEmpty function
evaluates an expression and returns true if the expression evaluates
to null.
Item dot function (MDX)
The Item dot function
returns either a member of a tuple from a specified position within
the tuple, or a tuple of a set from a specified position within the
set.
Lag dot function (MDX)
The Lag dot function
returns the member that is a specified number of positions before
a specified member, on the same level.
LastPeriods function (MDX)
The LastPeriods function
returns a set of consecutive members that include a specified member
and can be located by the member and the index number.
Levels dot function (MDX)
The Levels dot
function returns the level of a hierarchy requested by the argument.
You can request the level by specifying the position of the level
in a numeric expression or by specifying the name of the level in
a string expression.
Max function (MDX)
The Max function
finds the largest value in a specified set.
Median function (MDX)
The Median function
finds the median (middle) value in a specified set.
Mid function (MDX)
The Mid function returns a substring of
a string argument.
Min function (MDX)
The Min function
finds the smallest value in a specified set.
MTD function (MDX)
The MTD function
returns a set that contains a particular member of the Time hierarchy
and all preceding members that are of the same level and are for
the same month. The MTD function is equivalent
to the PeriodsToDate function with a level of Time.Month specified.
Name dot function (MDX)
The Name function
returns the name of the object. The Name dot function
is an alias for the Caption dot function.
NameToSet function (MDX)
The NameToSet function
converts a member to a set that contains the original member.
Nest function (MDX)
The Nest function returns
the cross product of two sets.
NextMember dot function (MDX)
The NextMember dot
function returns the member that follows the specified member in
its hierarchy.
NonEmptyCrossjoin function (MDX)
The NonEmptyCrossjoin function
returns the cross product of the members of two or more sets, excluding
empty tuples and tuples with which no data is associated.
OpeningPeriod function (MDX)
The OpeningPeriod function
returns the first descendant of a member that is at a particular
level. You can use this function to return the first period of a
level of the Time hierarchy.
Order function (MDX)
The Order function
sorts the members of a set in ascending or descending order. You can
specify whether to preserve or to break the hierarchy of the set.
ParallelPeriod function (MDX)
The ParallelPeriod function
finds the cousin of a specified member that occupies the same position
relative to its ancestor at a specified level, as the specified member
occupies relative to its ancestor at the same level. You can use
this function with the Time hierarchy to get the time period that
corresponds to a specified period under a different ancestor.
PatternMatches function (MDX)
The PatternMatches function
tests whether a specified regular expression string matches a specified
string.
PeriodsToDate function (MDX)
The PeriodsToDate function
returns a set that contains a particular member of the Time hierarchy
and all preceding members that are both of the same level and from
the same time period.
PrevMember dot function (MDX)
The PrevMember dot
function returns the member that precedes the specified member in
its hierarchy.
QTD function (MDX)
The QTD function
returns a set that contains a particular member of the Time hierarchy
and all preceding members that are of the same level and are for
the same quarter. The QTD function is equivalent
to the PeriodsToDate function with a level of Time.Quarter specified.
Rank function (MDX)
The Rank function
reports the rank of a tuple in a set. You can specify how to rank
the tuples in the set.
Right function (MDX)
The Right function
returns a string of a specified number of characters from the right
side (end) of a specified string.
Round function (MDX)
The Round function rounds a numeric value
to the nearest integer or real number.
RoundDown function (MDX)
The RoundDown function rounds a numeric
value down to the nearest integer or real number.
RoundUp function (MDX)
The RoundUp function rounds a numeric
value up to the nearest integer or real number.
Siblings dot function (MDX)
The Siblings dot
function returns all of the siblings of a member, including the original
member that the function is called on.
StdDev function (MDX)
The StdDev function computes
the sample standard deviation of a numeric expression that is evaluated
over a set. Use the function when your set contains a partial sample
of data for the population that you are interested in.
StdDevP function (MDX)
The StdDevP function computes
the population standard deviation of a numeric expression that is
evaluated over a set. Use this function when your set contains every
member of the population that you are interested in.
Stdev function (MDX)
The Stdev function computes the sample standard deviation of a numeric
expression that is evaluated over a set. Use the function when your
set contains a partial sample of data for the population that you
are interested in.
StdevP function (MDX)
The StdevP function computes the population standard deviation of a
numeric expression that is evaluated over a set. Use this function
when your set contains every member of the population that you are
interested in.
Subset function (MDX)
The Subset function
returns a subset of elements from a specified set.
Sum function (MDX)
The Sum function
returns the sum of the results of evaluating a numeric expression
over a set.
Tail function (MDX)
The Tail function
returns a specified number of elements from the end of a set.
ToggleDrillState function (MDX)
The ToggleDrillState function
toggles the drill state of members, then returns the set that contains
all the members whose drill state are toggled.
TopCount function (MDX)
The TopCount function
sorts a set and returns the specified number of tuples that have
the greatest values in the set.
TopPercent function (MDX)
The TopPercent function
sorts a set and returns the highest-valued tuples in the set that
sum to a specified percent of the total of the values of the set.
TopSum function (MDX)
The TopSum function
sorts a set and returns the highest-valued tuples in the set that
make up a specified sum.
Union function (MDX)
The Union function
returns the union of two sets. The result set includes every member
of the first set in addition to every member of the second set.
Var function (MDX)
The Var function computes the sample variance of a numeric expression
that is evaluated over a set. Use this function when your set contains
a sample of data for the population that you are interested in.
Variance function (MDX)
The Variance function computes
the sample variance of a numeric expression that is evaluated over
a set. Use this function when your set contains a sample of data for
the population that you are interested in.
VarianceP function (MDX)
The VarianceP function returns
the population variance of a numeric expression evaluated over a set.
Use this function when your set contains every member of the population
you are interested in.
VarP function (MDX)
The VarP function returns the population variance of a numeric expression
evaluated over a set. Use this function when your set contains every
member of the population you are interested in.
WTD function (MDX)
The WTD function
returns a set that contains a particular member of the Time hierarchy
and all preceding members that are of the same level and are from
the same week. The WTD function is equivalent
to the PeriodsToDate function with a level of Time.Week specified.
YTD function (MDX)
The YTD function
returns a set that contains a particular member of the Time hierarchy
and all preceding members that are of the same level and are for
the same year. The YTD function is equivalent
to the PeriodsToDate function with a level of Time.Year specified.
Math functions DB2 Warehouse Cubing
Services support functions that are used for mathematical computation.