MDX Reference
This section lists the MDX functions and operators allowed in a calculated measure formula.
- ABS
The
ABS
function returns the absolute value of the argument. - AGGREGATE
The
AGGREGATE
function Returns a number that is calculated by aggregating over the cells returned by the set expression. - ALL
Refers to the
All
level with a single member called theAll
member. Used to constrain a tuple expression. - ANCESTOR A function that returns the ancestor of a specified member at a specified level or at a specified distance from the member.
- Arithmetic Operators (MDX) Arithmetic operators perform basic math operations on their operands. AtScale supports arithmetic operators in Calculated Measure formulas. Notice the NULL handling behavior in MDX is different than SQL.
- AVG
The
AVG
function returns the average of a Measure evaluated over a given set. - CASE
The
CASE
function evaluates each row in the dataset according to one or more input conditions, and outputs the specified result when the input conditions are met. - CBOOL Casts the value to a Boolean.
- CDBL Casts the value to a Double.
- CDEC Casts the value to a Decimal number.
- CEILING
The
CEILING
function returns the smallest interger value greater than or equal to the argument. - CHILDREN Returns a naturally ordered set that contains the children of a specified member.
- CINT Casts the value to an Integer.
- CLONG Casts the value a Long.
- Comparison Operators (MDX) Comparison operators evaluate to a Boolean data type, returning true or false based on the outcome of the tested condition.
- COUNT
The
COUNT
function returns the size of a given set. - CSTR Casts the value to a String.
- CURRENTMEMBER.NAME
The
CURRENTMEMBER.NAME
function returns the current member value of the specified dimension attribute during iteration. - DAY Returns the day of month from the Date or DateTime measure as an integer.
- DESCENDANTS Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
- DIMENSION UNIQUE NAME The unique name of the dimension to which this member belongs.
- DIVIDE Performs division and returns alternate result or BLANK() on division by zero.
- E Returns the value of mathematical constant e.
- EXP Returns the mathematical constant e raised to the specified power.
- FIRSTCHILD Returns the first child of an input member.
- FIRSTSIBLING Returns the first child of the parent of a member, otherwise known as the member's first sibling.
- FLOOR
The
FLOOR
function returns the largest interger value less than or equal to the argument. - HIERARCHY_UNIQUE_NAME The unique name of the hierarchy to which this member belongs.
- HOUR Returns the hour of day component of a DateTime or Timestamp measure as an integer.
- IIF
The
IIF
function evaluates one of two different expressions, depending on whether a Boolean condition is true or false. - INSTR
The
InStr
function returns the position of the first occurrence of one string within another string. - ISEMPTY
The
ISEMPTY
function evaluates if a cell in a cube is empty or not. - KEY The value of the member key in the original data type.
- LAG
The
LAG
function returns a member that precedes the specified member by a specified number of positions in its level. - LASTCHILD Returns the last child of a specified member.
- LASTSIBLING Returns the last child of the parent of a member, otherwise known as the member's last sibling.
- LEAD
The
LEAD
function returns a member that follows the specified member by a specified number of positions in its level. - LEFT
The
LEFT
function returns a string of a specified number of characters from the left side of a specified string. - LEN Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.
- LEVEL Returns the Name of a dimension member's Level.
- LEVEL_NUMBER The distance of the member from the root of the hierarchy.
- LEVEL_UNIQUE_NAME The unique name of the level to which the member belongs.
- LOG Returns the base x logarithm of the argument.
- LOG2 Returns the base 2 logarithm of the argument.
- LOG10 Returns the base 10 logarithm if the argument.
- Logical Operators (MDX) Logical operators are used to combine arguments in a Boolean expression (an expression that evaluates to true or false).
- MAX
The
MAX
function returns the max of a Measure evaluated over a given set. - MEMBER_CAPTION A label or caption associated with the member. The caption is primarily for display purposes.
- MEMBER_KEY The value of the member key in the original data type.
- MEMBER_LEVEL_NUMBER The distance of the member from the root of the hierarchy.
- MEMBER_NAME The name of the member.
- MID
The
MID
function returns a substring of a string argument. - MIN
The
MIN
function returns the min of a Measure evaluated over a given set. - MINUTE Returns the minute of hour component of a DateTime or Timestamp measure as an integer.
- MONTH Returns the Month component of a Date or DateTime measure as an integer.
- NEXTMEMBER
The
NEXTMEMBER
function returns the member that follows the specified member in the level. - NOW Returns the DateTime from the data warehouse.
- PARALLELPERIOD
The
PARALLELPERIOD
funciton returns a member from the same relative position in a previous period as the specified member. - PARENT Returns the parent member of the specified member.
- PARENT_COUNT The number of parents that this member has.
- PARENT_LEVEL The distance of the member's parent from the root level of the hierarchy.
- PERIODSTODATE
The
PERIODSTODATE
function returns a set of sibling members from the specified level of a time dimension, beginning with the first sibling and ending with a specified member. - PI Returns the value of mathematical constant Pi.
- POW Returns a base raised to a power.
- PREVMEMBER
The
PrevMember
function returns the member that precedes the specified member in the level. - PROPERTIES Returns the value of the specified member for the specified member property.
- RAND - Returns a random number between 0 and 1 based on an optional seed.
- RIGHT -
The
Right
function returns a string of a specified number of characters from the right side of a specified string - ROUND - Round a number to an integer or the specified number of fractional digits.
- SECOND Returns the second of minute component of a DateTime or Timestamp measure as an integer.
- Set Operators (MDX) Set operators perform operations on members or sets, and return a set.
- SIBLINGS Returns the siblings of a member, including the member.
- SIGN
The
SIGN
function returns -1, 1, or 0 if the sign of the argument is either negative, positive, or undefined (zero). - SQLSUM
The
SQLSUM
function returns the sum of its arguments. - SUM
The
SUM
function returns the sum of a Measure evaluated over a given set. - Trigonometric Functions AtScale supports the following MDX trigonometric functions in a calculated measure formula.
- TRIM | LTRIM |
RTRIM
The
TRIM
function removes both leading and trailing spaces from the specified string.LTRIM
removes just leading spaces, andRTRIM
removes just trailing spaces. - TRUNCATE Returns the integral component of a floating-point or fixed-precision number.
- Tuple Expressions AtScale supports simple cube-sided tuple expressions for calculated measures.
- UCASE |
LCASE
The
UCASE
converts all alphabetic characters in a string to all upper case..LCASE
converts all alphabetic characters in a string to all lower case. - YEAR Returns the Year component of a Date or DateTime measure as an integer.