Advanced Programming Techniques

[Previous] [Next]

Now that you know how to perform some of the basic programmatic operations, let's discuss a few of the more advanced techniques you can use with the PivotTable component. Many of these are implemented in the Sales Analysis and Reporting solution covered in Chapter 7, so I will briefly discuss these techniques here and refer you to that chapter for a more detailed explanation of the code involved.

Saving and Restoring Views

Anyone using the PivotTable component for a reporting system will at some point want to enable users to save a report view they have constructed and recall that report later—but with current data. The PivotTable control makes this quite easy to do. In fact, the solution in Chapter 7 illustrates this technique.

The basic approach to saving and restoring a view is to use the XMLData property at the top-level interface of the control. This property is read/write and returns a large string in an XML format. Don't confuse this with using an XML stream as a data source—the XMLData property returns a definition of the current view's layout, formatting, filters, sorts, and so on. The string completely describes the view but does not contain any data values.

If you want to save the current view definition, get this property's value and save it where you can retrieve it later. A typical approach is to post this string to an ASP page or a CGI program, which would in turn write the string to a file or database associated with the current user. When the user wants to view that report again, your code retrieves this string from the persistent storage and sets the XMLData property to the retrieved string. When you set this property, the PivotTable control throws away any data it is currently showing, connects to the original data source (if it's not connected already), and executes the appropriate query to re-create the report. The user will see any new data that appears in the data source, but the report layout will be the same as it was when the user saved it.

The PivotTable control silently discards parts of the view that are no longer valid. For example, if the user had a fieldset in the view that was removed from the data source after the view was saved, the PivotTable control will ignore any information saved with that fieldset and will not attempt to restore it in the view. The same is true for totals or members no longer contained in the data set.

To experiment with the XMLData property, run the XMLDataProperty.htm file on your companion CD. The buttons at the top of the page allow you to get the XMLData property and set it back. Plus, they let you clear the PivotTable report. Try getting the property, clearing the PivotTable report, and resetting the property.

Note that the connection information (connection string and name of the cube or command text) is also included in the string returned by the XMLData property. If the location of the data source changes between the user saving the report and reopening it, the PivotTable will fail to connect and will generate an error. If this is a possibility, you should include error-handling code to catch this and to adjust the connection information in the string before handing it to the PivotTable control. The connection string is stored in the <x:ConnectionString> tag, the data member (cube name for an OLAP source) is stored in the <x:DataMember> tag, and the command text (used for a tabular source) is stored in the <x:CommandText> tag. You can use the InStr and Replace functions in VBScript or Microsoft VBA to easily find and replace the contents of these tags. See Chapter 7 for more information on the XMLData property and changing data sources.

Locking Down a View

Reporting systems often present two types of reports: standard reports that everyone will probably want to see and ad hoc reports that users can construct themselves and save. You might want to configure those standard reports so that users cannot remove or add fields to the reports, but they can still drill down and filter. You might also want to disallow filtering, allowing only expanding and collapsing within the view. The PivotTable component offers a few settings to help you lock down the view. It also raises some events that you can use to monitor what users are doing; however, no general mechanism for denying those actions exists.

Adventures in XML

All this wonderful XML stuff only works because another of OWC's star developers, Kevin Grealish, spent many a late night wrestling with the XML parser, namespaces, and seemingly daily changes in formats and standards. While we were in the middle of developing the PivotTable component, the XML standards for namespaces were fluctuating quite a bit. Since we had to read in XML data that Excel 2000 published, it was a continual dance to keep Excel and our code bases in sync. When the source of the PivotTable report was on-sheet data, we also had to stay in sync with the MDAC persistence provider used to load the XML-Data streams saved by Excel. This discrepancy eventually worked itself out—so the next time you use the XMLData property or use XML as a data source, thank Kevin.

To prohibit users from adding or removing fields from the row and column axes, set the AllowGrouping property of the PivotTable control to False. When this property is False, the PivotTable control prohibits the user from inserting or removing fields from the row or column axis. Users can still add fieldsets to the filter axis and add new totals to the view.

To prohibit users from changing any of the filter settings, set the AllowFiltering property to False. When this property is false, the PivotTable component will let users open the filtering drop-down lists but prohibit them from changing the current filter settings—in other words, they can view the current filters, but they can't change them. The control also prevents the user from adding more fieldsets to the filter axis.

To make sure the user cannot change formatting applied to the report, set the AllowPropertyToolbox property to False. This will disable the Property Toolbox button on the toolbar and the corresponding context menu item, prohibiting the user from even opening the Property Toolbox. However, users can still use the keyboard formatting commands, such as Ctrl-B, Ctrl-I, and Ctrl-U. To experiment with these various properties, see the LockDownView.htm sample file on your companion CD.

Determining Selection for Drill Through

Like the Chart component, the PivotTable component has a Selection property at the top level that returns the currently selected object. As with the Chart component, the type of object returned by this property varies, so use the TypeName function in VBScript and VBA to determine the object type. To see the different kinds of objects the PivotTable control can return from this property, run the sample file DeterminingSelection.htm on your companion CD.

You can emulate several interesting features if you know what is currently selected. I will discuss one of these features here: drilling through to details.

OLAP systems are good at showing users a high-level data summary and letting them drill down to deeper and deeper levels of detail. However, a user will eventually reach the lowest level of the hierarchies in the hypercube and often will want the ability to drill through to the underlying details that make up that lowest-level aggregate. In the case of a tabular data source, the PivotTable control can do this automatically because the detail data is readily available. However, there is not yet a general way in OLE DB for OLAP to retrieve the set of details behind an aggregate. Despite this fact, vertical solutions often have enough domain knowledge to pinpoint which tabular data source contains the detail data and to determine how to construct a SQL statement to get the detail rows given all the members that intersect to produce that aggregate.

The following code, taken from the DeterminingSelection.htm sample file on the companion CD, shows how to get all the information from a selected aggregate to formulate a SQL statement that gets the details:

 Sub PivotTable1_SelectionChange()     ' Local variables     Dim sel         ' Temporary selection object     Dim sFilters    ' Current filter strings     Dim fSet        ' Temporary fieldset reference     ' Grab the current selection     Set sel = PivotTable1.Selection      ' There are many types of objects that the selection     ' could be, depending on what was selected     ' Examples include PivotAggregates, PivotTotals,     ' PivotMembers, PivotFields, and PivotView     ' You can use the TypeName function to determine the type     ' of the object     '     ' If the user selected an aggregate number,     ' the TypeName function will return "PivotAggregates"     ' Set the type name label     lblType.innerText = TypeName(sel)     ' If the type is "PivotAggregates", show how to get     ' the row and column members that define that aggregate     ' You could of course make this a Select Case statement     ' and handle other selection types     If TypeName(sel) = "PivotAggregates" Then         ' PivotAggregates could contain many items, but since         ' this is a sample, I will just work with the first item         Set pivotagg = sel.Item(0)         ' Set the value label         lblVal.innerText = pivotagg.Value         ' Get the total caption, the row and column members,         ' and the current filters         lblTotal.innerText = pivotagg.Total.Caption         lblColMems.innerText = BuildFullName(pivotagg.Cell.ColumnMember)         lblRowMems.innerText = BuildFullName(pivotagg.Cell.RowMember)         For Each fset In PivotTable1.ActiveView.FilterAxis.FieldSets              sFilters = sFilters & fset.Caption & "=" & _                 fset.FilterMember.Caption & ", "         Next          lblFilters.innerText = sFilters     Else         ' Selection was something other than a PivotAggregates         ' object. Clear the labels.         lblVal.innerText = ""         lblTotal.innerText = ""         lblRowMems.innerText = ""         lblColMems.innerText = ""         lblFilters.innerText = ""     End If 'typename(sel) = "PivotAggregates" End Sub 'PivotTable1_SelectionChange() 

This code first uses the TypeName function to determine the type of selection; if that type is PivotAggregates, you know that the user has selected an aggregate number. The code then retrieves the value of the aggregate and the total to which the aggregate belongs. (An aggregate by definition belongs to just one total.) Next, the code uses a helper function, BuildFullName, to build a single string representing all members up the hierarchy on the axis. The code for BuildFullName follows:

 Function BuildFullName(PivotMem)     ' Local variables     Dim pmTemp    ' Temporary PivotMember reference     ' Start by getting the current member's name     sFullName = PivotMem.Caption     ' Set the temporary reference to the current member     Set pmTemp = PivotMem     ' Navigate up the parent hierarchy until you hit nothing     While Not(pmTemp.ParentMember Is Nothing)         Set pmTemp = pmTemp.ParentMember         sFullName = pmTemp.Caption & "-" & sFullName     Wend     ' Return sFullName     BuildFullName = sFullName End Function 'BuildFullName() 

The key part of this code is the While loop in the center. As mentioned earlier, every member has a ParentMember property that returns the parent for the member if one exists. If it does not, the property returns Nothing, which is a Null object pointer in other languages. The loop builds a single string using the Caption property of each member as it walks up the hierarchy. Of course, you might want to use the Name property or even the UniqueName property (instead of the Caption property) to retrieve values needed in a SQL WHERE clause.

Showing Empty Members

By default, the PivotTable component will not display any report row or column that has no data values in any of its cells. However, sometimes the fact that a row or column has no data is meaningful, and you want to display it anyway. To do so, write the following code:

 PivotTable1.ActiveView.RowAxis.DisplayEmptyMembers = True PivotTable1.ActiveView.ColumnAxis.DisplayEmptyMembers = True 

