List of Supported MDX Functions by Atoti

MDX Functions

This table lists all existing MDX functions and details their support level in Atoti. The following notations are used:

Symbol	Meaning
	fully supported
	partially supported
	not supported
	will not be supported

Function Name	Support	Documentation & Comments
AddCalculatedMembers		MSDN AddCalculatedMembers (MDX)
Aggregate		Aggregate Function (MDX)
AllMembers		MSDN AllMembers (MDX)
Ancestor		MSDN Ancestor (MDX)
Ancestors		MSDN Ancestors (MDX)
Ascendants		MSDN Ascendants (MDX)
Avg		MSDN Avg (MDX)
Axis		MSDN Axis (MDX)
BottomCount		MSDN BottomCount (MDX)
BottomPercent		MSDN BottomPercent (MDX)
BottomSum		MSDN BottomSum (MDX)
CASE Statement		MSDN CASE Statement (MDX)
Caption		Caption Function (MDX)
CDate		MSDN Type Conversion Functions (VBA - CDate)
Children		MSDN Children (MDX)
CInt		MSDN Type Conversion Functions (VBA - CInt)
CLng		MSDN Type Conversion Functions (VBA - CLng)
ClosingPeriod		MSDN ClosingPeriod (MDX)
CoalesceEmpty		MSDN CoalesceEmpty (MDX) Without type constraint
Correlation		MSDN Correlation (MDX)
Count (Dimensions)		MSDN Count (Hierarchy Levels) (MDX)
Count (Levels)		MSDN Count (Hierarchy Levels) (MDX)
Count (Tuple)		MSDN Count (Tuple) (MDX)
Count (Set)		MSDN Count (Set) (MDX)
Cousin		MSDN Cousin (MDX)
Covariance		MSDN Covariance (MDX)
CovarianceN		MSDN CovarianceN (MDX)
CrossJoin		MSDN CrossJoin (MDX)
CStr		MSDN Type Conversion Functions (VBA - CStr)
Current		MSDN Current (MDX)
CurrentMember		MSDN Current Member (MDX)
CurrentOrdinal		MSDN Current Ordinal (MDX) Only works on Named sets
CustomData		MSDN CustomData (MDX)
DataMember		MSDN DataMember (MDX)
DateAdd		MSDN DateAdd (VBA)
DateDiff		MSDN DateDiff (VBA)
DatePart		MSDN DatePart (VBA) Optional arguments are not supported
Day		MSDN Day (DAX)
DefaultMember		MSDN DefaultMember (MDX)
Descendants		MSDN Descendants (MDX)
Dimension		MSDN Dimension (MDX)
Dimensions		MSDN Dimensions (MDX)
Distinct		MSDN Distinct (MDX)
DistinctCount		MSDN DistinctCount (MDX)
DrillDownLevel		MSDN DrillDownLevel (MDX)
DrillDownLevelBottom		MSDN DrillDownLevelBottom (MDX)
DrillDownLevelTop		MSDN DrillDownLevelTop (MDX)
DrillDownMember		MSDN DrillDownMember (MDX)
DrillDownMemberBottom		MSDN DrillDownMemberBottom (MDX)
DrillDownMemberTop		MSDN DrillDownMemberTop (MDX)
DrillupLevel		MSDN DrillUpLevel (MDX)
DrillupMember		MSDN DrillupMember (MDX)
Error		MSDN Error (MDX)
Except		MSDN Except (MDX)
Exists		MSDN Exists (MDX), Signature with MeasureGroup not supported (i.e. this function is an alias for NonEmpty)
Extract		MSDN Extract (MDX)
Filter		MSDN Filter (MDX)
FirstChild		MSDN FirstChild (MDX)
FirstSibling		MSDN FirstSibling (MDX)
Generate		MSDN Generate (MDX)
Head		MSDN Head (MDX)
Hierarchize		MSDN Hierarchize (MDX)
Hierarchy		MSDN Hierarchy (MDX)
IIf		MSDN IIf (MDX)
InStr		InStr Function (MDX)
Intersect		MSDN Intersect (MDX), Signature with ALL is not supported
IsAncestor		MSDN IsAncestor (MDX)
IsEmpty		MSDN IsEmpty (MDX)
IsGeneration		MSDN IsGeneration (MDX)
IsLeaf		MSDN IsLeaf (MDX)
IsNull		MSDN IsNull (VBA)
IsSibling		MSDN IsSibling (MDX)
Item (Member)		MSDN Item (Member) (MDX), Signature with strings is not supported
Item (Tuple)		MSDN Item (Tuple) (MDX), Signature with strings is not supported
KPIGoal		MSDN KPIGoal (MDX)
KPIStatus		MSDN KPIStatus (MDX)
KPITrend		MSDN KPITrend (MDX)
KPIWeight		MSDN KPIWeight (MDX)
KPICurrentTimeMember		MSDN KPICurrentTimeMember (MDX)
KPIValue		MSDN KPIValue (MDX)
Lag		MSDN Lag (MDX)
LastChild		MSDN LastChild (MDX)
LastPeriods		MSDN LastPeriods (MDX)
LastSibling		MSDN LastSibling (MDX)
LCase		MSDN LCase (VBA)
Lead		MSDN Lead (MDX)
Leaves		MSDN Leaves (MDX)
Left		Left Function
Level		MSDN Level (MDX)
Levels		MSDN Levels (MDX)
LinkMember		MSDN LinkMember (MDX)
LinRegIntercept		MSDN LinRegIntercept (MDX)
LinRegPoint		MSDN LinRegPoint (MDX)
LinRegR2		MSDN LinRegR2 (MDX)
LinRegSlope		MSDN LinRegSlope (MDX)
LinRegVariance		MSDN LinRegVariance (MDX)
LookupCube		MSDN LookupCube (MDX)
Max		MSDN Max (MDX)
MeasureGroupMeasures		MSDN MeasureGroupMeasures (MDX)
Median		MSDN Median (MDX)
member_caption		member_caption Function
Members		MSDN Members (MDX)
MemberToStr		MSDN MemberToStr (MDX)
MemberValue		MSDN MemberValue (MDX)
Min		MSDN Min (MDX)
Month		MSDN Month (DAX)
Mtd		MSDN Mtd (MDX)
Name		MSDN Name (MDX)
NameToSet		MSDN NameToSet (MDX)
NextMember		MSDN NextMember (MDX)
NonEmpty		MSDN NonEmpty (MDX)
NonEmptyCrossjoin		MSDN NonEmptyCrossjoin (MDX)
Now		MSDN Now (DAX)
OpeningPeriod		MSDN Opening Period (MDX)
Order		Atoti Order Function (MDX), `DESC` is sorted as in `AS 2000`
Ordinal		MSDN Ordinal (MDX)
ParallelPeriod		MSDN ParallelPeriod (MDX)
Parent		MSDN Parent (MDX)
PeriodsToDate		MSDN PeriodsToDate (MDX)
Predict		MSDN Predict (MDX)
PrevMember		MSDN PrevMember (MDX)
Properties		MSDN Properties (MDX)
Qtd		MSDN Qtd (MDX)
Rank		MSDN Rank (MDX)
Right		Right Function
RollupChildren		MSDN RollupChildren (MDX)
Round		MSDN Round (VBA)
SetToArray		MSDN SetToArray (MDX)
SetToStr		MSDN SetToStr (MDX)
Siblings		MSDN Siblings (MDX)
Stdev		MSDN Stdev (MDX)
StdevP		MSDN StdevP (MDX)
StripCalculatedMembers		MSDN StripCalculatedMembers (MDX)
StrToMember		MSDN StrToMember (MDX), acts as the CONSTRAINED keyword was provided
StrToSet		MSDN StrToSet (MDX)
StrToTuple		MSDN StrToTuple (MDX)
StrToValue		MSDN StrToValue (MDX)
Subset		MSDN Subset (MDX)
Sum		MSDN Sum (MDX)
Tail		MSDN Tail (MDX), one missing signature
This		MSDN This (MDX)
ToggleDrillState		MSDN ToggleDrillState (MDX)
TopCount		MSDN TopCount (MDX)
TopPercent		MSDN TopPercent (MDX)
TopSum		MSDN TopSum (MDX)
TupleToStr		MSDN TupleToStr (MDX)
UCase		MSDN UCase (VBA)
Union		MSDN Union (MDX)
UniqueName		MSDN UniqueName (MDX)
UnknownMember		MSDN UnknownMember (MDX)
Unorder		MSDN Unorder (MDX)
UserName		MSDN UserName (MDX)
ValidMeasure		MSDN ValidMeasure (MDX)
Value		MSDN Value (MDX)
Var		MSDN Var (MDX)
Variance		MSDN Variance (MDX)
VarianceP		MSDN VarianceP (MDX)
VarP		MSDN VaRP (MDX)
VisualTotals		VisualTotals function (MDX)
Weekday		MSDN Weekday (DAX)
Wtd		MSDN Wtd (MDX)
Year		MSDN Year (DAX)
Ytd		MSDN Ytd (MDX)

