Drilling by Member

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

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

The member Washington from <set2> has 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)

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

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 causes another iteration to begin as it breaks out of the IF clause.

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 five 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. That is, drilling up is usually recursive. Therefore, the call

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

returns the following set:

{USA, France, Canada}

As a result, all descendants of USA ? California, LA, 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.