Help IndexTable of Contents

MDX Function Listing

The following guide is a summary of the functions available in the Pyramid MDX Editor. The descriptions have been derived from the online MDX reference published by Microsoft.

For more detail on the structure of each function and how it should be used please check this reference and other third party materials.

MDX Function Name Grouping Type Description
AddCalculatedMembers Set function Returns a set generated by adding calculated members to a specified set.
Aggregate Numeric function Returns a scalar value calculated by aggregating either measures or an optionally specified numeric expression over the tuples of a specified set.
AllMembers Dimension property Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level.
AllMembers Hierarchy property Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level.
AllMembers Level property Returns a set that contains all members, including calculated members, of the specified dimension, hierarchy, or level.
Ancestor Member function Returns the ancestor of a member at a specified level or distance.
Ancestors Set function Returns a set of all ancestors of a member at a specified level or distance.
Ascendants Set function Returns the set of the ascendants of a specified member, including the member itself.
Avg Numeric function Returns the average value of measures or the average value of an optional numeric expression, evaluated over a specified set.
Axis Set function Returns a set defined in an axis.
BottomCount Set function Sorts a set in ascending order, and returns the specified number of tuples with the lowest values.
BottomPercent Set function Sorts a set in ascending order, and returns a set of tuples with the lowest values whose cumulative total is equal to or less than a specified percentage.
BottomSum Set function Sorts a set in ascending order, and returns a set of tuples with the lowest values whose total is equal to or less than a specified value.
CalculationCurrentPass Numeric function Returns the current calculation pass of a cube for the specified query context.
CalculationPassValue Numeric function Returns the value of a MDX expression evaluated over the specified calculation pass of a cube.
CalculationPassValue Numeric function Returns the value of a MDX expression evaluated over the specified calculation pass of a cube.
Children Set property Returns the children of a specified member.
ClosingPeriod Member function Returns the last sibling among the descendants of a member at a specified level.
CoalesceEmpty Numeric function Coalesces an empty cell value to a number or string and returns the coalesced value.
Correlation Numeric function Returns the correlation coefficient of two series evaluated over a set.
Count Numeric function Returns the number of cells in a set.
Count Numeric property Returns the number of cells in a set.
Cousin Member function Returns the child member with the same relative position under a parent member as the specified child member.
Covariance Numeric function Returns the population covariance of two series evaluated over a set, using the biased population formula.
CovarianceN Numeric function Returns the sample covariance of two series evaluated over a set, using the unbiased population formula.
Crossjoin Set function Returns the cross product of one or more sets.
Current Member property Returns the current tuple from a set during iteration.
CurrentMember Dimension property Returns the current member along a specified dimension or hierarchy during iteration.
CurrentMember Hierarchy property Returns the current member along a specified dimension or hierarchy during iteration.
CurrentOrdinal Numeric property Returns the current iteration number within a set during iteration.
DataMember Numeric property Returns the system-generated data member that is associated with a non-leaf member of a dimension.
DefaultMember Member property Returns the default member of a dimension or hierarchy.
DefaultMember Member property Returns the default member of a dimension or hierarchy.
Descendants Set function Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
Descendants Set function Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
Dimension Hierarchy property Returns the dimension that contains a specified member, level, or hierarchy.
Dimension Level property Returns the dimension that contains a specified member, level, or hierarchy.
Dimension Member property Returns the dimension that contains a specified member, level, or hierarchy.
Dimensions Dimension function Returns a hierarchy specified by a numeric or string expression.
Distinct Set function Returns a set, removing duplicate tuples from a specified set.
DistinctCount Numeric function Returns the number of distinct, nonempty tuples in a set.
DrilldownLevel Set function Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set.
DrilldownLevel Set function Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set.
DrilldownLevelBottom Set function Drills down the bottommost members of a set, at a specified level, to one level below.
DrilldownLevelTop Set function Drills down the topmost members of a set, at a specified level, to one level below.
DrilldownMember Set function Drills down the members in a specified set that are present in a second specified set. Alternatively, the function drills down on a set of tuples.
DrilldownMemberBottom Set function Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members. Alternatively, this function also drills down on a set of tuples.
DrilldownMemberTop Set function Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members. Alternatively, this function drills down on a set of tuples.
DrillupLevel Set function Drills up the members of a set that are below a specified level.
DrillupMember Set function Drills up the members in a specified set that are present in a second specified set.
Error Logical function Raises an error, optionally providing a specified error message.
Except Set function Finds the difference between two sets, optionally retaining duplicates.
Exists Set function Returns the set of members of one set that exist with one or more tuples of one or more other sets.
Extract Set function Returns a set of tuples from extracted dimension elements.
Filter Set function Returns the set that results from filtering a specified set based on a search condition.
FirstChild Member property Returns the first child of a member.
FirstSibling Member property Returns the first child of the parent of a member.
Generate Set function Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set.
Generate Set function Applies a set to each member of another set, and then joins the resulting sets by union. Alternatively, this function returns a concatenated string created by evaluating a string expression over a set.
Head Set function Returns the first specified number of elements in a set, while retaining duplicates.
Hierarchize Set function Orders the members of a set in a hierarchy.
Hierarchy Level property Returns the hierarchy that contains a specified member or level.
Hierarchy Member property Returns the hierarchy that contains a specified member or level.
IIf Logical function Returns one of two values determined by a logical test.
Intersect Set function Returns the intersection of two input sets, optionally retaining duplicates.
IsAncestor Logical function Returns whether a specified member is an ancestor of another specified member.
IsEmpty Logical function Returns whether the evaluated expression is the empty cell value.
IsGeneration Logical function Returns whether a specified member is in a specified generation.
IsLeaf Logical function Returns whether a specified member is a leaf member.
IsSibling Logical function Returns whether a specified member is a sibling of another specified member.
Item Member property Returns a member from a specified tuple.
KPICurrentTimeMember KPI function Returns the current time member of the specified Key Performance Indicator (KPI).
KPIGoal KPI function Returns the member that calculates the value for the goal portion of the specified Key Performance Indicator (KPI).
KPIStatus KPI function Returns the member that calculates the value for the status portion of the specified Key Performance Indicator (KPI).
KPITrend KPI function Returns the member that calculates the value for the trend portion of the specified Key Performance Indicator (KPI).
KPIValue KPI function Returns the member that calculates the value for the value portion of the specified Key Performance Indicator (KPI).
KPIWeight KPI function Returns the weight of the specified Key Performance Indicator (KPI).
Lag Member property Returns the member that is a specified number of positions before a specified member along the member's dimension.
LastChild Member property Returns the last child of a specified member.
LastPeriods Set function Returns a set of members up to and including a specified member.
LastSibling Member property Returns the last child of the parent of a specified member.
Lead Member property Returns the member that is a specified number of positions following a specified member along the member's dimension.
Leaves Set function Returns the set of leaf members in the specified dimension, member, or tuple.
Level Level property Returns the level of a member.
Levels Member property Returns the level whose position in a dimension or hierarchy is specified by a numeric expression or whose name is specified by a string expression.
LinkMember Member function Returns the member equivalent to a specified member in a specified hierarchy.
LinRegIntercept Numeric function Calculates the linear regression of a set and returns the value of the intercept in the regression line, y = ax + b.
LinRegPoint Numeric function Calculates the linear regression of a set and returns the value of y in the regression line, y = ax + b.
LinRegR2 Numeric function Calculates the linear regression of a set and returns the coefficient of determination, R2.
LinRegSlope Numeric function Calculates the linear regression of a set, and returns the value of the slope in the regression line, y = ax + b.
LinRegVariance Numeric function Calculates the linear regression of a set, and returns the variance associated with the regression line, y = ax + b.
LookupCube Member function Returns the value of an MDX expression evaluated over another specified cube in the same database.
Max Numeric function Returns the maximum value of a numeric expression that is evaluated over a set.
MeasureGroupMeasures Set function Returns a set of measures that belongs to the specified measure group.
Median Numeric function Returns the median value of a numeric expression that is evaluated over a set.
Members Dimension property Returns the set of members in a dimension, level, or hierarchy.
Members Hierarchy property Returns the set of members in a dimension, level, or hierarchy.
Members Level property Returns the set of members in a dimension, level, or hierarchy.
Members Set function Returns the set of members in a dimension, level, or hierarchy.
MemberToStr String function Returns an MDX–formatted string that corresponds to a specified member.
Min Numeric function Returns the minimum value of a numeric expression that is evaluated over a set.
Mtd Set function Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension.
Name Dimension property Returns the name of a dimension, hierarchy, level, or member.
Name Hierarchy property Returns the name of a dimension, hierarchy, level, or member.
Name Level property Returns the name of a dimension, hierarchy, level, or member.
Name Member property Returns the name of a dimension, hierarchy, level, or member.
NameToSet Set function Returns a set that contains the member specified by an MDX–formatted string.
NextMember Member property Returns the next member in the level that contains a specified member.
NonEmptyCrossjoin Set function Returns the cross product of one or more sets as a set, excluding empty tuples and tuples without associated fact table data.
OpeningPeriod Member function Returns the first sibling among the descendants of a specified level, optionally at a specified member.
Order Set function Arranges members of a specified set, optionally preserving or breaking the hierarchy.
Ordinal Numeric property Returns the zero-based ordinal value associated with a level.
ParallelPeriod Member function Returns a member from a prior period in the same relative position as a specified member.
Parent Member property Returns the parent of a member.
PeriodsToDate Set function Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension.
Predict Numeric function Returns a value of a numeric expression evaluated over a data mining model.
PrevMember Member property Returns the previous member in the level that contains a specified member.
Properties Numeric property Returns a string, or a strongly-typed value, that contains a member property value.
Qtd Set function Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Quarter level in the Time dimension.
Rank Numeric function Returns the one-based rank of a specified tuple in a specified set.
RollupChildren Numeric function Returns a value generated by rolling up the values of the children of a specified member using the specified unary operator.
Root Member function Returns a tuple that consists of the All members from each attribute hierarchy in a cube, dimension, or tuple.
SetToArray Other function Converts one or more sets to an array for use in a user-defined function.
SetToStr String function Returns an MDX-formatted string of that corresponds to a specified set.
Siblings Set property Returns the siblings of a specified member, including the member itself.
Stddev Numeric function Alias for Stdev (MDX).
StddevP Numeric function Alias for StdevP (MDX).
Stdev Numeric function Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula.
StdevP Numeric function Returns the population standard deviation of a numeric expression evaluated over a set, using the biased population formula.
StripCalculatedMembers Set function Returns a set generated by removing calculated members from a specified set.
StrToMember Member function Returns the member specified by an MDX–formatted string.
StrToSet Set function Returns the set specified by an MDX–formatted string.
StrToTuple Tuple function Returns the tuple specified by an MDX–formatted string.
StrToValue Numeric function Returns the value specified by an MDX–formatted string.
Subset Set function Returns a subset of tuples from a specified set.
Sum Numeric function Returns the sum of a numeric expression evaluated over a set.
Tail Set function Returns a subset from the end of a set.
ToggleDrillState Set function Toggles the drill state of members.
TopCount Set function Sorts a set in descending order and returns the specified number of elements with the highest values.
TopPercent Set function Sorts a set in descending order, and returns a set of tuples with the highest values whose cumulative total is equal to or less than a specified percentage.
TopSum Set function Sorts a set and returns the topmost elements whose cumulative total is at least a specified value.
TupleToStr Tuple function Returns an MDX–formatted string that corresponds to specified tuple.
Union Set function Returns the union of two sets, optionally retaining duplicates.
UniqueName Dimension property Returns the unique name of a specified dimension, hierarchy, level, or member.
UniqueName Hierarchy property Returns the unique name of a specified dimension, hierarchy, level, or member.
UniqueName Level property Returns the unique name of a specified dimension, hierarchy, level, or member.
UniqueName Member property Returns the unique name of a specified dimension, hierarchy, level, or member.
UnknownMember Member property Returns the unknown member associated with a level or member.
UnknownMember Member property Returns the unknown member associated with a level or member.
Unorder Set function Removes any enforced ordering from a specified set.
UserName String function Returns the domain name and user name of the current connection.
ValidMeasure Member function Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level.
Value Member property Returns the value of a measure.
Var Numeric function Returns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula.
Variance Numeric function Alias for Var (MDX).
VarianceP Numeric function Alias for VarP (MDX).
VarP Numeric function Returns the population variance of a numeric expression evaluated over a set, using the biased population formula.
VisualTotals Set function Returns a set generated by dynamically totaling child members in a specified set, optionally using a pattern for the name of the parent member in the resulting cellset.
Wtd Set function Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Week level in the Time dimension.
Ytd Set function Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension.

Home | Table of Contents | Index | User Community
Pyramid Analytics © 2011-2018