MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions


 

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 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>:
  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 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}

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: