A simple example defining a new measure (i.e., [New Value]) as a constant equal to 2.
Query
WITH
MEMBER [Measures].[New Value] AS 2
SELECT
{ [Geography].[Geo].[United States] } ON 0,
{ [Measures].[New Value] } ON 1
FROM
[Sales]
Result
|
United States |
| New Value |
2 |
A classical example showing how to calculate market shares.
Market Share X = Licenses sold of type X / Total number of licences sold
In the example, you can see how to combine in the same expression tuples with different dimensionality.
Query
WITH
MEMBER [Measures].[Market Share] AS ([Measures].[Amount]) / ( [Product].currentMember.parent ,[Measures].[Amount] ), FORMAT_STRING = "Percent"
SELECT
[Measures].[Market Share] ON 0,
[Product].[icCube].children ON 1
FROM
[Sales]
WHERE
[Geography].[Geo].[Europe]
Result
|
Market Share |
| Corporate |
56,47% |
| Partnership |
37,65% |
| Personal |
5,88% |
| Startup |
|
In this example, we show how to overwrite the current member in a calculated member.
Even though we are requesting the value for [United States] on the axis 0, the calculated member is using the value for [Switzerland] as the hierarchy member is redefined for the current tuple (in this example coming from a cell evaluation).
Query
WITH
MEMBER [Measures].[Switzerland Value] AS ( [Geography].[Geo].[Switzerland],[Measures].[Amount] )
SELECT
{ [Geography].[Geo].[Switzerland],[Geography].[Geo].[United States] } ON 0,
[Measures].[Switzerland Value] ON 1
FROM
[Sales]
Result
|
Switzerland |
United States |
| Switzerland Value |
248 |
248 |
Assertion : Cell Equals
SELECT
{ [Geography].[Geo].[Switzerland],[Geography].[Geo].[Switzerland] } ON 0,
[Measures].[Amount] ON 1
FROM
[Sales]
We can ask for the current member of a hierarchy and act in consequence.
In this example we define a measure which is changing the value only for [Switzerland].
Query
WITH
MEMBER [Measures].[Switzerland as 2] AS IIF([Geography].[Geo].currentMember is [Geography].[Geo].[Switzerland], 2, [Measures].[Amount] )
SELECT
{ [Geography].[Geo].[Switzerland], [Geography].[Geo].[United States] } ON 0,
[Measures].[Switzerland as 2] ON 1
FROM
[Sales]
Result
|
Switzerland |
United States |
| Switzerland as 2 |
2 |
768 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[2] AS 2
SELECT
{ [Measures].[2], [Measures].[Amount] } ON 0,
[Geography].[Geo].[United States] ON 1
FROM
[Sales]
Calculated measures are not limited to the [Measures] dimension. New members (hence the name calculated members) can be added into any existing hierarchy.
It's important to note that as for [Measures] the calculated members are not added into the original hierarchy structure. This implies that functions like Children() and Members() will not return calculated members.
Query
WITH
// Calculates the average over [Europe] countries, new member is added without level and parent
MEMBER [Geography].[Geo].[Europe Avg] AS Avg( [Geography].[Geo].[Europe].children )
// Calculates the max for all [Europe] countries, new member added as a child of a [Europe]
MEMBER [Geography].[Geo].[Europe].[Europe Max] AS Max( [Geography].[Geo].[Europe].children)
// Difference between [Europe] and [Switzerland], new member added as a child of a country
MEMBER [Geography].[Geo].[Europe].[Europe - Switzerland] AS [Geography].[Geo].[Europe] - [Geography].[Geo].[Switzerland]
SELECT
{ [Geography].[Geo].[Europe], [Geography].[Geo].[Europe Avg], [Geography].[Geo].[Europe Max], [Geography].[Geo].[Europe - Switzerland],[Geography].[Geo].[Europe].children} ON 0,
{ [Measures].[Amount] } ON 1
FROM
[Sales]
Result
|
Europe |
Europe Avg |
Europe Max |
Europe - Switzerland |
France |
Spain |
Switzerland |
| Amount |
255 |
85.0 |
248 |
7 |
4 |
3 |
248 |
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Europe Avg] AS Avg( [Geography].[Geo].[Europe].children )
MEMBER [Geography].[Geo].[Europe Max] AS Max( [Geography].[Geo].[Europe].children)
MEMBER [Geography].[Geo].[Europe - Switzerland] AS [Geography].[Geo].[Europe] - [Geography].[Geo].[Switzerland]
SELECT
{ [Geography].[Geo].[Europe], [Geography].[Geo].[Europe Avg], [Geography].[Geo].[Europe Max], [Geography].[Geo].[Europe - Switzerland],[Geography].[Geo].[Europe].children} ON 0,
{ [Measures].[Amount] } ON 1
FROM
[Sales]
The calculated member/measure being evaluated is the .CurrentMember().
Query
WITH
MEMBER [Measures].[Y] AS [Measures].CurrentMember.NAME
MEMBER [Measures].[X] AS [Measures].CurrentMember.NAME + '-' + [Measures].[Y]
SELECT
[Measures].[X] ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[X] AS "X-Y"
SELECT
[Measures].[X] ON 0
FROM
[Sales]
Overwriting an existing member is not possible. However it's possible to add a member with the same name in a different path.
In this example we are adding [Switzerland] in the root of the [Geo] hierarchy.
Query
WITH
MEMBER [Geography].[Geo].[Swiss-Lausanne] AS ([Geography].[Geo].[Switzerland] /* calculated member */)
MEMBER [Geography].[Geo].[Swiss] AS ([Geography].[Geo].[Europe].[Switzerland] /* hierarchy member */ )
MEMBER [Geography].[Geo].[Switzerland] AS ([Geography].[Geo].[Lausanne])
SELECT
{ [Geography].[Geo].[Switzerland],[Geography].[Geo].[Swiss], [Geography].[Geo].[Swiss-Lausanne] } ON 0
FROM
[Sales]
Result
| Switzerland |
Swiss |
Swiss-Lausanne |
| 56 |
248 |
56 |
Assertion : Cell Equals
SELECT
{ [Geography].[Geo].[Lausanne],[Geography].[Geo].[Europe].[Switzerland], [Geography].[Geo].[Lausanne] } ON 0
FROM
[Sales]
Overwriting an existing member is not possible (if same path).
Query
WITH
MEMBER [Geography].[Geo].[Europe].[Switzerland] AS ([Geography].[Geo].[Lausanne])
SELECT
{ [Geography].[Geo].[Switzerland] } ON 0
FROM
[Sales]
Result
Within the calculated measure the tuple is completed with hierarchies coming from the axis.
In our example, the [Economy] member coming from axis 1 is 'set' in the calculated member.
Query
WITH
MEMBER [Measures].[xx] AS ( [Geography].[Geo].[France], [Measures].[Amount] )
SELECT
[Measures].[xx] on 0,
{ [Geography].[Economy].[France], [Geography].[Economy].[Spain] } on 1
FROM
[Sales]
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[xx] AS ( [Geography].[Geo].[France], [Geography].[Economy].currentMember, [Measures].[Amount] )
SELECT
[Measures].[xx] on 0,
{ [Geography].[Economy].[France], [Geography].[Economy].[Spain] } on 1
FROM
[Sales]
Within the calculated measure the [Geo] hierarchy is completed by the current context.
In our example, the calculated member tuple ([Geography].[Geo].[France],[Measures].[Amount]) is completed with the member [Geography].[Economy].[Spain] coming from the axis.
Query
WITH
MEMBER [Measures].[xx] AS Iif( ( [Geography].[Geo].[France], [Measures].[Amount] ) = 4 , 'exist', 'no' )
SELECT
Iif( Generate( { [Geography].[Economy].[Spain] }, [Measures].[xx] ) = 'exist', [Time].[Calendar].[2010], [Time].[Calendar].[2011] ) on 0
FROM
[Sales]
Result
Assertion : MDX Equals
SELECT [Time].[Calendar].[2011] ON 0 FROM [Sales]
The calculated measures being evaluated is the .CurrentMember() so beware of infinite loop.
Here [X] as current member of the [Product].[Prod] hierarchy in its formula is creating an infinite loop.
Why is it a recursion ?
( [Product].[Prod].[X] )
is evaluated as ( [Measures].[Amount] )
is evaluated as ( [Measures].[Amount], [Product].[Prod].currentMember )
is evaluated as ( [Measures].[Amount], [Product].[Prod].[X] )
is evaluated as ( [Product].[Prod].[X] )
the latest one defines a recursion.
Query
WITH
MEMBER [Product].[Prod].[X] AS [Measures].[Amount]
SELECT
[Product].[Prod].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Product].[Company].[X] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( X)
The calculated measures being evaluated is the .CurrentMember() so beware of infinite loop.
Here [X] as current member of the [Product].[Prod] hierarchy in its formula is creating an infinite loop.
Query
WITH
MEMBER [Product].[Prod].[X] AS Avg( [Geography].[Geo].Members )
SELECT
[Product].[Prod].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Product].[Company].[X] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( X)
A calculated member defined outside any hierarchy is implicitly defined within the [Measures] dimension.
Query
WITH
MEMBER [the-answer] AS 42
MEMBER [the-answer-hierarchy] AS [the-answer].Hierarchy.Name
SELECT
{ [the-answer], [the-answer-hierarchy] } ON 0
FROM
[Sales]
Result
| the-answer |
the-answer-hierarchy |
| 42 |
Measures |
Assertion : MDX Equals
WITH
MEMBER [Measures].[the-answer] AS 42
MEMBER [Measures].[the-answer-hierarchy] AS "Measures"
SELECT
{ [Measures].[the-answer], [Measures].[the-answer-hierarchy] } ON 0
FROM
[Sales]
A calculated member can be defined directly into a level of depth zero.
Query
WITH MEMBER [Product].[Prod].[Company].[New Company] AS 42
SELECT FROM [Sales] WHERE [Product].[Prod].[Company].[New Company]
Result
Assertion : Cell Equals
WITH MEMBER [Expected Result] AS 42
SELECT FROM [Sales] WHERE [Expected Result]
A calculated member cannot be defined into a level of depth greater than zero without
specifying any parent in the previous level.
Query
WITH MEMBER [Product].[Prod].[Article].[OEM] AS 42
SELECT FROM [Sales] WHERE [Product].[Prod].[Article].[OEM]
Result
Error Code : OLAP_CALC_MEMBER_ORPHAN
Error Message : cannot attach the calculated member 'OEM' into the level '[Product].[Prod].[Article]' without defining any parent (hint: attach the calculated member to a member of the previous level)
Recursion is supported but beware of infinite loop.
Query
WITH
MEMBER [Product].[Prod].[X] AS [Product].[Prod].[X]
SELECT
[Product].[Prod].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Product].[Company].[X] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( X)
Recursion is supported but beware of infinite loop.
Query
WITH
MEMBER [Product].[Prod].[X] AS 1 + [Product].[Prod].[X]
SELECT
[Product].[Prod].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Product].[Company].[X] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( X)
Recursion is supported but beware of infinite loop.
Query
WITH
MEMBER [Measures].[Z] AS [Measures].[X]
MEMBER [Measures].[Y] AS [Measures].[Z]
MEMBER [Measures].[X] AS [Measures].[Y]
SELECT
[Measures].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Measures].[Y] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( Y)