DrilldownByLayer

The MDX DrilldownByLayer function for Essbase drills down members of a set that are at a specified layer.

Syntax

DrilldownByLayer ( set [, layer | index ] )

Parameters

set

The set in which the drilldown should occur.

layer

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:

Table 4-56 Output Grid from MDX Example

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