Now that we have an idea what appears within the "drilled
down" Product Categories, let's focus on the use of the DrillDownLevelTop()
and DrillDownLevelBottom() functions, beginning with the initial request:
to perform drill down for Internet Order Quantity and Internet Sales
Amount, upon the Bikes, Clothing, and Accessories
Product Categories, returning the top selling two (2) Subcategories
for CY2003, ranking them upon Internet Sales Amount.
4.
Modify the top
line in the query (the commented line) to the following:
-- MDX041-002-1 DrillDownLevelTop - Initial
5.
Select File
-> Save MDX041-001 As..., name the
file MDX041-002-1, placing it with the previous query files, to protect
the former query.
6.
In the fifth
line from the top in the query, replace the DRILLDOWNLEVEL keyword with
the following:
DRILLDOWNLEVELTOP
7.
On the last
line of the AXIS(1) specification (appearing one line above the FROM
keyword), add a comma (",") after the right curly brace symbol ("}")
– that is, appearing to the immediate right of following:
[Product].[Product Categories].
[Category].[Accessories])
8.
Insert a
space, and then the following, after the comma just added:
2,
9.
Immediately
after typing the comma (",") to the right of the 2, press the Enter
key four times to push the remainder of the line down and insert a line between
it and the line from which it travels.
10.
Insert the
following syntax:
[Product].[Product Categories].
[Category],
between the following line into the open line we have created
and the ON AXIS(1) keywords, that is, between:
[Product].[Product Categories].
[Category].[Accessories]),
and
)ON AXIS(1)
The new line represents the Level
Expression we discussed in the Syntax section above. This is
the level to which we are specifying that we wish to drill down via DrillDownLevelTop().
11.
Immediately
after the newly added syntax, press the Enter key twice, to insert space
underneath it.
12.
Insert the
following syntax about midway in the newly created space:
[Measures].[Internet Sales Amount]
This insertion represents the Numeric
Expression, upon which the "top" Count of two (2) is
based. We therefore are specifying that we wish to return the two Subcategories
for each of the Bikes, Clothing, and Accessories Product
Categories, in terms of Internet Sales.
The Query pane appears, with our
modifications circled, as depicted in Illustration 12.
Illustration 12: Our Core Query, with Modifications
13.
Execute the
query by clicking the Execute button in the toolbar, as we did earlier.
The Results pane is populated by Analysis
Services, and the dataset shown in Illustration 13 appears.
Illustration 13: Results Dataset – DrillDownLevelTop() at
Work ...
We note that the three Product Categories are drilled
down, displaying under each the top two underlying Subcategories with
regard to Internet Sales Amount.
As we noted was the case with various components of the axis
definitions, together with the slicer, in Set Functions: The
DrillDownLevel() Function, the Count (here fixed at 2) – and
even the Level Expression (here specified as the Category level
of the Product hierarchy) and (as we will see in our next practice
example) Numeric Expression (here specified as the Internet Sales
Amount measure) – might be subjected to parameterization within a reporting
(or other) application. For that matter, we might even create a report
parameter whose setting determined the placement of the DrillDownLevelTop,
DrillDownLevelBottom, or the more generic DrillDownLevel keyword
to allow variable actions within the query underlying a given report dataset.
As with many other options that accrue to us when developing within the
Microsoft integrated BI solution, the clever combination of MDX functions and
parameterization can mean far-reaching capabilities with regard to manipulation
of a target report via our MDX queries.
14.
Select File
-> Save MDX041-002-1 to save the
query.
Let's look at a further example of the operation of DrillDownLevelTop().
The report developers and authors with which we are working have informed us
that they are pleased with the clarity of the example we have presented, and
state that they wish to see the same query in action, this time adjusted to use
the Internet Order Quantity as the basis upon which the "top two"
Subcategories are selected. We can manage this change with a simple
substitution of the Numeric Expression, currently represented by the Internet
Sales Amount measure.
15.
Modify the top
line in the query (the commented line) to the following:
-- MDX041-002-2 DrillDownLevelTop - New Basis Measure
16.
Select File
-> Save MDX041-002-1 As..., and name
the file MDX041-002-2, placing it with the previous query files, to
protect our former query.
17.
Replace the
following Numeric Expression (which we added in our last set of steps
above):
[Measures].[Internet Sales Amount])
(just above the line containing
the keywords ON AXIS(1)) with the following:
[Measures].[InternetOrder Quantity])
The Query pane appears, with our
modifications circled, as depicted in Illustration 14.
Illustration 14: The Modified Query ...
18.
Execute the
query by clicking the Execute button in the toolbar, as before.
The dataset
shown in Illustration 15 appears in the Results pane.
Illustration 15: Results Dataset – DrillDownLevelTop()
with Another Basis
We note that the three Product Categories are drilled
down, as before, displaying under each the top two underlying Subcategories.
This time, however, it is apparent that the "top" selections have
been made based upon the Internet Order Quantity measure.
19.
Select File
-> Save MDX041-002-2 to save the
query.
» 
Comment and Contribute
