Drilling by Member

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

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

Drills down members in set1 that are present in set2. If RECURSIVE is not specified, then each member of set2 is checked against the original set1 and if it is present it is drilled down. If RECURSIVE is specified, then 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 since it does not occur in set1. Note that 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 shown here

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 if 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 if 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 if 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.

    Note that this is sensitive to the order of the members in set2. If set2 were {Washington, USA, Canada}, then the result would not contain all the cities in Washington. This is because set1 is checked to see if it contains “Washington” first. It does not at this stage because USA has not been drilled down yet.

    A typical usage scenario for an application is: 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 user clicks the plus (+) sign next to a member m of set1 in the UI. The application now calls DRILLDOWNMEMBER(set1, {m}) to drill down on m. More sophisticated UIs 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:

  4. Let set3 equal set1, the empty set.

  5. In the FOR statement, let X = set3 if RECURSIVE and let X = set1 if not.

  6. For each member m of set2, and for each occurrence of m in the set X:

    a. Let p be the member immediately after m in set X.

    b. If p is a descendant of m, then break.

    c. Otherwise, let set3 = HIERARCHIZE(Union(set3, m.CHILDREN))

  7. 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 just includes the top n children (or descendants). The syntax of this function is:

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 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 set (assuming the top 5 states in USA were California, Texas, Florida, Georgia, and Washigton)

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

DRILLUPMEMBER(<set1>, <set2>)

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 set

{USA, France, Canada}

Note that 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 set

{USA, France, Canada}

As a result, all descendants of USA — California, Los Angeles, Washington, Seattle, 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 the 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:

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

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), then DRILLUPMEMBER(set1, {m}) is applied. If it is drilled up (that is, there is no descendant of m that immediately follows m), then DRILLDOWNMEMBER(set1, {m}[, RECURSIVE]) is applied to set1. The optional RECURSIVE flag is used if TOGGLEDRILLSTATE was called with RECURSIVE.