Figure 1 shows the drillthrough operation in action. In this PivotTable, I included the Cities level of the Customers dimension on the rows and the Year level (from the Time dimension) and the Product Family level (from the Product dimension) on the columns. I've selected the C7 cell, and I'm about to select the Drill to details menu option (which the VBA macro added) to perform a drillthrough. When I select the drillthrough option, the VBA macro will generate the query that Listing 1 shows.
You might notice two anomalies in Listing 1. First, the query has the numbers 0, 1, and 2 for axis names instead of the names COLUMNS and ROWS. The numbers are a shorthand notation for the axis names and are easier to generate programmatically. Second, Berkeley, 1997, and Drink are all represented in the drillthrough query, but they don't appear on the same axes as they do in the PivotTable. For example, the PivotTable shows Berkeley on the rows, but the drillthrough query lists Berkeley on columns (axis 0). This switch is OK because the axis layout of the drillthrough query doesn't affect the rowset result. Only the list of dimension members determines what rows the DRILLTHROUGH command returns.
Listing 2 shows the VBA code you down-loaded that creates the drillthrough query. The CreateDrillMdx() function uses a Pivot-Table data cell (oPTCell) to determine which dimension members you need to create the DRILLTHROUGH statement. You can identify the dimension members by stepping through the row and column headers that match oPTCell. The drillthrough query doesn't use all the header cells; it uses only one member from each dimension. The dimension member that the query uses is the lowest member (hierarchically) in that dimension. Cell A7, which references California (CA), is an example of an unused header cell. The query doesn't reference CA because Berkeley is from the same dimension and is lower in the dimension hierarchy than CA. I included CA only to give context to Berkeley, and the numbers in the data area correspond to Berkeley, not CA.
To determine the row and column headers, the VBA code uses the RowItems and ColumnItems properties of the PivotCell object in Listing 2. These properties are collections of header items in order from the outside edge of the PivotTable toward the data area. Although Listing 2 doesn't show it, the code determines the current PivotCell object by using the global expression ActiveCell.PivotCell. The inner FOR loop, which contains the loop variable i, steps through the header cells from the outside edge in. For each cell, the code records the dimension name and the dimension member name and checks two conditions before adding the member name to the drillthrough query. First, the code checks whether the previous header cell corresponded to a different dimension than the current cell. If so, the code needs to add the member from the previous cell because it's the lowest-level member from the previous dimension. The second condition checks whether the FOR loop is in its last iteration; in other words, is the current header cell adjacent to the data area of the PivotTable? If so, this cell must be the lowest-level member from the current dimension because no more space exists in the header for a lower member.
After the VBA code traverses the row and column headers, it continues by checking the page fields. The page fields are dimension-member selections for the PivotTable that don't appear on either rows or columns. Every page field corresponds to a different dimension, so the code doesn't require any additional logic to eliminate extraneous dimension members (as it would in the rows and columns).
The final step in the CreateDrillMdx() function is to add the cube name to the drillthrough query. Note that I've included hard brackets that act as quotes to surround the cube name in case the cube name includes spaces.
This drillthrough example is simple, but it's useful for anyone who's trying to use Excel XP to access OLAP data. As I mentioned, the biggest drawback of using Excel's OLAP capabilities is that Excel doesn't support drillthrough and writeback. In "Extending Excel OLAP Functionality," Microsoft includes examples of how to use VBA to circumvent both these limitations. Once you understand them, these examples are useful for more than just Excel-based analytic applications. You can use them to add drillthrough and writeback functions to any analytic application because they show how to access these important features through ADO MD.
End of Article
Prev. page
1
[2]
next page -->