Functions Deviating from MDX Standards

Aggregate Function

The aggregate function in Atoti behaves mostly as described in the MSDN Aggregate (MDX) article. The differences are:

Signature with Numeric Expression as Second Argument not Supported

The signature with a numeric expression as second argument is not supported:

Aggregate(<Set Expression>) is supported
Aggregate(<Set Expression>, <Numeric Expression>) is not supported

Sets with Dimensionality 1

The aggregate function can only operate on sets with dimensionality 1.

Double Counting is Discarded

Double counting is discarded. For example, if you have a Geography dimension with the following members:

AllMember
  +- France
      +- Paris
      +- Lyon

and you ask for: Aggregate({[Geography].[ALL].[AllMember].[France].[Paris], [Geography].[ALL].[AllMember].[France].[Paris]}) , then Paris is only counted once.

Isolated Calculated Member

An aggregate can only be used as an isolated calculated member. It means

WITH MEMBER
  [Geography].[Test] AS Aggregate({[Geography].[ALL].[AllMember].[France], [Geography].[ALL].[AllMember].[USA]}) + 1
SELECT
  [Geography].[Test] ON COLUMNS
FROM [MyCube]

would not work and needs to be rewritten as

WITH MEMBER
  [Geography].[Test_Intermediary] AS Aggregate({[Geography].[ALL].[AllMember].[France], [Geography].[ALL].[AllMember].[USA]})
  [Geography].[Test] AS [Geography].[Test_Intermediary] + 1
