| Where Set Clause Exists | (standard MDX) |
More about the slicer as a set of tuples : i.e., behaviors related to tuple exists mechanism.
Description
This section highlights several usage of the slicer related to tuple completion and exists mechanisms (i.e., tuple with several members of the same dimension).
See Also
Where Clause Where Clause Exists
Examples
Calculated members are completed with the slicer content. Note, that hierarchies of the same dimension are also completed.
In our example, the slicer members [Geo].[France] and [United States] are completing the [Economy] hierarchy members 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]),([Geography].[Geo].[United States]) }
Result
| Amount | sliced |
| 772 | 772 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[france+us] AS ( [Geography].[Economy].[France], [Measures].[Amount] ) + ( [Geography].[Economy].[United States], [Measures].[Amount] )
SELECT
{ [Measures].[france+us], [Measures].[france+us] } on 0
FROM
[Sales]
Similar as the former example but using a set of tuples instead of a set of members.
Query
WITH
MEMBER [Measures].[sliced] AS ( [Geography].[Economy].defaultMember, [Measures].[Amount] )
SELECT
{ [Measures].[amount], [Measures].[sliced] } on 0
FROM
[Sales]
WHERE
{ ( [Geography].[Geo].[France],[Product].[Prod].[Personal] ), ( [Geography].[Geo].[Switzerland],[Product].[Prod].[Corporate] ) }
Result
| Amount | sliced |
| 148 | 148 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[france+switzerland] AS ( [Geography].[Economy].[France],[Product].[Prod].[Personal], [Measures].[Amount] ) + ( [Geography].[Economy].[Switzerland],[Product].[Prod].[Corporate], [Measures].[Amount] )
SELECT
{ [Measures].[france+switzerland], [Measures].[france+switzerland] } on 0
FROM
[Sales]
As the slicer is defining several countries, currentMember called by the calculated member is failing.
Query
WITH
MEMBER [Measures].[france+spain] AS Sum( ( [Geography].[Economy].[Country].members, [Geography].[Geo].currentMember ), [Measures].[Amount] )
SELECT
{ [Measures].[france+spain] } on 0
FROM
[Sales]
WHERE
{ ([Geography].[Geo].[France]),([Geography].[Geo].[Spain]) }
Result
| france+spain |
| #ERROR : (OLAP_SLICER_SET_CURRENT_MEMBER) |
Error Code : OLAP_SLICER_SET_CURRENT_MEMBER
Error Message : more than one current member (from the slicer) for hierarchy '[Geography].[Geo]'
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].[Country].members, [Measures].[Amount] )
SELECT
[Geography].[Economy].members on 0,
{ [Measures].[Amount], [Measures].[xx] } on 1
FROM
[Sales]
WHERE
{ ( [Geography].[Geo].[France] ), ( [Geography].[Geo].[Spain] ) }
Result
| ALL | EU | France | Spain | |
| Amount | 7 | 7 | 4 | 3 |
| xx | 7 | 7 | 7 | 7 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[sliced-amount] AS
Iif( [Geography].[Economy].currentMember.level IS [Geography].[Economy].[Country],
( [Geography].[Economy].currentMember, [Measures].[Amount] ) ,
( [Geography].[Economy].[France], [Measures].[Amount] ) + ( [Geography].[Economy].[Spain], [Measures].[Amount] )
)
MEMBER [Measures].[xx] AS ( [Geography].[Economy].[Spain], [Measures].[Amount] ) + ( [Geography].[Economy].[France], [Measures].[Amount] )
SELECT
Hierarchize( Ascendants( [Geography].[Economy].[France] ) + Ascendants( [Geography].[Economy].[Spain] )) on 0,
{ [Measures].[sliced-amount], [Measures].[xx] } on 1
FROM
[Sales]
The set is evaluated according to the slicer content.
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]), ([Geography].[Geo].[Spain]) }
Result
| xx | |
| ALL | 4 |
| EU | 4 |
| France | 4 |
| Spain | 4 |
Assertion : Cell Equals
WITH MEMBER [Measures].[xx] AS Count( Ascendants( [Geography].[Economy].[France]) + Ascendants( [Geography].[Economy].[Spain]) ) SELECT [Measures].[xx] ON 0 , Hierarchize( Ascendants( [Geography].[Economy].[France]) + Ascendants( [Geography].[Economy].[Spain]) ) on 1 FROM [Sales]
Within the axis evaluation, the dimension [Geography] members are filtered according to the slicer content.
Query
SELECT
Head( [Time].[Calendar].[Month].members , Count( [Geography].[Economy].members )) on 0
FROM
[Sales]
WHERE
{ ([Geography].[Geo].[France]), ([Geography].[Geo].[Spain]) }
Result
| Jan 2010 | Feb 2010 | Mar 2010 | Apr 2010 |
Assertion : Cell Equals
SELECT
Head( [Time].[Calendar].[Month].members , Count( Ascendants( [Geography].[Economy].[France] ) + Ascendants( [Geography].[Economy].[Spain]) )) 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]+[Geo].[Spain]).
Query
SELECT
Iif( [Geography].[Economy].[All] = 7, [Time].[Calendar].[2010], [Time].[Calendar].[2011] ) on 0
-- ^
-- the value of [France] + [Spain]
FROM
[Sales]
WHERE
{ ([Geography].[Geo].[France]), ([Geography].[Geo].[Spain]) }
Result
| 2010 |
| 7 |
Assertion : Cell Equals
WITH MEMBER [france+spain 2010] AS ([Time].[Calendar].[2010], [Geography].[Geo].[France],[Measures].[Amount]) + ([Time].[Calendar].[2010], [Geography].[Geo].[Spain],[Measures].[Amount]) SELECT [france+spain 2010] on 0 FROM [Sales]
XMLA Properties
Find here the value of the XMLA properties when not specified otherwise.