Library of MDX Functions 
Planning and Consolidation provides several Multidimensional Expressions (MDX) functions you can use in your dimension rule formulas. You can use some of these MDX functions in advanced rule formulas as well.
A majority of the MDX functions define industry-standard financial ratios. You can use ratios to evaluate the performance of your business and identify potential problems. Ratios expose factors such as the earning power, solvency, efficiency, and debt load of your business.
The following table describes some of the more common MDX functions:
MDX Function |
Description |
Parameters |
|---|---|---|
Ancestor |
Returns the ancestor of a member at a specified level |
<Member>, <Level> |
ClosingPeriod |
Returns the last sibling among the descendants of a member at a level |
[<Level>[, <Member>] ] |
Cousin |
Returns the member with the same relative position under a member as the member specified |
<Member1>, <Member2> |
Current Member |
Returns the current member along a dimension during an iteration |
(none) |
Default Member |
Returns the default member of a dimension |
(none) |
FirstChild |
Returns the first child of a member |
(none) |
FirstSibling |
Returns the first child of the parent of a member |
(none) |
IsEmpty |
Determines if an expression evaluates to the empty cell value |
<Expression> |
Item |
Returns a member from a tuple |
<Numeric Expression> |
Lag |
Returns a member prior to the specified member along the member's dimension |
<Numeric Expression> |
LastChild |
Returns the last child of a member |
(none) |
LastSibling |
Returns the last child of the parent of a member |
(none) |
Lead |
Returns a member further along the specified member's dimension |
<Numeric Expression> |
Members |
Returns the member whose name is specified by a string expression |
(none) |
NextMember |
Returns the next member in the level that contains a specified member |
(none) |
OpeningPeriod |
Returns the first sibling among the descendants of a member at a level |
[<Level>[, <Member>] ] |
ParallelPeriod |
Returns a member from a prior period in the same relative position as a specified member |
[<Level>[, <Numeric Expression>[, <Member>] ] ] |
Parent |
Returns the parent of a member |
(none) |
PrevMember |
Returns the previous member in the level that contains a specified member |
(none) |
Aggregate |
Returns a calculated value using the appropriate aggregate function, based on the context of the function |
<Set>[, <Numeric Expression>] |
Avg |
Returns the average value of a numeric expression evaluated over a set |
<Set>[, <Numeric Expression>] |
CoalesceEmpty |
Coalesces an empty cell value to a number |
<Numeric Expression>[, <Numeric Expression>...] |
Correlation |
Returns the correlation of two series evaluated over a set |
<Set>, <Numeric Expression>[, <Numeric Expression>] |
Count |
Returns the number of tuples in a set, empty cells included unless the optional EXCLUDEEMPTY flag is used |
<Set>[, EXCLUDEEMPTY | INCLUDEEMPTY] |
IIf |
Returns one of two values determined by a logical test |
<Logical Expression>, <Numeric Expression>, <Numeric Expression> |
LinRegIntercept |
Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b |
<Set>, <Numeric Expression>[, <Numeric Expression>] |
LinRegPoint |
Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b |
<Numeric Expression>, <Set>, <Numeric Expression>[, <Numeric Expression>] |
LinRegR2 |
Calculates the linear regression of a set and returns R2 (the coefficient of determination) |
<Set>, <Numeric Expression>[, <Numeric Expression>] |
LinRegSlope |
Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b |
<Set>, <Numeric Expression>[, <Numeric Expression>] |
LinRegVariance |
Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b |
<Set>, <Numeric Expression>[, <Numeric Expression>] |
Max |
Returns the maximum value of a numeric expression evaluated over a set |
<Set>[, <Numeric Expression>] |
Median |
Returns the median value of a numeric expression evaluated over a set |
<Set>[, <Numeric Expression>] |
Min |
Returns the minimum value of a numeric expression evaluated over a set |
<Set>[, <Numeric Expression>] |
Sum |
Returns the sum of a numeric expression evaluated over a set |
<Set>[, <Numeric Expression>] |
For additional NW BI MDX keywords, run function module BAPI_MDPROVIDER_GET_FUNCTIONS via transaction SE37.