SELECT
  [Geography].[Test] ON COLUMNS
FROM [MyCube]

Caption Function

This function returns the formatted name of a member. It is also the value of the Intrinsic Member Property MEMBER_CAPTION. See MSDN Intrinsic Member Properties (MDX).

Syntax:

<String Expression> <- <Member Expression>.Caption

InStr Function

This function looks for a string within another. It returns the index (one based) at which the sub-string starts, zero if it is not found. The lookup is case-insensitive. It is possible to specify an offset (one based) at which the search should start. See MSDN InStr Function (VBA).

Syntax

<Numeric Expression> <- InStr(
      [<Numeric Expression> : offset,]
      <String Expression> : value,
      <String expression> : lookup
)

offset (optional): The offset at which the lookup should start in the value. This offset is one based. If the offset is not an integer, an error is returned. If the offset is less or equal to zero, an error is returned.
value: The string to look into.
lookup: The string to look up.

Examples

InStr(1, "Test", "es") = 2
InStr("Test", "es") = 2
InStr("Test", "ES") = 2
InStr(0, "Test", "t") = ! ERROR !
InStr("Test", "k") = 0
InStr(10, "Test", "t") = 0
InStr(2, "Test", "t") = 4

Left Function

This function returns the sub-string of a string starting from the first character up to a specific input length.

Syntax

<String Expression> <- Left(
      <String Expression> : value,
      <Numeric expression> : length
)

value: The string to sub-string.
length: The length of the sub-string. If the length is not an integer, an error is returned. If the length is lower than zero, an error is returned.

Examples

