List of Supported MDX Functions by ActivePivot
MDX Functions
The table provided below lists all existing MDX functions and details their support level in ActivePivot.
The following table details notations:
| Symbol | Meaning |
|---|---|
 | fully upported |
 | partially supported |
 | not supported |
Functions Deviating from MDX Standards
Aggregate Function
The aggregate function in ActivePivot 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. It means:
Aggregate(<Set Expression>)is supportedAggregate(<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 if you ask for
Aggregate({[Geography].[ALL].[AllMember].[France].[Paris], [Geography].[ALL].[AllMember].[France].[Paris]})
then 'Paris' will only be counted once.
Isolated Calculated Member
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
It 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
It looks for String within another. Returns the index (one based) at which the sub-string starts, zero if it is not
found.
Lookup is case-insensitive. It is possible to specify an offset (one based) at which the search should start.
Syntax
<Numeric Expression> <- InStr([<Numeric Expression> : offset,] <String Expression> : value, <String expression> : lookup)
- offset (optional): The offset at which 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 lookup.
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
It returns the sub-string of some string starting from the first character up to some 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 ActivePivot 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.
Since ActivePivot 5.8.2, calling Order(Order(set_expression)) performs a stable sort, which means that members that
are equal according to the first (outer) ordering will be sorted according to the second (inner) ordering. This
functionality does not work if any function is inserted between the calls to Order. For example,
Order(Filter(Order(set_expression))) will not perform a stable sort; results will be sorted only according to the
first (outer) ordering.
Before ActivePivot 5.8.2, nested calls to Order only sorts 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))
Order(Crossjoin(Order([City].Members, ASC, cityMeasure), [Currency].Members), DESC, currencyMeasure) // sorts by descending currencies, breaking ties using default hierarchical order (no stable order because calls to Order do not directly follow each other)
// Since 5.8.2: stable ordering is supported when calls to Order directly follow each other
Order(Order(Crossjoin([City].Members, [Currency].Members), ASC, cityMeasure), DESC, currencyMeasure) // sorts by descending currencyMeasure, breaking ties by ascending cityMeasure
// Before 5.8.2: stable ordering is not supported
Order(Order(Crossjoin([City].Members, [Currency].Members), ASC, cityMeasure), DESC, currencyMeasure) <=> Order(Crossjoin([City].Members, [Currency].Members), DESC, currencyMeasure)
Right Function
It returns the sub-string of some string starting from the last character going backwards until some 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 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 that are not measures will be granted (see ISubCubeProperties in Standard Context Values). As a consequence the members that are not contained in the VisualTotals will be 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 2 queries below will 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
ActivePivot also allows the use of several non-standard functions.
Is_DataMember
It 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] would be considered unknown because it has
N/A in its path.
Syntax
MemberExpression.Is_UnknownMember
ActiveMeasure
See the article about Active Measures and MDX compilation.