DrillDownInSet
Returns the intersection between Set2 and the descendants of the member or
members of Set1.
Syntax 1
DrillDownInSet ("Set1", "Set2")
Syntax 2
DrillDownInSet("Member", "Set")
This returns a set of those descendants of Member which are in the Set as well. If a
descendant of Member is not 'visible' (the user has no
right to work with it) but there is a descendant in the Set, the resulting set contains the descendant. The same rule is used
recursively for other descendants.
The consequence of this definition is that tuples from the Set can contain only one element, meaning there are 1-tuples
in the Set.
DrillDownInSet("Set1", "Set2")
<=>
Generate("Set1", DrillDownInSet("Set1".CurrentMember, "Set2"), ALL)All duplicates are retained.
Examples
The examples are based on this structure for the REGIONS dimension:
- World
- North/South America
- North America
- South America
- Europe
- Northern Europe
- North-West Europe
- Central Europe
- Southern Europe
- South-East Europe
- North/South America
Example 1: Drill down from World to its children
This example returns:
- North/South America
- Europe
WITH
SET [__REGION_selection] AS '[REGIONS].[Level 2].members'
SELECT
DrilldownInSet([REGIONS].[World], [__REGION_selection]) ON ROWS,
{} ON COLUMNS
FROM [TOTSALES]Example 2: Drill down from World to its descendants on level 3
This example returns:
- North America
- South American
- Northern Europe
- North-West Europe
- Central Europe
- Southern Europe
- South-East Europe
WITH
SET [__REGION_selection] AS '[REGIONS].[Level 3].members'
SELECT
DrilldownInSet([REGIONS].[World], [__REGION_selection]) ON ROWS,
{} ON COLUMNS
FROM [TOTSALES]Drill down from World to its children with a defined calculated member
This example returns:
- North/South America
- Europe
- New Calculation
WITH
MEMBER [REGIONS].[World].[New Calculation] AS '0'
SET [__REGION_selection] AS 'AddCalculatedMembers([REGIONS].[Level 2].members)'
SELECT
DrilldownInSet([REGIONS].[World], [__REGION_selection]) ON ROWS,
{} ON COLUMNS
FROM [TOTSALES]