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

Drilling by Member

Note   All functions in this section require that the sets on which they operate be formed from a single dimension. The set arguments to these functions cannot be the result of a CROSSJOIN operation. This includes the following:

To drill down by a member, use the DRILLDOWNMEMBER function.

DRILLDOWNMEMBER(<set1>, <set2>[, RECURSIVE])

This expression drills down members in <set1> that are present in <set2>. If RECURSIVE is not specified, each member of <set2> is checked against the original <set1> and if a member is present, it is drilled down. If RECURSIVE is specified, each member of <set2> is checked against the result of the previous application of a member from <set2>. Typically, <set2> is a subset of <set1> in the invocation of this function.

For example, the call

   DRILLDOWNMEMBER({USA, France, Canada}, {USA, Washington, Canada})

returns the set

   {USA, <[all] states in USA>, France, Canada, <[all] provinces in 
   Canada>}

The member Washington from <set2> had no effect because it does not occur in <set1>. The members that result from the drill down are hierarchized to occur under their parents.

If the optional RECURSIVE flag is used in the above call, as in the statement

   DRILLDOWNMEMBER({USA, France, Canada}, {USA, Washington, Canada}, 
      RECURSIVE)

then the following set is returned:

   {USA, <[all] states USA before Washington>, Washington, 
   <[all] cities in Washington>, <[all] states in USA after 
   Washington>, France, Canada, <[all] provinces in Canada>}

This is because the following process takes place:

1. The member USA from <set2> is inspected to see whether it is present in <set1>. It is present; therefore, it is drilled down to yield the set that has USA, all states in USA, France, and Canada. Call this set <set1 result1>.
   
   
2. The second member of <set2>, Washington, is inspected to see whether it is present in <set1 result1>. It is; therefore, Washington is drilled down to yield all cities in Washington, and a union is formed with the result and <set1 result2>.
   
   
3. The third member of <set2>, Canada, is inspected to see whether it is present in <set1 result2>. It is; therefore, it is drilled down to get all the provinces in Canada, and a union is formed with this result and <set1 result2> to get the final set.

This process is sensitive to the order of the members in <set2>. If <set2> were {Washington, USA, Canada}, the result would not contain all the cities in Washington. This is because <set1> is checked to see whether it contains "Washington" first. At this stage, it does not, because USA has not been drilled down yet.

A typical usage scenario for an application is the following: <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 clicks the plus (+) sign next to a member m of <set1> in the user interface. The application now calls DRILLDOWNMEMBER(<set1>, {m}) to drill down on m. More sophisticated user interfaces can expose options for recursive drill downs, drill downs on multiple members, and so on.

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

1. Let <set3> equal <set1>, the empty set.
   
   
2. In the FOR statement, let X = <set3> if RECURSIVE and let X = <set1> if not.
   
   
3. For each member m of <set2> and for each occurrence of m in the set X:
   • Let p be the member immediately after m in set X.
     
     
   • If p is a descendant of m, then break.
     
     
   • Otherwise, let <set3> = HIERARCHIZE(Union(<set3>, m.CHILDREN)).
4. Return <set3>.

In the above algorithm, the BREAK command pops out of the IF clause and begins another iteration.

The DRILLDOWNMEMBERTOP function is a variation of DRILLDOWNMEMBER. Instead of including all children (or descendants) of a member, it includes only the top n children (or descendants). The syntax of this function is as follows:

DRILLDOWNMEMBERTOP(<set1>, <set2>, <index> [, [<numeric_value_expression>][, RECURSIVE]])

The desired number of children (or descendants) is specified by <index>, and <numeric_value_expression> gives the criterion. For example, the call

   DRILLDOWNMEMBERTOP({USA, France, Canada}, {USA, Washington, Canada},
      5, Sales)

returns the following set:

   {USA, <top 5 states in USA based on Sales>, France, Canada, 
   <top 5 provinces in Canada based on Sales>}

Use of the RECURSIVE flag has an effect similar to its effect in DRILLDOWNMEMBER.

For example, the call

   DRILLDOWNMEMBERTOP({USA, France, Canada}, {USA, Canada}, 5, Sales,
      RECURSIVE)

returns the following set (assuming the top 5 states in USA were California, Texas, Florida, Georgia, and Washington):

   {USA, California, Texas, Florida, Georgia, Washington, 
   <top 5 cities in Washington based on sales>, France, Canada, 
   <top 5 provinces in Canada based on Sales>}

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

To drill up by a member, use the DRILLUPMEMBER function. The syntax is as follows:

DRILLUPMEMBER(<set1>, <set2>)

DRILLUPMEMBER drills up the members in <set1> that are present in <set2>. As in the case of DRILLDOWNMEMBER, <set2> is usually a subset of <set1> in the invocation of this function.

For example, the call

   DRILLUPMEMBER({USA, Alabama, Alaska, France, Canada, Ontario, Quebec}, 
      {USA, Canada})

returns the following set:

   {USA, France, Canada}

DRILLUPMEMBER does not have a RECURSIVE flag. This is because when a member is drilled up, it is common to have all the descendants (not just the immediate children) removed. In other words, drilling up is usually recursive. Therefore, the call

   DRILLUPMEMBER({USA, California, Los Angles, Washington, Seattle, 
      King County, France, Canada}, {USA})

returns the following set:

   {USA, France, Canada}

As a result, all descendants of USA—California, Los Angeles, Washington, Seattle, and King County—have been drilled up, even though only California and Washington are the direct children of USA.

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

1. For each member m of <set2> and for each occurrence of m in <set1>, remove from <set1> all descendants of m that directly follow m.
   
   
2. Return <set1>.

The function TOGGLEDRILLSTATE is a combination of DRILLDOWNMEMBER and DRILLUPMEMBER. The syntax of this function is as follows:

TOGGLEDRILLSTATE(<set1>, <set2>[, RECURSIVE])

TOGGLEDRILLSTATE toggles the drill state of each member of <set2> that is present in <set1>. If a member m of <set2> that is present in <set1> is drilled down (that is, has a descendant), DRILLUPMEMBER(<set1>, {m}) is applied. If it is drilled up (that is, there is no descendant of m that immediately follows m), DRILLDOWNMEMBER(<set1>, {m}[, RECURSIVE]) is applied to <set1>. The optional RECURSIVE flag is used if TOGGLEDRILLSTATE was called with RECURSIVE.