DrilldownMember

The MDX DrilldownMember function for Essbase drills down on any members or tuples of set1 that are also found in set2. The resulting set contains the drilled-down members or tuples, as well as the original members or tuples (whether they were expanded or not).

Syntax

DrilldownMember( set1, set2 [, RECURSIVE] )

Parameters

set1: The set containing members or tuples to drill down on if comparison with set2 tests positive for identical members or tuples.
set2: The set to compare with set1 before drilling down on members or tuples in set1.
RECURSIVE: Optional. A keyword to enable repeated comparisons of the sets.

Notes

This function drills down on all members of set1 that are also found in set2. The two sets are compared. Then the members or tuples of the first set that are also present in the second set are expanded to include their children.

If the first set is a list of tuples, then any tuples in the first set that contain members from the second set are expanded to their children, generating more tuples.

If the RECURSIVE keyword is used, multiple passes are made on the expanded result sets. Drilldownmember repeats the set comparison and resulting drilldown until there are no more unexpanded members or tuples of set1 that are also present in set2.

Example

Drilling Down on Members

The following examples drill down on members.

Example 1

Example 2

The following expression

DrilldownMember({Market, [New York]}, {Market, West}, RECURSIVE)

returns the set:

{Market, East, West, California, Oregon, Washington, Utah, Nevada, South,
 Central, [New York]}

The member Market is drilled down and then the West member of the resulting set is drilled down, because the RECURSIVE parameter was specified.

Drilling Down on Tuples

This example uses the following part of the Sample Basic outline:

Expanded Product dimension with children 100, 200, etc, and children of 100: 100-10, 100-20, and 100-30.

The following example drills down on tuples.

The following expression

DrilldownMember
 ( {([100],[California]), ([200],[Washington])},
   { [100] }
 )

returns the set of tuples:

{ ([100],California), ([100-10],California), ([100-20],California),
 ([100-30],California), ([200],Washington)}

Therefore, the following query

SELECT
DrilldownMember
 ( {([100],[California]), ([200],[Washington])},
   { [100] }
 )
ON COLUMNS
FROM Sample.Basic

returns the grid:

Table 4-57 Output Grid from MDX Example

100	100-10	100-20	100-30	200
California	California	California	California	Washington
999	3498	-1587	-912	1091