DrilldownLevel

Drills down to one level below a specified level number, or to one level below the lowest level in the input set.

Syntax

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.

Description

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.

Notes

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)

Example

Example 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

Example 2

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