Drilling by Level

DRILLDOWNLEVEL(<set1> [, <level>]])

To drill down by a level, the DRILLDOWNLEVEL function is used. This function drills down members in set1, at the level specified by level, to one level below.

For example, the call

DRILLDOWNLEVEL({USA, California, Washington, France, Canada, Ontario, 
   Quebec}, States)

returns the set

{USA, California, <all cities in California>, Washington, 
<all cities in Washington>, France, Canada, Ontario, 
<all cities in Ontario>, Quebec, <all cities in Quebec>}

Note that the members that result from the drill down are hierarchized to occur under their parents. Among the original members in set1, order is preserved. Thus, in the above example, France still occurs after USA and Canada still occurs after France.

If level is not specified, then it is assumed to be the level of the lowest level member of set1.

For example, the call

DRILLDOWNLEVEL({North America, USA, California, Washington, France, 
   Canada})

is equivalent to the call

DRILLDOWNLEVEL({North America, USA, California, Washington, France, 
   Canada}, States)

When there is no member at the specified level in set1, this call has no effect.

For example, the call

DRILLDOWNLEVEL({USA, California, Washington, France, Canada, Ontario, 
   Quebec}, Cities)

returns the original set unchanged.

For example, in a typical application, set1 is the set of members from a given dimension on a given axis. (Note that set1 may itself be the result of an expression.) The UI has a way to specify “drill down to level” — for example, by using a drop-down list box of levels. In response to a user selection, the application calls DRILLDOWNLEVEL with set1 and the level specified by the user.

The set returned by this function is described in the following algorithm:

1. If level is not specified, then let level be the level of the lowest level member in set1.
   
   
2. Let set2 = set1.
   
   
3. For each member m in set1:

      a. if m is at level, and

      b. if there are no descendants of m immediately following m,

   then set2 = UNION(set2, m.CHILDREN, ALL).

4. Return set2.

DRILLDOWNLEVELTOP(<set>, <index>[, [<level>]
  [, <numeric_value_expression>]])

The function DRILLDOWNLEVELTOP is a variation of DRILLDOWNLEVEL. Instead of including all children of a member at the specified level, it just includes the top n children.

For example, the call

DRILLDOWNLEVELTOP({USA, California, Washington, France, Canada, 
Ontario, Quebec}, 5, States, Sales)

returns the set

{USA, California, <top 5 cities in California based on sales>, 
Washington, <top 5 cities in Washington based on sales>, France, 
Canada, Ontario, <top 5 cities in Ontario based on sales>, Quebec, 
<top 5 cities in Quebec based on sales>}

As another example, the call

DRILLDOWNLEVEL({North America, USA, California, Washington, France, 
   Canada}, 5, States, Sales)

results in the set

{USA, California, <top 5 cities in California>, Washington, 
<top 5 cities in Washington>, France, Canada}

The function DRILLDOWNLEVELBOTTOM is similar, except that the bottom condition is applied instead of the top condition.

DRILLUPLEVEL(<set1>[, <level>])

To drill up by level, the DRILLUPLEVEL function is used.

This function drills up members in set1 that are below the specified level. If level is not specified, it is assumed to be one less than the level of the lowest level member in set1.

For example, the call

DRILLUPLEVEL({USA, California, <all cities in California>, Washington, 
<all cities in Washington>, France, Canada, Ontario, 
<all cities in Ontario>, Quebec, <all cities in Quebec>}, States)

returns the set

{USA, California, Washington, France, Canada, Ontario, Quebec}

If the level in the above call were changed from States to Countries, then the resulting set would be

{USA, France, Canada}

The set returned by this function is described by the following algorithm:

1. For each member m in set1, if m is below level, then remove m from set1.
   
   
2. Return set1.
   14. Note the following about DRILLDOWNLEVEL and DRILLUPLEVEL:
       1. Calling DRILLUPLEVEL without a level parameter on a set that has only the root level members has no effect.
          
          
       2. Calling DRILLDOWNLEVEL without a level parameter has no effect when the input set has all non-leaf members drilled down to their leaf level descendants.