Drilling by Level

OLE DB Programmer's Reference

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 following set:

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

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

If <level> is not specified, 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 following:

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. Therefore, 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. (<set1> may itself be the result of an expression.) The user interface 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, let <level> be the level of the lowest level member in <set1>.
2. Let <set2> = <set1>.
3. For each member m in <set1>:
   • If m is at <level>, and
   • If there are no descendants of m immediately following m,
   • Then <set2> = UNION(<set2>, m.CHILDREN, [ALL]).
4. Return <set2>.

DRILLDOWNLEVEL can be used in unbalanced hierarchies, where the concept of a level does not exist, by using DRILLDOWNLEVEL(<set>). The members selected for drill-down are those with the greatest distance from the root of the hierarchy.

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 includes only the top n children.

For example, the call

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

returns the following 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 following set:

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

To use DRILLDOWNLEVELTOP with unbalanced hierarchies, use the form DRILLDOWNLEVELTOP(<set>, <index> [, <numeric_value_expression>]). The members selected for drill-down are those with the greatest distance from the root of the hierarchy.

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 following set:

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

If the <level> in the above call were changed from States to Countries, the resulting set would be the following:

{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>, remove m from <set1>.
2. Return <set1>.

Note the following about DRILLDOWNLEVEL and DRILLUPLEVEL:

• Calling DRILLUPLEVEL without a level parameter, on a set that has only the root level members, has no effect.
• Calling DRILLDOWNLEVEL without a level parameter has no effect when the input set has all nonleaf members drilled down to their leaf-level descendants.

1998-2001 Microsoft Corporation. All rights reserved.