Code Walkthrough: Data Drillthrough from the PivotTable Component
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Frank C. Rice
Microsoft Corporation
April 2003
Applies to:
Microsoft® Office XP Web Components
Microsoft SQL Server® 2000 Analysis Services
Microsoft Internet Explorer 5.0
Summary: The Office XP Web Component Toolpack includes a code sample that demonstrates using drillthrough from the PivotTable component to get at the data behind aggregations. This article examines some of the objects, methods, and procedures that make up the sample in detail. (22 printed pages)
Contents
Introduction
The Office XP Web Component Toolpack
The Drillthrough Code Sample
Drillthrough Code Sample Contents
How to Select Drillthrough in the Sample
Limitations on Drillthrough
Programmability in the Sample
Events Exposed in the Sample
Drillthrough Object
DrillthroughConstants Object
DrillthroughUI Object
DrillthroughData Object
DrillthroughSet Object
Tuple Object
The Drillthrough.htm File Code
The Drillthrough.htc File Code
Conclusion
Introduction
Online analytical processing (OLAP) cubes offer a way to aggregate, organize, and view multidimensional data. Multidimensional data is data that may have dimensions (time, region, product, customer, and so forth) that exceed what can be displayed in standard two-dimensional models such as spreadsheet grids or database tables. The PivotTable® component of the Microsoft® Office XP Web Components provides a method to view a limited set of multidimensional data from a cube.
Note The Office XP Web Components are installed by default in all editions of Office XP. You can find more information about installation and licensing in the Knowledge Base article OFFXP: General Information About Licensing and Using Office XP Web Components.
One common request from financial analysts, decision-makers, and developers is to be able to drill though on their multidimensional data. Drillthrough is an operation in which you can select a single cell containing a piece of aggregated data from a cube and retrieve the raw data from the source data in order to get more detailed information. For example, if you find sales in the Northwest district were unusually high on a certain day; you may want to see the individual sale records to see a sampling of what people may have been buying on that day.
For you to drill through, the client application in your solution must support this capability. Microsoft SQL Server™ 2000 Analysis Services introduced support for drillthrough. However, the Office XP Web Components, including the PivotTable component, do not provide any native support for performing drillthrough operations. To provide a solution for this, the Office XP Web Component Toolpack contains the Drillthrough Code sample that adds drillthrough support to the PivotTable component in a fairly seamless manner.
Note This sample requires access to a server running Microsoft SQL Server 2000 Analysis Services with the Foodmart 2000 database installed. The Foodmart 2000 database is installed with Analysis Services by default. You will also need to have drillthrough enabled on the Sales cube which is contained in the database. To enable drillthrough, see the topic Specifying Drillthrough Options in the SQL Server Books Online which can be installed as part of Analysis Services.
The Office XP Web Component Toolpack
The Office Web Component Toolpack contains a wealth of information, demonstrations, and Microsoft JScript® code samples to help you get the most out of the Office XP Web Components. This includes code walkthroughs, an interactive code library, and a number of code samples to illustrate creating business intelligence solutions with the PivotTable component. This article will also focus on the PivotTable component.
The Drillthrough Code Sample
The Drillthrough Code sample provides the code and files that can be used to add drillthrough functionality to a PivotTable component connected to an OLAP data source.
To summarize what the sample does, the code connects to an OLAP data source, retrieves data from the Sales cube in the Foodmart 2000 database, and then populates the PivotTable component. First, the user can select where the results of the drillthrough are displayed; either in a new window or in the same window as the current PivotTable component. If the user makes no selection, the data is displayed in a new window. When the user double-clicks a cell containing an aggregate or uses one of the other methods to trigger the drillthrough operation, the same original data connection is used to retrieve the detailed data. The code then calls a series of functions to build the result set. The drillthrough results are then displayed in a PivotTable component.
When you initially open the Drillthrough Code sample (point to Programs on the Start menu, point to Office XP Web Component Toolpack, point to PivotTable List Advanced Samples, and then click Drillthrough Code Sample), Microsoft Internet Explorer displays the start page as seen in Figure 1.
.gif "Aa155755.odc_pvtdrill01(en-us,office.10).gif")
Figure 1. The Drillthrough Code Sample start page
You are prompted for the name of the Analysis Services Server that contains the Foodmart 2000 (Foodmart2000.mdb) database. The Foodmart 2000 database is installed by default with Analysis Services.
.gif "Aa155755.odc_pvtdrill02(en-us,office.10).gif")
Figure 2. Prompt for name of Analysis Server
Once you enter the name of the Analysis Server (you can leave the default of localhost if the server is located on the same computer that is running the sample) and click OK, the PivotTable component loads the data from the Sales cube. Depending how your server is setup, you may also be prompted to enter a username and password before the sample can connect to the server.
.gif "Aa155755.odc_pvtdrill03(en-us,office.10).gif")
Figure 3. PivotTable component with data from the Sales cube in Analysis Services
You are now ready to use drillthrough to see the data behind an aggregation. To do a drillthrough, just click on the expand button (the + symbol) or double-click on an aggregate (such as one of the numbers in the Profit column). You can either choose to have the results of the drillthrough displayed in the same window or a new window. To have it displayed on a new window, first click the Host in New Window, and then do the drillthrough. Figure 4 shows an example of the results of a drillthrough.
.gif "Aa155755.odc_pvtdrill04(en-us,office.10).gif")
Figure 4. Results of drillthrough for Q4, Bellingham WA
Drillthrough Code Sample Contents
The Drillthrough Code sample consists of the following files:
Drillthrough.htm – The Web page user interface containing the PivotTable component and the code that initializes and controls the command buttons and context-menu option. For a default installation, this file (and the next file) are located at C:\Program Files\Office Component Toolpack\Drillthrough.
Drillthrough.htc – The HTML component file that contains code that enhances the functionality of the PivotTable component. This file sets up certain events to respond when the user clicks command buttons on the Drillthrough.htm Web page. It also contains the procedures that set up the user interface, trap user actions for drillthrough, display the drillthrough results in a new window if requested, and builds the sets that define the drillthrough result.
Note HTML components (HTCs) provide a mechanism to implement components in script as DHTML behaviors. Saved with the .htc extension, an HTC is an HTML file that contains script and a set of HTC-specific elements that define the component. More information about HTML Components can be found in the HTC Reference.
DataConn.js – Contains the code to connect to the data source and retrieve the records used by the PivotTable component. In a default installation, this file is located at C:\Program Files\Office Component Toolpack\Common.
How to Select Drillthrough in the Sample
The Drillthrough Code sample exposes drillthrough functionality in three different ways:
- Double-click an aggregate (total).
- Select an aggregate (total) and then click the Show Details button on the PivotTable component toolbar.
- Right-click an aggregate, and click Show Details from the context menu.
Note Drillthrough takes over the Show Details action that the PivotTable component exposes for relational sources but which is normally disabled when connected to OLAP data sources. Although showing details in the relational case looks somewhat different, this sample and the native functionality for showing details in the relational case, let users accomplish the same scenario: finding the individual records that contribute to a total.
With the drillthrough sample, the results of drillthrough actions can be shown in two different manners. By default, drillthrough results are shown in a new window in a PivotTable component. The benefit of this view is that the original, aggregated PivotTable component view is still visible in the original browser window, which lets a user easily continue with their analysis when they are done with the drillthrough data.
A developer can also specify an alternate mechanism for showing drillthrough data. When drillthrough is performed, that data can replace the aggregated data within the currently used PivotTable component. Users can return to their aggregated view by clicking the Show Details button a second time (the button will have the appearance of the Hide Details button when drilled in), or they can right click anywhere on the PivotTable component and select Return to Aggregated View. As many users may find pages that spawn additional windows annoying, this display mechanism eliminates that problem.
Limitations on Drillthrough
OLAP data sources, almost by definition, can contain high volumes of data. Thus, in some situations when a user clicks on a total in a PivotTable component, the implication and expectation is that drillthrough should get the records that are used to compose that total. Sometimes, there may be hundreds of thousands of records that contribute to a total. Trying to return all of these records may be not only impossible, but it would also not be something that a user may want.
For this reason, drillthrough has three developer-definable limitations that can be specified. These are provided to prevent users from potentially locking their Microsoft Internet Explorer browser when it tries to return far too many aggregate records:
- Maximum Number of Records Returned: At the outset, there should be a limit to the total number of records returned. By default, this is set to 25,000 records.
- Maximum Number of Records Returned Per Set: A drillthrough set (alternatively called a tuple) is essentially an intersection of grouping members and filter members. For example, if an aggregate is at the intersection of
Customer.USAandTime.1997.Q1and the data is filtered to only showMedia.TVandMedia.Radio, then there are two sets: the intersection of (USA, Q1, and TV), and the intersection of (USA, Q1, and Radio). As there could potentially be a number of sets, the limit defaults to 5,000 records per set.
Note A tuple is used to define a slice of data from a cube; it is essentially a collection of members from different dimensions. For example, the tuple {[North America].[United States], Q2} is formed by members of two dimensions: geography and time. The tuple {[North America].[United States], Q2, [sheet steel]} is formed by members of three dimensions: geography, time, and product. Tuples list dimensions and members in order to identify individual cells as well as larger sections of cells in the cube and because each cell is an intersection of all the dimensions of the cube, tuples can uniquely identify every cell in the cube. The tuple
(Source.[North America].[United States], Time.[2nd half].[4th quarter])
identifies a slice of a cube.
- Maximum Number of Sets: Each member you filter on creates a new set, and if you filter members from more than one field, you must do a cross-product across fields. As an example, if you filter in Media.TV and Media.Radio as well as Education.Bachelors and Education.Graduate, four drillthrough sets need to be retrieved: (Bachelors, TV), (Bachelors, Radio), (Graduate, TV), and (Graduate, Radio). Adding more filters could exponentially increase the number of sets that need to be retrieved. Note, however, that if you add something to the filter axis but do not filter out any members, that does not factor in this cross-product calculation.
Hence, the potential exists where a user may request drillthrough to be performed on a large number of sets. Since each set can have a substantial performance overhead, this might result in a slow operation even if the total number of records retrieved is low. Thus, the drillthrough sample also lets users control the maximum number of sets to attempt to retrieve, as another way to prevent a drillthrough request from overwhelming the user’s machine.
Programmability in the Sample
The drillthrough sample supports a full object model for manipulating drillthrough functionality. The hierarchy of the objects and methods used in the sample is as follows:
PivotTable as OWC10.PivotTable
.Drillthrough as Drillthrough object
.Constants as DrillthroughConstants Object
.MaxDrillthroughSets as Integer
.MaxDrillthroughRecords as Integer
.MaxDrillthroughRecordsPerSet as Integer
.SetAggregate(PivotAggregateIn)
.GetAggregate() as PivotAggregate
.UserInterface as DrillthroughUI object
.GetUseDoubleClick() as Boolean
.GetUseContextMenu() as Boolean
.GetUseToolbarButton() as Boolean
.GetViewMode() as Integer
.SetUseDoubleClick(boolean)
.SetUseContextMenu(boolean)
.SetUseToolbarButton(boolean)
.SetViewMode(integer)
.ShowMaxRecordWarning as Boolean
.Data as DrillthroughData
.GetRecordset() as ADODB.Recordset
.CoreTuple as Tuple
.Sets as array of DrillthroughSet objects
.MDX as String
.Tuple as Tuple
.Recordset as ADODB.Recordset
Events Exposed in the Sample
These events are added at the PivotTable component level:
- DrillthroughInvoke: This event fires when a user has invoked a drillthrough on an aggregate either from a double-click, context menu, or toolbar button. A DrillthroughData object is created.
- NewDrillthroughPivotTable: This event fires when a new drillthrough PivotTable object has been has been set. A developer can trap this event and do custom layout on the newly created PivotTable component. The user can also cancel the Autolayout algorithm from within this event.
- event.PivotTable: contains a pointer to the PivotTable component. A developer can set
event.Cancel = trueto prevent automatic layout of the PivotTable component.
Drillthrough Object
The Drillthrough object is the core object supporting the drillthrough functionality. The following Drillthrough object properties and methods are used in the sample:
Properties
| Property Name | Class | Description |
|---|---|---|
| Constants | DrillthroughConstants | A list of constants used for drillthrough. |
| Data | DrillthroughData | An object that contains the results of a drillthrough. |
| UserInterface | DrillthroughUI | An object for controlling user interface options for drillthrough. |
| MaxDrillthroughSets | Integer | An integer that specifies the maximum number of sets to perform drillthrough upon. If this number is exceeded, the drillthrough operation is cancelled. Default is 100. |
| MaxDrillthroughRecords | Integer | An integer that specifies the maximum number of drillthrough records to return. Default is 25,000. |
| MaxDrillthroughRecordsPerSet | Integer | An integer that specifies the maximum number of drillthrough records, per drillthrough set, to return. Default is 5,000. |
Methods
| Method Name | Parameters | Description |
|---|---|---|
| GetAggregate | PivotAggregate | Returns the current PivotAggregate which the current drillthrough pertains to. |
| SetAggregate | PivotAggregate | Add a new actionable item to the collection. |
DrillthroughConstants Object
The DrillthroughConstants object contains the enumerations that determine how the results of a drillthrough are displayed. The following DrillthroughConstants object properties are used in the sample:
Properties
| Property Name | Description |
|---|---|
| plDrillthroughViewModeNone | When the user invokes drillthrough, a DrillthroughData object is created but nothing further is done (the developer should trap OnDrillthroughInvoke and do their own drillthrough implementation.) |
| plDrillthroughViewModeSpawn | When a user invokes drillthrough, the results are shown in a new window. |
| plDrillthroughViewModeOriginal | When a user invokes drillthrough, results are placed in the original PivotTable component. |
DrillthroughUI Object
The DrillthroughUI object contains the methods and properties used for configuring the user interface. The following DrillthroughUI object properties and methods are used in the sample:
Properties
| Property Name | Class | Description |
|---|---|---|
| ShowMaxRecordsWarning | Boolean | Toggles whether to show a warning if the maximum number of records is exceeded. Default is True. |
Methods
| Method Name | Parameters | Returns | Description |
|---|---|---|---|
| GetUseDoubleClick | Boolean | Determines whether a drillthrough should be done if a user double clicks on an aggregate. | |
| GetUseContextMenu | Boolean | Determines if drillthrough options should be added to the context menu. | |
| GetUseToolbarButton | Boolean | Determines if a drillthrough button should be added to the toolbar. | |
| GetViewMode | Integer | Current view mode. | |
| SetUseDoubleClick | Boolean | Void | Toggles whether drillthrough should occur if a user double clicks on an aggregate. |
| SetUseContextMenu | Boolean | Void | Toggles whether a context menu item Show Details should be added to the context menu. |
| SetUseToolbarButton | Boolean | Void | Toggles whether a toolbar button should be added to the PivotTable component toolbar for drillthrough. |
| SetViewMode | Integer | Void | Specifies how drillthrough PivotTable component should be viewed. There are three view choices:
Default is plDrillthroughViewModeSpawn. |
DrillthroughData Object
The DrillthroughData object contains data that constitutes the current drillthrough operation. It is created when a drillthrough aggregate is set. However, actually getting drillthrough records is not performed until or if the GetRecordset method is called.
Properties
| Property Name | Class | Description |
|---|---|---|
| Sets | Array of DrillthroughSet objects | Returns an Array of DrillthroughSet objects. Drillthrough sets represent the combination of multiple filter members. |
| CoreTuple | Tuple | Returns a Tuple object that represents the core tuple. |
Methods
| Method Name | Parameters | Description |
|---|---|---|
| GetRecordset | Returns ADODB.Recordset | The Recordset that represents the drillthrough operation in its entirety. Note that this method may take some time, so you may want to consider showing a user interface that lets people know that something is happening. |
DrillthroughSet Object
The DrillthroughSet object represents the set of records that comprise the data for the PivotTable component. The following DrillthroughSet object properties are used in the sample:
Properties
| Property Name | Class | Description |
|---|---|---|
| MDX | String | Multidimensional expression (MDX) that represents the MDX necessary to get a drillthrough Recordset. |
| Recordset | ADODB.Recordset | A Recordset that represents the drillthrough set. |
| Tuple | Tuple object | A tuple that partially represents this drillthrough set. It does not include members that are part of the core tuple. |
Tuple Object
The Tuple object contains the members that define the section of a cube that make-up the drillthrough return data. The following Tuple object properties are used in the sample:
Properties
| Property Name | Class | Description |
|---|---|---|
| Members | Array of PivotMember objects | A list of PivotMembers that constitute this tuple. |
The Drillthrough.htm File Code
When the Drillthrough.htm Web page is opened, the <SCRIPT> element contains an attribute that points the page to the DataConn.js file as the source of data:
<script language="JScript" src='../common/DataConn.js'>
Next, the expression in the <BODY> tag onload='setup()' tells the page to run the setup function when then page is loaded. The setup function displays an error message if the FoodMartSalesConnection function (located in the DataConn.js file) isn't able to connect to the Foodmart 2000 database.
function setup()
{
if (!FoodMartSalesConnection().ApplyTo(pt))
{
alert ("Error connecting to data source.");
}
else
setDefaultView();
}
Otherwise, the function calls the setDefaultView function. The setDefaultView function is used to setup the PivotTable component to display the data returned from the DataConn.js file:
function setDefaultView()
{
var i;
with (pt.ActiveView)
{
DataAxis.InsertTotal(Totals("Profit"));
RowAxis.InsertFieldSet(FieldSets("Customers"));
ColumnAxis.InsertFieldSet(FieldSets("Time"));
FilterAxis.InsertFieldSet(FieldSets("Promotion Media"));
FilterAxis.InsertFieldSet(FieldSets("Promotions"));
}
with (pt.ActiveView.RowAxis.FieldSets("Customers"))
{
for (i=2; i<Fields.count; i++)
Fields(i).IsIncluded = false;
}
with (pt.ActiveData)
{
RowAxis.Member.ChildMembers(0).Expanded = true;
ColumnAxis.Member.ChildMembers(0).Expanded = true;
}
}
The page also contains two command buttons that allow the user to select where the drillthrough data should be displayed:
- Host in New Window button: Clicking this button displays the results of the drillthrough in a new window by executing the
onclickevent which calls thespawnDTfunction. - Host in Same Window button: This button is the counterpart of the above button in that the results of the drillthrough appear in the same window as the original cube data. Clicking the button executes the
onclickevent which calls thehostDTfunction.
The spawnDT and hostDT functions appear below:
function spawnDT()
{ pt.Drillthrough.UserInterface.SetViewMode(pt.Drillthrough.Constants.plDrillthroughViewModeSpawn);
}
function hostDT()
{ pt.Drillthrough.UserInterface.SetViewMode(pt.Drillthrough.Constants.plDrillthroughViewModeOriginal);
}
These functions call the SetViewMode function with the constant appropriate for the desired view. The SetViewMode function is located in the Drillthrough.htc file.
The Drillthrough.htc File Code
As stated earlier, HTC files provide script and elements that add additional functionally to components such as the PivotTable component. The Drillthrough.htc file contains a numbers of functions, of which, we will examine a few of the more important to the sample. The SetViewMode function, mentioned previously, is used to set the mode of display for the component:
function SetViewMode(iIn)
{
iViewMode = iIn;
if (btnHideDetails != null)
{
if (iViewMode == constants.plDrillthroughViewModeOriginal && fShowingDetails)
btnHideDetails.Visible = true;
else
btnHideDetails.Visible = false;
}
}
In here, you'll notice the use of constants to determine the viewing condition of the drillthrough result. These constants are defined in the DrillthroughConstants object as:
- constants.plDrillthroughViewModeOriginal: Show drillthrough operations in the original PivotTable component.
- constants.plDrillthroughViewModeSpawn: Show drillthrough operations in a new window in a PivotTable component.
- constants.plDrillthroughViewModeNone: Don't show any user interface from within the Drillthrough.htc file to handle drillthrough. The presumption is that the developer will handle doing something for drillthrough.
In JScript, functions can also contain functions. For example, one of the key functions in the Drillthrough Code sample is the Drillthrough function. This function creates the Drillthrough object and contains functions that create other objects used in the drillthough operation such as the DrillthroughData object that contains the data returned from the data source.
function Drillthrough()
{
var paggMain;
var pt;
var me = this; // When calling methods inside a function, "this" points to the function instance e.g., inside the SetAggregate function, "this" is a pointer to SetAggregate, not Drillthrough.
var iPivotVersion;
if (element != null) { pt = element; iPivotVersion = pt.MajorVersion; } // "element" is a PivotTable object.
// declare public items for this object.
this.Constants = constants;
this.Data = null;
this.UserInterface = new DrillthroughUI(pt, this);
this.UserInterface.ShowToolbarButtons();
this.ObjectName = "Drillthrough";
this.SetAggregate = SetAggregate;
this.GetAggregate = GetAggregate;
this.SetPivotTable = SetPivotTable;
this.GetPivotTable = GetPivotTable;
this.MaxDrillthroughSets = 100;
this.MaxDrillthroughRecords = 25000;
this.MaxDrillthroughRecordsPerSet = 5000;
this.ShowTotalMaxWarning = false;
this.ShowIndividualMaxWarning = false;
this.CurrentRowCount = 0;
// The SetAggregate function creates a DrillthroughData (.Data) object that hangs off the Drillthrough object. Note that if the process of building a drillthrough data object fails (e.g., too many drillthrough sets are created) then .Data == null.
function SetAggregate(paggIn)
{
paggMain = paggIn;
DoDrillthrough(paggIn);
}
function GetAggregate()
{
return paggMain;
}
function SetPivotTable(ptIn)
{
pt = ptIn;
}
function GetPivotTable()
{
return pt;
}
function DoDrillthrough(pagg)
{
var dtsarr;
var sTemp;
var adors;
me.Data = new DrillthroughData(pt, pagg);
// verification code: if we couldn't build the drillthrough sets for some reason (e.g., too many sets returned), just cancel the drillthrough operation and set the drillthrough data to null.
if (me.Data.Sets == null)
me.Data = null;
}
}
The Drillthrough function contains a number of public environmental variables which set attributes of the Drillthrough object such as the object's name and limits for the object such as the maximum number of sets and records that it can return. The Drillthrough function next calls the SetAggregate function that then calls the DoDrillthrough function to create the DrillthroughData object.
...
function SetAggregate(paggIn)
{
paggMain = paggIn;
DoDrillthrough(paggIn);
}
....
function DoDrillthrough(pagg)
{
var dtsarr;
var sTemp;
var adors;
me.Data = new DrillthroughData(pt, pagg);
if (me.Data.Sets == null)
me.Data = null;
}
...
One of the other functions called by the Drillthrough function is DrillthroughUI:
...
this.UserInterface = new DrillthroughUI(pt, this);
...
The DrillthroughUI function is used to configure the user interface and display for the returned data from the drillthrough operation. The statements and functions in the DrillthroughUI function are pretty self-explanatory and are left for you to explore on your own.
A function called by the Drillthrough function that does merit further examination here is the DrillthroughData function:
...
me.Data = new DrillthroughData(pt, pagg);
...
The DrillthroughData function is used to build the result set of data from the drillthrough operation. After initializing some constants and variables, the SetAggregate function is called which calls the BuildDrillthroughSets function. The BuildDrillthroughSets function first calls the HandleGroupAxisFromMember function with the column member, and then the row member, which together form the intersection of the cell the user clicked (or double-clicked for aggregations).
...
if (pcell.ColumnMember != null)
{
memTemp = pcell.ColumnMember
if (memTemp != null)
HandleGroupAxisFromMember(memTemp);
}
...
The HandleGroupAxisFromMember function then walks through the children of the row or column member. If the child is part of a subtotal, then the HandleFieldSet function is called:
...
for (i=0; i<memCur.ChildMembers.Count; i++)
{
memTemp = memCur.ChildMembers(i);
if (!memTemp.IsTotal)
HandleFieldSet(pfsCur, TrulyGetMember(memTemp), pfu);
}
...
The HandleFieldSet function takes a member in the fieldset (that is, one or more logically grouped sets of fields) and recursively looks through the fieldset. The fieldset is filtered using the passed in member.
...
function HandleFieldSet(pfs, memRoot, pfu)
{
var i, pfu, pfs;
if (arguments.length == 2)
{
pfu = pfs.CreateFilterUpdate;
vFirstMemberFound = null;
iCurGroupIndex = -1;
}
switch (pfu.StateOf(memRoot))
{
case ptconst.plMemberStateChecked:
if (vFirstMemberFound == null)
{
//This is the first member found.
vFirstMemberFound = memRoot;
}
else
{
if (vFirstMemberFound.UniqueName != null)
{
//A second member was found so build the structures
//for a multi-member result set.
iCurGroupIndex = iNumMFGroups;
mfmgarrGroups[iCurGroupIndex] = new MultiFilterMemberGroup();
mfmgarrGroups[iCurGroupIndex].FieldSet = pfs;
iNumMFGroups++;
mfmarrMembers[iNumMFMembers] = new MultiFilterMember();
//Add the first member found to the result set.
mfmarrMembers[iNumMFMembers].Member = vFirstMemberFound;
//Index the array for easier search.
mfmarrMembers[iNumMFMembers].Group = mfmgarrGroups[iCurGroupIndex];
mfmgarrGroups[iCurGroupIndex].NumMultiFilterMembers++;
iNumMFMembers++;
vFirstMemberFound = "MULTI";
}
mfmarrMembers[iNumMFMembers] = new MultiFilterMember();
mfmarrMembers[iNumMFMembers].Member = memRoot;
mfmarrMembers[iNumMFMembers].Group = mfmgarrGroups[iCurGroupIndex];
mfmgarrGroups[iCurGroupIndex].NumMultiFilterMembers++;
iNumMFMembers++
}
break;
//This the fallthough for ambiguous members so recursively
//look through the members beneath it.
case ptconst.plMemberStateGray:
for (i=0; i<memRoot.ChildMembers.Count; i++)
{
HandleFieldSet(pfs, memRoot.ChildMembers(i), pfu);
}
break;
case ptconst.plMemberStateClear:
return;
break;
}
//Finished checking and are back at the root.
if (arguments.length == 2)
{
//If only one member found then add that as a core tuple.
if (vFirstMemberFound!=null)
{
if (vFirstMemberFound.UniqueName != null)
{
AddTupleMember(tupCore, vFirstMemberFound);
}
}
else
{
alert(L_Err7_ErrorMessage);
}
}
}
...
As stated previously, the HandleFieldSet function receives the member from the HandleGroupAxisFromMember function and uses that member to filter the fieldset. The HandleFieldSet function then checks the filtered fieldset and if there is only one member in the filter, then that member is added to the result as a tuple. If there are multiple members in the filter, then all members are added to a member Array to comprise the drillthrough set.
For example, if only a single element is found beneath the member, the AddTupleMember function is called.
function AddTupleMember(tupSource, memIn)
{
var memarrOld = tupSource.Members;
var memarrNew;
var i;
if (memarrOld == null)
{
memarrNew = new Array(1);
memarrNew[0] = memIn;
tupSource.Members = memarrNew;
return;
}
memarrNew = new Array(memarrOld.length + 1);
for (i=0; i<memarrOld.length; i++)
{
memarrNew[i] = memarrOld[i];
}
memarrNew[memarrNew.length-1] = memIn;
tupSource.Members = memarrNew;
}
The AddTupleMember function checks to see if the tupSource.Members argument is NULL (which indicates that no members have been passed before) and, if so, creates a new Array. Otherwise, the new member is stored in the existing array.
Getting back to the BuildDrillthroughSets function, this function also contains code which constructs the SQL statement that is used to query the Foodmart 2000 database:
...
sMDX = "DRILLTHROUGH\n";
sMDX = sMDX + "SELECT FROM [" + element.DataMember + "]\n";
sMDX = sMDX + "WHERE (" + GetTupleMDX(tupCore);
...
As can be seen, the drillthrough SQL statement is built using the <DATAMEMBER> element of the PivotTable component. The <DATAMEMBER> elements contain the cube name. The WHERE clause is used to filter the results by the tuple of the selected member. The tuple is retrieved by using the GetTupleMDX function that returns a comma separated list of all the members that make up this tuple.
The Drillthrough Code sample contains a number of smaller functions with various roles in the drillthrough operation. While we have briefly covered most of the major functions in the sample, mapping the flow through the other functions can help you better understand how drillthrough works and ways to navigate through the data in an OLAP cube.
Conclusion
Drillthrough allows you to get at the data behind the summary data in an OLAP cube. Drillthrough is an important part of working with OLAP data. However, the Office XP Web Components don't natively support drillthrough. Fortunately, the Office XP Web Component Toolpack provides a sample that illustrates one way to do drillthrough. The code in the sample can be adapted for your own applications and even modified to provide better performance.