DisplayEmptyMembers is a property of the row or column axis, and by default, its value is False. For an OLAP source, this setting affects the MDX query the PivotTable component sends to the data source, so more data is downloaded when this property is set to True. For a tabular data source, this also affects the MDX used to query the temporary cube. But since all the data is on the client by that time, it hardly affects performance—unless the cube is exceedingly sparse.

Displaying Visual Totals

By default, the PivotTable component displays what are known as visual totals. Displaying visual totals means that the subtotals and grand totals in the report are the totals of the data currently displayed in the report. If a member is filtered out, the subtotals and grand totals above that member do not include that member's value. This way, all the data shown in the report adds up, even if you filter out some members.

Suppose you have a geographical hierarchy on the row axis with two levels: State and City. The subtotal for each state reflects the total of all cities in it. Now suppose you filter out the cities Seattle and Redmond. Should the total for Washington state be the same number as before, or should it be the total of all cities still shown in the report? This is actually a hotly debated issue. Fortunately, the PivotTable control supports both modes.

The PivotTable component shows visual totals by default. If you want this control to show totals for all members regardless of their visibility, use this code:

 PivotTable1.ActiveView.TotalAllMembers = True 

The PivotTable control will place small asterisks next to the subtotals and grand totals when TotalAllMembers is True to indicate that the values are not reflecting the visibility of members. The asterisks are shown to match Excel 2000's OLAP PivotTable reports feature behavior. Ideally, you should put an explanation of the asterisk in HTML below the PivotTable control.

Showing Total Captions as Rows

By default, the PivotTable component displays total captions as the innermost column headings on the report. Occasionally, it is desirable to show those total captions as the innermost row headings instead. You can easily accomplish this with the following line of code:

 PivotTable1.ActiveView.TotalOrientation = plTotalOrientationRow 

The TotalOrientation property is set to one of the PivotViewTotalOrientationEnum constants, which include values for row or column orientation. The PivotTable control does not yet support placing the total caption anywhere but the innermost level of the row or column axis.

Expanding Automatically

By default, the PivotTable component will leave all fields and members collapsed when they are added to the view. Users can then expand any path along the axis about which they want to see more detail. You might want to configure your report to automatically expand all fields and members as soon as they are added to the view. Since this can result in a large and unwieldy display, you should do this only if auto-expansion will generate a reasonably sized report.

To turn the auto-expansion behavior on, write the following line of code:

 PivotTable1.MemberExpand = plMemberExpandAlways 

The value of this property is taken from the PivotTableMemberExpandEnum enumeration, with the other possible values of plMemberExpandNever and plMemberExpandAutomatic (which is the default).

This property also comes in handy when using the PivotTable control on a web server to generate a GIF image of your report, which we'll discuss in the next section. Setting this property so that the members always expand ensures that all information in the report is exposed when the image is written—which is important, considering that the user will get only a static image of the report and will not be able to expand members.

Using the PivotTable Component on a Web Server

Just as the other Office Web Components can be used without a user interface, you can create the PivotTable component as an in-memory object, connect to a data source, construct a report programmatically, and generate a GIF image of the current report or build your own HTML representation by walking the elements of the view. Using the PivotTable control on a web server might be a more compelling option than writing MDX queries, using ADO MD, and handcrafting a formatted result.

All the programmatic manipulation discussed so far can apply to using the PivotTable control on a server. The only new coding element you need to learn about is the ExportPicture method. This method is almost identical to the Chart component's ExportPicture method discussed in Chapter 3. To export the current report to a GIF image file, write the following code:

 Set fsoTemp = CreateObject("Scripting.FileSystemObject") strFilename = fsoTemp.GetTempName PivotTable.ExportPicture Session("strTempFilePath") & strFilename, _     "gif", PivotTable.MaxHeight, PivotTable.MaxWidth Response.Write "<IMG src='/books/3/208/1/html/2/" & Session("strTempURLPath") & _     strFilename & "'>" Session("Pivot" & Session("cntPivotImages")) = strFilename Session("cntPivotImages") = Session("cntPivotImages") + 1 

The key line in this code block is the call to the ExportPicture method. An instance of FileSystemObject (a class from the Microsoft Scripting Runtime library) retrieves a temporary filename for the new GIF image, and then the code hands that filename plus a root path to the ExportPicture method as the first parameter. As with the Chart component, the second parameter must be "gif". The last two parameters indicate the height and width, but unlike programming the Chart component (which can scale its content to any size), you must use the PivotTable component's MaxHeight and MaxWidth properties to avoid cropping the report image.

Generating a report image on the server creates the most extreme version of a locked-down report. Users can view such an image on any platform and in any browser that can display a GIF image, but the report will not be interactive.