Calculated members are completed with the slicer content. Note, that hierarchies of the same dimension are also completed.
In our example, the slicer member [Geo].[France] is completing the [Economy] hierarchy members defined in the calculated member [sliced].
Query
WITH
MEMBER [Measures].[sliced] AS Sum( [Geography].[Economy].[Country].members, [Measures].[Amount] )
SELECT
{ [Measures].[amount], [Measures].[sliced] } on 0
FROM
[Sales]
WHERE
( [Geography].[Geo].[France] )
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[france] AS ( [Geography].[Economy].[France], [Measures].[Amount] )
SELECT
{ [Measures].[france], [Measures].[france] } on 0
FROM
[Sales]
Let´s take the value of the filter by using currentMember in a calculated member.
Query
WITH
MEMBER [Measures].[france] AS Sum( ( [Geography].[Economy].[Country].members, [Geography].[Geo].currentMember ), [Measures].[Amount] )
SELECT
{ [Measures].[france] } on 0
FROM
[Sales]
WHERE
( [Geography].[Geo].[France] )
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[france] AS ( [Geography].[Economy].[France], [Measures].[Amount] )
SELECT
{ [Measures].[france] } on 0
FROM
[Sales]
Another example of tuple completion and filtering.
In our example, the slicer filters the members function of axis 0 and applies to the calculated member [xx].
Query
WITH
MEMBER [Measures].[xx] AS Sum( [Geography].[Economy].members, [Measures].[Amount] )
SELECT
[Geography].[Economy].members on 0,
{ [Measures].[Amount], [Measures].[xx] } on 1
FROM
[Sales]
WHERE
( [Geography].[Geo].[France] )
Result
|
ALL |
EU |
France |
| Amount |
4 |
4 |
4 |
| xx |
12 |
12 |
12 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[axis-sliced-amount] AS ( [Geography].[Economy].[France], [Measures].[Amount] )
MEMBER [Measures].[calc-member-sliced-amount] AS ( [Geography].[Economy].[France], [Measures].[Amount] ) * (1+[Geography].[Economy].[France].level.ordinal)
SELECT
Hierarchize( Ascendants( [Geography].[Economy].[France] )) on 0,
{ [Measures].[axis-sliced-amount], [Measures].[calc-member-sliced-amount] } on 1
FROM
[Sales]
The set is being evaluated according to the current context (i.e., slicer).
Query
WITH
MEMBER [Measures].[xx] AS Count( [my-set] )
SET [my-set] AS [Geography].[Economy].members
SELECT
[Measures].[xx] on 0, [my-set] on 1
FROM
[Sales]
WHERE
( [Geography].[Geo].[France] )
Result
Assertion : Cell Equals
WITH MEMBER [Measures].[xx] AS Count( Ascendants( [Geography].[Economy].[France] ))
SELECT [Measures].[xx] ON 0 , Hierarchize( Ascendants( [Geography].[Economy].[France] )) on 1 FROM [Sales]
Within the axis evaluation, the dimension [Geography] members are filtered according to the slicer.
Query
SELECT
Head( [Time].[Calendar].[Month].members , Count( [Geography].[Economy].members )) on 0
FROM
[Sales]
WHERE
( [Geography].[Geo].[France] )
Result
| Jan 2010 |
Feb 2010 |
Mar 2010 |
|
|
|
Assertion : Cell Equals
SELECT
Head( [Time].[Calendar].[Month].members , Count( Ascendants( [Geography].[Economy].[France] ))) on 0
FROM
[Sales]
Similar to calculated member behavior : dimensions are overwritten by the actual slicer content.
In our example, [Economy].[All] is evaluated as the tuple ([Economy].[All],[Geo].[France]).
Query
SELECT
Iif( [Geography].[Economy].[All] = 4, [Time].[Calendar].[2010], [Time].[Calendar].[2011] ) on 0
-- ^
-- the value of [France]
FROM
[Sales]
WHERE
( [Geography].[Geo].[France] )
Result
Assertion : Cell Equals
SELECT ( [Time].[Calendar].[2010], [Geography].[Geo].[France] ) on 0
FROM [Sales]