DrilldownLevel
Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set.
Syntax
DrilldownLevel(«Set»[, {«Level» | , «Index»}])
Returns the hierarchized members of a set, specified in «Set», one level below the lowest level represented in the set, or to one level below an optional level, either specified by reference in «Level» or by its zero-based index in «Index», of a member represented in the set.
Remarks
The members that result from the drill down are hierarchized to occur under their parents. Order is preserved among the original members in «Set».
If «Level» is specified and there is no member of «Level» represented in «Set», «Set» is returned.
When drilling down in sets of tuples, you can specify the dimension to be drilled down by its zero-based numeric position in the tuples instead of by level. For example, in the following set, a sample tuple is (Canada, Drink, [1998]):
(Crossjoin(Crossjoin([Store Country].members,[Product Family].members),
[Year].members)
The Store dimension is position 0, the Product dimension is position 1, and the Time dimension is position 2. The following expression drills down on the Product dimension:
DrilldownLevel(Crossjoin(Crossjoin([Store Country].members,
[Product Family].members),[Year].members),,1)
Example
The following example returns the set {USA, CA, <all cities in CA>, WA, <all cities in WA>, Canada}:
DrilldownLevel({[Customers Location].USA, [Customers Location].CA,
[Customers Location].WA, [Customers Location].Canada})
This example returns the same set as the previous example:
DrilldownLevel({[Customers Location].USA, [Customers Location].CA,
[Customers Location].WA, [Customers Location].Canada},
[Customers Location].[State Province])
The following example returns the set {USA, CA, WA, Canada, BC}, assuming that the [Country] level of the [Customers Location] dimension has an index of 1:
DrilldownLevel({[Customers Location].USA, [Customers Location].CA,
[Customers Location].WA, [Customers Location].Canada}, ,1)