Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions - Page 5
March 6, 2006
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:
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:
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]),
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.
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.
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:
The Query pane appears, with our modifications circled, as depicted in Illustration 14.
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.
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.