Left("Test", 1) = "T"
Left("Test", 0) = ""
Left("Test", -1) = ! ERROR !
Left("Test", 10) = "Test"

Member Caption Function

The ‘member_caption Function’ is an alias for the ‘Caption Function’. See Caption Function (MDX)

Order Function

The MDX Order function in Atoti behaves mostly as described in the MSDN Order (MDX) documentation. This section provides additional details that are not mentioned in the MSDN page.

Nested Calls to Order and Hierarchize - Stable Sort

Calling Order(Hierarchize(set_expression)) deactivates the Hierarchize order. Calling Hierarchize(Order(set_expression)) deactivates the Order order. Calling Order(Order(set_expression)) performs a stable sort. This means that members that are equal according to the first (outer) ordering are sorted according to the second (inner) ordering. This functionality doesn’t work if any function is inserted between the calls to ‘Order’. For example, Order(Filter(Order(set_expression))) doesn’t perform a stable sort. Results are sorted only according to the first (outer) ordering.

Examples

// Always
Order(Crossjoin(Hierarchize([City].Members), [Currency].Members), BASC, cityMeasure) <=> Order(Crossjoin([City].Members, [Currency].Members), BASC, cityMeasure)
Hierarchize(Crossjoin(Order([City].Members, BASC, cityMeasure), [Currency].Members)) <=> Hierarchize(Crossjoin([City].Members, [Currency].Members))
// Sorts by descending currencies, breaking ties using default hierarchical order (no stable order because calls to Order do not directly follow each other)
Order(Crossjoin(Order([City].Members, ASC, cityMeasure), [Currency].Members), DESC, currencyMeasure)

// Stable ordering is supported when calls to Order directly follow each other
// Sorts by descending currencyMeasure, breaking ties by ascending cityMeasure
Order(Order(Crossjoin([City].Members, [Currency].Members), ASC, cityMeasure), DESC, currencyMeasure)

Right Function

It returns the sub-string of a string starting from the last character going backwards to a specific input length.

Syntax

<String Expression> <- Right(
      <String Expression> : value,
      <Numeric expression> : length
)

value: The string to sub-string.
length: The length of the sub-string. If the length is not an integer, an error is returned. If the length is lower than zero, an error is returned.

Examples

Right("Test", 1) = "t"
Right("Test", 0) = ""
Right("Test", -1) = ! ERROR !
Right("Test", 10) = "Test"

VisualTotals Function

The behavior of this function is slightly different from the standard (see MSDN). When you use VisualTotals on a dimension, only the members that appear in the given set and are not measures will be granted (see ISubCubeProperties in Standard Context Values). As a result, members not contained in the VisualTotals are excluded from the results.

Syntax

<Set> <- VisualTotals(
      <Set Expression> : set
      [, <String expression> : pattern]
)

set: The string to sub-string.
pattern: Not supported.

Examples

Assuming we have the following data:

Underlyings	contributors.COUNT
AllMember	7
EUR	4
JPY	2
USD	1

The two queries below return the same pivot table:

SELECT [Measures].[contributors.COUNT] ON 0,
VisualTotals({[Underlyings].[ALL].[AllMember], [Underlyings].[ALL].[AllMember].[EUR], [Underlyings].[ALL].[AllMember].[USD]}) ON 1
FROM [cube]

SELECT [Measures].[contributors.COUNT] ON 0,
{VisualTotals({[Underlyings].[ALL].[AllMember], [Underlyings].[ALL].[AllMember].[EUR], [Underlyings].[ALL].[AllMember].[USD]}), [Underlyings].[ALL].[AllMember].[JPY]} ON 1
FROM [cube]

Result:

Underlyings	contributors.COUNT
AllMember	5
EUR	4
USD	1

Custom Functions

Atoti also allows the use of several non-standard functions.

Is_DataMember

This function returns ‘true’ if the given member is a datamember, ‘false’ otherwise.

Syntax

MemberExpression.Is_DataMember

Is_UnknownMember

It returns true if the given member is an unknown member, false otherwise. A member is considered unknown either if it is the UnknownMember of its parent or if it has an UnknownMember in its path. For example, the member [dimension].[hierarchy].[level].[N/A].[A child].[A grand child] is considered unknown because it has N/A in its path.

