Drills down members of a set that are at a specified layer.
Syntax
DrilldownByLayer ( set [, layer | index ] )
Parameter | Description |
---|---|
The set in which the drilldown should occur. | |
The layer of the members that should be drilled down. | |
index | A number of hierarchical steps representing the location of members that should be drilled down. |
Notes
This function 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.
Example
The following query
SELECT DrilldownByLayer ( {([Product],[California]), ([Product],[Oregon]), ([Product],[New York]), ([Product],[South]), ([Product],[Washington])}, [Market].[Region] ) ON COLUMNS FROM Sample.Basic
returns the grid:
Product | ||||||||
---|---|---|---|---|---|---|---|---|
California | Oregon | New York | South | Texas | Oklahoma | Louisiana | New Mexico | Washington |
12964 | 5062 | 8202 | 13238 | 6425 | 3491 | 2992 | 330 | 4641 |
TO use index, note that index is the index number of the dimension to drill down on. In the example below, the function drills down on Market. If you change the 1 to a 0, it drills down on Product.
SELECT DrilldownByLayer ( { ([Product],[East]), ([Product],[West]) }, 1 ) ON COLUMNS FROM Sample.Basic