Drills down to one level below a specified level number, or to one level below the lowest level in the input set.
DrilldownLevel ( set [, { layer | index ])
set | The set in which the drilldown should occur. |
layer | The level of the members that should be drilled down. |
index | A number of hierarchical steps representing the location of members that should be drilled down. |
Returns the members of set to one level below the optionally specified layer (or index number of the level). If layer (or index) is omitted, the lowest level of set is returned. Members are returned in their hierarchical order as represented in the database outline.
If set consists of tuples from more than one dimension, you can specify the dimension to be drilled down by its zero-based numeric position in the tuples. The default value for the index is zero.
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 layer is specified and there is no member of layer represented in set, set is returned.
When drilling down on sets of tuples, instead of specifying by level, you can specify the dimension to be drilled down by its zero-based numeric position in the tuples.
For example, in the following set, a sample tuple is (East, Colas, [Qtr1]):
(Crossjoin(Crossjoin([Market].Children,[Product].Children), [Year].Children))
The Market dimension is position 0, the Product dimension is position 1, and the Time dimension is position 2.
Given the dimension positions mentioned above, the following expression drills down on the Product dimension (represented by position 1):
DrilldownLevel(Crossjoin(Crossjoin([Market].Children,[Product].Children), [Year].Children), 1)
The following example
DrilldownLevel( {Market, West})
returns the set:
{ Market, West, California, Oregon, Washington, Utah, Nevada }
after drilling down West, which is the lowest level represented in the input set.
Therefore, the following query
Select DrilldownLevel( {Market, West}) on columns from sample.basic
returns the grid:
Market | West | California | Oregon | Washington | Utah | Nevada |
---|---|---|---|---|---|---|
105522 | 29861 | 12964 | 5062 | 4641 | 3155 | 4039 |
The following example
DRILLDOWNLEVEL ( {([Product],[California]), ([Product],[Oregon]), ([Product],[New York]), ([Product],[South]), ([Product],[Washington])}, [Market].[Region] )
returns the set:
{ ([Product], California), ([Product], California), ([Product], [New York]), ([Product], South), ([Product], Texas), ([Product], Oklahoma), ([Product], Louisiana), ([Product], [New Mexico]), ([Product], Washington)}
Therefore, the following query
Select DRILLDOWNLEVEL ( {([Product],[California]), ([Product],[Oregon]), ([Product],[New York]), ([Product],[South]), ([Product],[Washington])}, [Market].[Region] ) on columns from sample.basic
returns the grid:
Product | Product | Product | Product | Product | Product | Product | Product | Product |
---|---|---|---|---|---|---|---|---|
California | Oregon | New York | South | Texas | Oklahoma | Louisiana | New Mexico | Washington |
12964 | 5062 | 8202 | 13238 | 6425 | 3491 | 2992 | 330 | 4641 |
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |