List of Supported MDX Functions by ActivePivot
MDX Functions
This table lists all existing MDX functions and details their support level in ActivePivot.
The following notations are used:
| Symbol | Meaning |
|---|---|
 | fully upported |
 | partially supported |
 | not supported |
 | will not be 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:
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 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 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.
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
ActivePivot 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.