SubCubeOthers (MDX+)
Creates an 'Others' category member
Parameters
Return
void
Description
Currently only available for categories, the SubCube functions allows for filtering facts using different operations.
SubCubeOthers() without parameters allows for creating a category member based on the siblings that are not "others". It is equivalent of the selection of the parent minus the union of the siblings:
SubCubeMinus( parentMember, SubCubeUnion( {siblings} ) )
If the parent member is the all member, it is equivalent to:
SubCubeComplement( SubCubeUnion( {siblings} ) )
E.g. assuming the following hierarchy:
Countries
United States
Switzerland
Others as SubCubeOther()
The "Others" will be [Countries] - [United States] - [Switzerland]
With a parameter it is a shortcut of:
SubCubeMinus( parentMember, SubCubeUnion( {parameter tuples} ) )
See Also
SubCubeComplement SubCubeIntersect SubCubeMinus SubCubeSymDifference SubCubeUnion
Examples
SubCubeOthers() without parameters takes all siblings excluding members that are 'Others'.
In this example, it is a shortcut for SubCubeComplement( SubCubeUnion(France,Spain) ).
Query
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[Others] AS SubCubeOthers()
CATEGORY MEMBER [Stats].[Ex].[All-M].[France] AS [Geography].[Geo].[France]
CATEGORY MEMBER [Stats].[Ex].[All-M].[Spain] AS [Geography].[Geo].[Spain]
SELECT
[Stats].[Ex].members on 0
FROM [Sales]
Result
| All-M |
Others |
France |
Spain |
| 1023 |
1016 |
4 |
3 |
Assertion : Cell Equals
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[Others] AS SubCubeComplement( SubCubeUnion([Geography].[Geo].[France],[Geography].[Geo].[Spain]) )
CATEGORY MEMBER [Stats].[Ex].[All-M].[France] AS [Geography].[Geo].[France]
CATEGORY MEMBER [Stats].[Ex].[All-M].[Spain] AS [Geography].[Geo].[Spain]
SELECT
[Stats].[Ex].members on 0
FROM [Sales]
In this example we're going to build a more complex hierarchy.
For other categories not attached
Query
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[Europe] AS [Geography].[Geo].[Europe]
CATEGORY MEMBER [Stats].[Ex].[Europe].[Spain] AS [Geography].[Geo].[Spain]
CATEGORY MEMBER [Stats].[Ex].[Europe].[Switzerland] AS [Geography].[Geo].[Switzerland]
CATEGORY MEMBER [Stats].[Ex].[Europe].[Others] AS SubCubeOthers()
CATEGORY MEMBER [Stats].[Ex].[All-M].[America] AS [Geography].[Geo].[America]
CATEGORY MEMBER [Stats].[Ex].[America].[US] AS [Geography].[Geo].[United States]
CATEGORY MEMBER [Stats].[Ex].[America].[Others] AS SubCubeOthers()
CATEGORY MEMBER [Stats].[Ex].[All-M].[Others Continent] AS SubCubeOthers()
SELECT
[Measures].[Amount] on 0,
[Stats].[Ex].members on 1
FROM [Sales]
Result
|
Amount |
| All-M |
1023 |
| Europe |
255 |
| Spain |
3 |
| Switzerland |
248 |
| Others |
4 |
| America |
768 |
| US |
768 |
| Others |
|
| Others Continent |
|
Assertion : Cell Equals
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[Europe] AS [Geography].[Geo].[Europe]
CATEGORY MEMBER [Stats].[Ex].[Europe].[Spain] AS [Geography].[Geo].[Spain]
CATEGORY MEMBER [Stats].[Ex].[Europe].[Switzerland] AS [Geography].[Geo].[Switzerland]
CATEGORY MEMBER [Stats].[Ex].[Europe].[Others] AS SubCubeMinus([Geography].[Geo].[Europe], SubCubeUnion([Geography].[Geo].[Spain],[Geography].[Geo].[Switzerland]) )
CATEGORY MEMBER [Stats].[Ex].[All-M].[America] AS [Geography].[Geo].[America]
CATEGORY MEMBER [Stats].[Ex].[America].[US] AS [Geography].[Geo].[United States]
CATEGORY MEMBER [Stats].[Ex].[America].[Others] AS SubCubeMinus( [Geography].[Geo].[America],[Geography].[Geo].[United States])
CATEGORY MEMBER [Stats].[Ex].[All-M].[Others Continent] AS SubCubeComplement( {[Geography].[Geo].[Europe],[Geography].[Geo].[America]} )
SELECT
[Measures].[Amount] on 0,
[Stats].[Ex].members on 1
FROM [Sales]
SubCubeOthers() with a list of parameters behaves as SubCubeComplement().
In our example, we select all except [France].
Query
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[All but France] AS SubCubeOthers([Geography].[Geo].[France] )
SELECT
[Stats].[Ex].[All-M].[All but France] on 0
FROM [Sales]
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[[All but France] AS ([Measures].[Amount],[Geography].[Geo].[All])- ([Measures].[Amount],[Geography].[Geo].[France])
SELECT
[Measures].[[All but France] on 0
FROM [Sales]
Assertion : Cell Equals
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[All but France] AS SubCubeComplement( [Geography].[Geo].[France] )
SELECT
[Stats].[Ex].[All-M].[All but France] on 0
FROM [Sales]
SubCubeOthers() with a list of parameters behaves as SubCubeComplement().
In our example, we select all except [France].
Query
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[Europe] AS [Geography].[Geo].[Europe]
CATEGORY MEMBER [Stats].[Ex].[Europe].[Europe but France] AS SubCubeOthers([Geography].[Geo].[France])
SELECT
[Stats].[Ex].[Europe].[Europe but France] on 0
FROM [Sales]
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[[Europe but France] AS ([Measures].[Amount],[Geography].[Geo].[Europe])- ([Measures].[Amount],[Geography].[Geo].[France])
SELECT
[Measures].[[Europe but France] on 0
FROM [Sales]
Assertion : Cell Equals
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[All but France] AS SubCubeMinus([Geography].[Geo].[Europe], [Geography].[Geo].[France] )
SELECT
[Stats].[Ex].[All-M].[All but France] on 0
FROM [Sales]
SubCubeOthers() with a list of parameters behaves as SubCubeComplement().
In our example, we select all except [France] and [Spain].
Query
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[All but France/Spain] AS SubCubeOthers( [Geography].[Geo].[France], [Geography].[Geo].[Spain] )
SELECT
[Stats].[Ex].[All-M].[All but France/Spain] on 0
FROM [Sales]
Result
| All but France/Spain |
| 1016 |
Assertion : Cell Equals
WITH
CATEGORY HIERARCHY [Stats].[Ex]
CATEGORY MEMBER [Stats].[Ex].[All-M].[All but France] AS SubCubeOthers( {[Geography].[Geo].[France], [Geography].[Geo].[Spain]} )
SELECT
[Stats].[Ex].[All-M].[All but France] on 0
FROM [Sales]
Assertion : Cell Equals
WITH
MEMBER [Measures].[[All but France/Spain] AS ([Measures].[Amount],[Geography].[Geo].[All])- ([Measures].[Amount],[Geography].[Geo].[France]) - ([Measures].[Amount],[Geography].[Geo].[Spain])
SELECT
[Measures].[[All but France/Spain] on 0
FROM [Sales]
Since Version
5.0
XMLA Properties
Find here the value of the XMLA properties when not specified otherwise.