Skip to main content

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:

SymbolMeaning
tickfully supported
pluspartially supported
minusnot supported
crosswill not be supported
Function NameSupportDocumentation & Comments
AddCalculatedMemberstickMSDN AddCalculatedMembers (MDX)
AggregateplusAggregate Function (MDX)
AllMemberstickMSDN AllMembers (MDX)
AncestortickMSDN Ancestor (MDX)
AncestorstickMSDN Ancestors (MDX)
AscendantstickMSDN Ascendants (MDX)
AvgtickMSDN Avg (MDX)
AxiscrossMSDN Axis (MDX)
BottomCounttickMSDN BottomCount (MDX)
BottomPercenttickMSDN BottomPercent (MDX)
BottomSumtickMSDN BottomSum (MDX)
CASE StatementtickMSDN CASE Statement (MDX)
CaptiontickCaption Function (MDX)
CDatetickMSDN Type Conversion Functions (VBA - CDate)
ChildrentickMSDN Children (MDX)
CInttickMSDN Type Conversion Functions (VBA - CInt)
CLngtickMSDN Type Conversion Functions (VBA - CLng)
ClosingPeriodtickMSDN ClosingPeriod (MDX)
CoalesceEmptytickMSDN CoalesceEmpty (MDX) Without type constraint
CorrelationtickMSDN Correlation (MDX)
Count (Dimensions)tickMSDN Count (Hierarchy Levels) (MDX)
Count (Levels)tickMSDN Count (Hierarchy Levels) (MDX)
Count (Tuple)tickMSDN Count (Tuple) (MDX)
Count (Set)tickMSDN Count (Set) (MDX)
CousintickMSDN Cousin (MDX)
CovariancetickMSDN Covariance (MDX)
CovarianceNtickMSDN CovarianceN (MDX)
CrossJointickMSDN CrossJoin (MDX)
CStrtickMSDN Type Conversion Functions (VBA - CStr)
CurrenttickMSDN Current (MDX)
CurrentMembertickMSDN Current Member (MDX)
CurrentOrdinalplusMSDN Current Ordinal (MDX) Only works on Named sets
CustomDatacrossMSDN CustomData (MDX)
DataMembertickMSDN DataMember (MDX)
DateAddtickMSDN DateAdd (VBA)
DateDifftickMSDN DateDiff (VBA)
DateParttickMSDN DatePart (VBA) Optional arguments are not supported
DaytickMSDN Day (DAX)
DefaultMembertickMSDN DefaultMember (MDX)
DescendantstickMSDN Descendants (MDX)
DimensiontickMSDN Dimension (MDX)
DimensionstickMSDN Dimensions (MDX)
DistincttickMSDN Distinct (MDX)
DistinctCounttickMSDN DistinctCount (MDX)
DrillDownLeveltickMSDN DrillDownLevel (MDX)
DrillDownLevelBottomplusMSDN DrillDownLevelBottom (MDX)
DrillDownLevelTopplusMSDN DrillDownLevelTop (MDX)
DrillDownMemberplusMSDN DrillDownMember (MDX)
DrillDownMemberBottomplusMSDN DrillDownMemberBottom (MDX)
DrillDownMemberTopplusMSDN DrillDownMemberTop (MDX)
DrillupLeveltickMSDN DrillUpLevel (MDX)
DrillupMembertickMSDN DrillupMember (MDX)
ErrortickMSDN Error (MDX)
ExcepttickMSDN Except (MDX)
ExiststickMSDN Exists (MDX), Signature with MeasureGroup not supported (i.e. this function is an alias for NonEmpty)
ExtracttickMSDN Extract (MDX)
FiltertickMSDN Filter (MDX)
FirstChildtickMSDN FirstChild (MDX)
FirstSiblingtickMSDN FirstSibling (MDX)
GeneratetickMSDN Generate (MDX)
HeadtickMSDN Head (MDX)
HierarchizetickMSDN Hierarchize (MDX)
HierarchytickMSDN Hierarchy (MDX)
IIftickMSDN IIf (MDX)
InStrtickInStr Function (MDX)
IntersecttickMSDN Intersect (MDX), Signature with ALL is not supported
IsAncestortickMSDN IsAncestor (MDX)
IsEmptytickMSDN IsEmpty (MDX)
IsGenerationcrossMSDN IsGeneration (MDX)
IsLeaftickMSDN IsLeaf (MDX)
IsNulltickMSDN IsNull (VBA)
IsSiblingminusMSDN IsSibling (MDX)
Item (Member)plusMSDN Item (Member) (MDX), Signature with strings is not supported
Item (Tuple)plusMSDN Item (Tuple) (MDX), Signature with strings is not supported
KPIGoaltickMSDN KPIGoal (MDX)
KPIStatustickMSDN KPIStatus (MDX)
KPITrendtickMSDN KPITrend (MDX)
KPIWeightminusMSDN KPIWeight (MDX)
KPICurrentTimeMemberminusMSDN KPICurrentTimeMember (MDX)
KPIValuetickMSDN KPIValue (MDX)
LagtickMSDN Lag (MDX)
LastChildtickMSDN LastChild (MDX)
LastPeriodstickMSDN LastPeriods (MDX)
LastSiblingtickMSDN LastSibling (MDX)
LCasetickMSDN LCase (VBA)
LeadtickMSDN Lead (MDX)
LeavesminusMSDN Leaves (MDX)
LefttickLeft Function
LeveltickMSDN Level (MDX)
LevelstickMSDN Levels (MDX)
LinkMembercrossMSDN LinkMember (MDX)
LinRegIntercepttickMSDN LinRegIntercept (MDX)
LinRegPointtickMSDN LinRegPoint (MDX)
LinRegR2tickMSDN LinRegR2 (MDX)
LinRegSlopetickMSDN LinRegSlope (MDX)
LinRegVariancetickMSDN LinRegVariance (MDX)
LookupCubecrossMSDN LookupCube (MDX)
MaxtickMSDN Max (MDX)
MeasureGroupMeasurescrossMSDN MeasureGroupMeasures (MDX)
MediantickMSDN Median (MDX)
member_captiontickmember_caption Function
MemberstickMSDN Members (MDX)
MemberToStrminusMSDN MemberToStr (MDX)
MemberValuetickMSDN MemberValue (MDX)
MintickMSDN Min (MDX)
MonthtickMSDN Month (DAX)
MtdtickMSDN Mtd (MDX)
NametickMSDN Name (MDX)
NameToSetcrossMSDN NameToSet (MDX)
NextMembertickMSDN NextMember (MDX)
NonEmptytickMSDN NonEmpty (MDX)
NonEmptyCrossjoincrossMSDN NonEmptyCrossjoin (MDX)
NowtickMSDN Now (DAX)
OpeningPeriodtickMSDN Opening Period (MDX)
OrdertickAtoti Order Function (MDX), DESC is sorted as in AS 2000
OrdinaltickMSDN Ordinal (MDX)
ParallelPeriodtickMSDN ParallelPeriod (MDX)
ParenttickMSDN Parent (MDX)
PeriodsToDatetickMSDN PeriodsToDate (MDX)
PredictcrossMSDN Predict (MDX)
PrevMembertickMSDN PrevMember (MDX)
PropertiesplusMSDN Properties (MDX)
QtdtickMSDN Qtd (MDX)
RanktickMSDN Rank (MDX)
RighttickRight Function
RollupChildrencrossMSDN RollupChildren (MDX)
RoundplusMSDN Round (VBA), numdecimalplaces is not supported
SetToArraycrossMSDN SetToArray (MDX)
SetToStrminusMSDN SetToStr (MDX)
SiblingstickMSDN Siblings (MDX)
StdevtickMSDN Stdev (MDX)
StdevPtickMSDN StdevP (MDX)
StripCalculatedMemberstickMSDN StripCalculatedMembers (MDX)
StrToMemberplusMSDN StrToMember (MDX), acts as the CONSTRAINED keyword was provided
StrToSetminusMSDN StrToSet (MDX)
StrToTupleminusMSDN StrToTuple (MDX)
StrToValuecrossMSDN StrToValue (MDX)
SubsettickMSDN Subset (MDX)
SumtickMSDN Sum (MDX)
TailplusMSDN Tail (MDX), one missing signature
ThiscrossMSDN This (MDX)
ToggleDrillStateminusMSDN ToggleDrillState (MDX)
TopCounttickMSDN TopCount (MDX)
TopPercenttickMSDN TopPercent (MDX)
TopSumtickMSDN TopSum (MDX)
TupleToStrminusMSDN TupleToStr (MDX)
UCasetickMSDN UCase (VBA)
UniontickMSDN Union (MDX)
UniqueNametickMSDN UniqueName (MDX)
UnknownMemberminusMSDN UnknownMember (MDX)
UnordertickMSDN Unorder (MDX)
UserNamecrossMSDN UserName (MDX)
ValidMeasurecrossMSDN ValidMeasure (MDX)
ValuetickMSDN Value (MDX)
VartickMSDN Var (MDX)
VariancetickMSDN Variance (MDX)
VariancePtickMSDN VarianceP (MDX)
VarPtickMSDN VaRP (MDX)
VisualTotalsplusVisualTotals function (MDX)
WeekdaytickMSDN Weekday (DAX)
WtdtickMSDN Wtd (MDX)
YeartickMSDN Year (DAX)
YtdtickMSDN 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:

Underlyingscontributors.COUNT
AllMember7
EUR4
JPY2
USD1

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:

Underlyingscontributors.COUNT
AllMember5
EUR4
USD1

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.