Syntax

MemberExpression.Is_UnknownMember

ActiveMeasure

For details, see Active Measures and MDX compilation.

Today

This function returns the date of the current day.

Syntax

Today()

ReverseHierarchize

ReverseHierarchize reorders a set so that its elements appear in reverse hierarchical order. The reverse hierarchical order is defined as follows: for each hierarchy in the set, elements are ordered such that

Parents appear before their children
Among siblings, the ordering is reversed: items that appear later in the hierarchy come first

You may optionally provide a level. When a level is specified, only elements at that level are reversed and the other levels in all other hierarchies are sorted in ascending hierarchical order. If a level is specified, but elements of the given Set do not express a coordinate on that level, this function behaves like Hierarchize.

Example

Given the following set:

Hierarchy 1	Hierarchy 2	Hierarchy 3
AllMember	AllMember	AllMember
AllMember	AllMember/A	AllMember/X
AllMember	AllMember/A/A1	AllMember/X
AllMember	AllMember/A/A2	AllMember/Y
AllMember	AllMember/B	AllMember/Y
AllMember/J	AllMember/C	AllMember/Y
AllMember/K	AllMember/C/C1	AllMember/Z
AllMember/K	AllMember/C/C2	AllMember/Z

Applying ReverseHierarchize to the full set produces:

Hierarchy 1	Hierarchy 2	Hierarchy 3
AllMember	AllMember	AllMember
AllMember	AllMember/B	AllMember/Y
AllMember	AllMember/A	AllMember/X
AllMember	AllMember/A/A2	AllMember/Y
AllMember	AllMember/A/A1	AllMember/X
AllMember/K	AllMember/C/C2	AllMember/Z
AllMember/K	AllMember/C/C1	AllMember/Z
AllMember/J	AllMember/C	AllMember/Y

If the level argument is set to Level 1 of Hierarchy 2 (A, B, and C), the result is:

Hierarchy 1	Hierarchy 2	Hierarchy 3
AllMember	AllMember	AllMember
AllMember	AllMember/B	AllMember/Y
AllMember	AllMember/A	AllMember/X
AllMember	AllMember/A/A1	AllMember/X
AllMember	AllMember/A/A2	AllMember/Y
AllMember/J	AllMember/C	AllMember/Y
AllMember/K	AllMember/C/C1	AllMember/Z
AllMember/K	AllMember/C/C2	AllMember/Z

Syntax

<Set> <- ReverseHierarchize(
    <Set Expression> : set
    [, <Level Expression> : level]
)

​MDX Functions

​Functions Deviating from MDX Standards

​Aggregate Function

​Signature with Numeric Expression as Second Argument not Supported

​Sets with Dimensionality 1

​Double Counting is Discarded

​Isolated Calculated Member

​Caption Function

​Syntax:

​InStr Function

​Syntax

​Examples

​Left Function

​Syntax

​Examples

​Member Caption Function

​Order Function

​Nested Calls to Order and Hierarchize - Stable Sort

​Examples

​Right Function

​Syntax

​Examples

​VisualTotals Function

​Syntax

​Examples

​Custom Functions

​Is_DataMember

​Syntax

​Is_UnknownMember

​Syntax

​ActiveMeasure

​Today

​Syntax

​ReverseHierarchize

​Example

​Syntax

MDX Functions

Functions Deviating from MDX Standards

Aggregate Function

Signature with Numeric Expression as Second Argument not Supported

Sets with Dimensionality 1

Double Counting is Discarded

Isolated Calculated Member

Caption Function

Syntax:

InStr Function

Syntax

Examples

Left Function

Syntax

Examples

Member Caption Function

Order Function

Nested Calls to Order and Hierarchize - Stable Sort

Examples

Right Function

Syntax

Examples

VisualTotals Function

Syntax

Examples

Custom Functions

Is_DataMember

Syntax

Is_UnknownMember

Syntax

ActiveMeasure

Today

Syntax

ReverseHierarchize

Example

Syntax