Basic Programming of the PivotTable Component

[Previous] [Next]

Now that you have a conceptual overview of the PivotTable component, let's discuss how you manipulate it to produce a desired report. As with the other Office Web Components, you can insert the PivotTable component through a designer such as Microsoft FrontPage 2000, Access 2000, Visual Basic, and so on. You can then activate it and manipulate the user interface the same way you would at runtime. (See Chapter 1 for a refresher on the containers that OWC supports and information on how to activate the controls.) The report is saved when you save the containing object (such as the web page or Visual Basic form), but in the case of the PivotTable component, only the view definition is saved. The view definition describes which fields appear on which axes, how the report is filtered and sorted, and any formatting you have applied. The control will always connect to the data source and retrieve up-to-date data when it runs.

Since this book focuses on building solutions with the Office Web Components, I will not go into building reports interactively in a designer. If you want more information on this topic, see the help file displayed when you click the PivotTable control's Help toolbar button, which explains how to do this in detail. In the sections that follow, I will describe how to build reports programmatically and how to use code to adjust many other visual aspects of the report.

Getting Data into the PivotTable Component

The PivotTable component is a fabulous piece of technology—but only if it contains data. The first thing you must do is tell the PivotTable control from where you want it to get data: a tabular data source, an OLAP (multidimensional) data source, or an XML stream.

Binding to the Data Source Component

In addition to the three sources of data mentioned above, the PivotTable component offers one more choice for retrieving data. You can use the Data Source component (DSC), the fourth member of the Office Web Components, in one of two ways: by using the built-in properties of the PivotTable component to specify a connection string and command text or cube name, or by creating the DSC directly. (Chapter 5 will discuss the DSC in much greater detail.)

The built-in properties are actually just a front for the DSC. When you set them, the PivotTable component creates a DSC internally. So in reality, you are always using the DSC, but the built-in properties save you from having to put an extra <object> tag in your HTML page or having to put an extra control on your form. Since there are no appreciable performance differences between the two options, it is purely a choice of convenience.

Whether you are using the built-in properties or the DSC, your code generally will be the same. I will note any differences between the two approaches in the discussion that follows. After this section, however, I will use only the built-in properties in the example code because it typically is easier to read.

Tabular Data Sources

To load the PivotTable component with data from a tabular data source, you would write the following code, taken from the PivotTableList.htm sample file in the Chap04 folder on the companion CD:

 Sub ConnectToTabular(ptable, sConnString, sSQL)     ptable.ConnectionString = sConnString     ptable.CommandText = sSQL End Sub 'ConnectToTabular() 

This example uses the built-in properties for data binding. If you have ever written ADO code to open a connection and execute a command, you will recognize the names of these properties. The ConnectionString property can accept any connection string that you can use with the ADO Connection object's Open method. The ADO documentation and help file provide examples and lists of all the accepted attributes for a large set of providers. (See the "Using providers with ADO" section of the ADO help file.)

A typical connection string for a Jet database looks like this:

 provider=microsoft.jet.oledb.4.0;data source=PathToMDBfile 

And a typical connection string for SQL Server looks like this:

 provider=sqloledb;data source=NameOfServer;     initial catalog=NameOfDatabase;integrated security=SSPI 

Note that this connection string indicates that the PivotTable control should use integrated security with SQL Server, which means it will use the client's Windows NT credentials when connecting to the server running SQL Server. If you simply want to use SQL Server's standard security, use the user id and password attributes instead.

After setting the connection string, you should set the CommandText property of the PivotTable component to your SQL statement or whatever command text the provider supports. (Some providers, such as the OLE DB provider for the Microsoft Active Directory services, support special command text that looks nothing like SQL but still returns a resultset.) The PivotTable component will execute this command text and process the results as described earlier in the "How the PivotTable Component Works with Data" section.

To use the DSC directly instead of via the built-in properties, you would write the following code:

 Sub ConnectToDSC(ptable, DSC, sDataMemberName)     Set ptable.DataSource = DSC     ptable.DataMember = sDataMemberName End Sub 'ConnectToDSC() 

We'll discuss how to initialize the DSC in the next chapter, but right now, let's look at how the PivotTable component is bound to it. As with the Chart component, you first set the DataSource property of the PivotTable component to an instance of the DSC. Then you set the DataMember property to the name of a data set exposed from the DSC. As in the case of the Chart component, the PivotTable component will ask the DSC for the data set by name and will then display any fields you want to include in the view (more on this in a moment).

To get started, you'll also want to use the ActiveView.AutoLayout method of the PivotTable object to quickly add all the fields in the data set to the detail area. The code you execute is just one line long:

 PivotTable1.ActiveView.AutoLayout 

OLAP Data Sources

Connecting to an OLAP data source is also easy. A typical function to connect the PivotTable component to an OLAP cube looks like this code snippet, taken from the PivotTableIntro.htm file in the Chap04 folder of your companion CD:

 Sub ConnectToCube(ptable, sConn, sCube)     ' Set the PivotTable component's ConnectionString property     ptable.ConnectionString = sConn          ' Set the DataMember property to the cube name     ptable.DataMember = sCube      End Sub 'ConnectToCube() 

The ConnectionString property is set just as it is in the previous tabular data source example, but this time the connection string looks slightly different. A typical connection string for a server running OLAP Services looks like this:

 provider=msolap;data source=NameOfServer;initial catalog=NameOfDatabase 

Because OLAP Services always uses integrated security, you don't need to specify that attribute in the connection string. The above connection string will get you connected to a server cube, but to connect to a cube file on disk, you need a connection string like this:

 provider=msolap;data source=PathToCubeFile 

After setting the ConnectionString property, you set the DataMember property of the PivotTable object to the name of the cube with which you want to work. As you adjust the report view, either through the user interface or through the programming model, the PivotTable control will generate the appropriate MDX query, execute it against the OLAP data source, and display the results in the view.

Strangely enough, the AutoLayout method has the opposite effect on the PivotTable report when using an OLAP data source instead of a tabular data source. When using a tabular data source, this method adds all fields to the detail area, showing all the data in a flat list. When using an OLAP data source, however, AutoLayout completely clears the view. Semantically, it performs the same task in both cases, but since an OLAP data source has no detail data to expose (it only exposes aggregates), the view is empty. This effect might not be exactly what you expect, but it's useful nonetheless.

XML Data Sources

As noted earlier, an XML stream (or file) is essentially the same as any other tabular data source as far as the PivotTable component is concerned. However, the way that you tell the PivotTable control to use the XML stream is slightly different. To load XML data returned from a URL into the PivotTable control, use the following code, taken from the PivotTableXML.htm file in the Chap04 folder on your companion CD:

 Sub ConnectToXML(ptable, sURL)     ptable.ConnectionString = "provider=mspersist"     ptable.CommandText = sURL End Sub 'ConnectToXML() 

This is the easiest code of all. The ConnectionString property should always be set to "provider=mspersist" when loading XML data, and the CommandText property is simply set to a URL that will return data in the specific XML format defined by ADO. Note that this URL could point to an ASP page or a CGI program that dynamically generates this XML data, providing a simple way to transmit data to the PivotTable control over HTTP.

Adjusting the View Layout

After you have connected the PivotTable component to a data source, you probably will want to add fieldsets and totals to the view, include and exclude fields in a fieldset from the view, and expand or collapse members or fields.

Immediately after connecting to the data source, the PivotTable control populates the PivotFieldSets and PivotTotals collections with all the fieldsets and totals exposed by the data source. (As you'll recall, we discussed what constitutes a fieldset and a total when using different data source types.) These two collections are returned by the FieldSets and Totals properties, which are available from the ActiveView property of the PivotTable object, and they define what is available to add to the view.

To add a fieldset or total to the view, you must first decide to which axis you want to add the element and then insert the fieldset or total into that axis. (See "Row, Column, Filter, and Data Areas (Axes))" for a review of the various axes in the view.) When you insert the fieldset or total into that axis, you can specify whether you want it to fall before any other fieldset on the axis. By default, the PivotTable component will append the new element as the innermost field on the axis.

Following is an example taken from the PivotTableIntro.htm file on the companion CD that shows how to configure a simple report for an OLAP data source:

 Sub QuickPivot(ptable, ptotal, fsRows, fsCols, fsFilter)     ' Local variables     Dim pview    ' Reference to the view          ' Grab a reference to the view     Set pview = ptable.ActiveView          ' Clear the view     pview.AutoLayout()          ' Put the fsRows dimension on the row axis     pview.RowAxis.InsertFieldSet pview.FieldSets(fsRows)          ' Put the fsCols dimension on the column axis     pview.ColumnAxis.InsertFieldSet pview.FieldSets(fsCols)          ' Put the fsFilter dimension on the filter axis     pview.FilterAxis.InsertFieldSet pview.FieldSets(fsFilter)          ' Finally, put ptotal in the data area     pview.DataAxis.InsertTotal pview.Totals(ptotal)          ' Synchronize the drop-down lists in the Quick Pivot interface     If vartype(fsRows) = vbObject Then         cbxRow.value = fsRows.Caption         cbxCol.value = fsCols.Caption         cbxFilter.value = fsFilter.Caption         cbxTotal.value = ptotal.Caption     Else         cbxRow.value = pview.FieldSets(fsRows).Caption         cbxCol.value = pview.FieldSets(fsCols).Caption         cbxFilter.value = pview.FieldSets(fsFilter).Caption         cbxTotal.value = pview.Totals(ptotal).Caption     End If          pview.TitleBar.Caption = cbxTotal.value & " by " & _         cbxRow.value & " by " & _         cbxCol.value & " filtered by " & _         cbxFilter.value  End Sub 'QuickPivot() 

You pass the names of fieldsets or totals available in the PivotFieldSets collection or PivotTotals collection to the method in the parameters ptotal, fsRows, fsCols, and fsFilter. For example, you could pass "Store Sales", "Promotion", "Time", and "Gender" as arguments, and the method will place the Promotion fieldset along the rows, the Time fieldset across the columns, the Store Sales total in the center as the numbers of the report, and the Gender fieldset on the filter axis for filtering.

Although this method places only one fieldset on each axis, you can easily nest many fieldsets on a single axis. For example, the following code builds a report showing customer locations inside customer genders on the row axis:

 pview.RowAxis.InsertFieldSet pview.FieldSets("Gender") pview.RowAxis.InsertFieldSet pview.FieldSets("Customer") 

You also can add multiple fieldsets to the column and filter axes and multiple totals to the data axis.

To insert a fieldset before an existing fieldset in the view without adding it as the innermost fieldset on the axis, use the optional Before parameter on the InsertFieldSet method:

 pview.RowAxis.InsertFieldSet pview.FieldSets("Gender") pview.RowAxis.InsertFieldSet pview.FieldSets("Customer"), 0 

This code adds the Customer fieldset to the left (or outer) side of the Gender fieldset. The last argument is the zero-based index of the fieldset on the axis that the new fieldset should be inserted before. Since this argument is 0 in the above code, the Customer fieldset is inserted as the outermost fieldset. You should always check whether the axis contains a fieldset before using this argument because passing an invalid index generates a runtime error.

Although you might be tempted to fiddle with that last optional parameter of the InsertFieldSet method, don't do it! The Remove parameter is reserved for future use and must remain set to its default value of True.

Besides adding entire fieldsets to an axis, you might also need to show a few fields of the fieldset while hiding the others. For example, if you have a fairly deep hierarchy (fieldset), such as the Product hierarchy in the sample cube, you might want to initially put only three levels (fields) of that hierarchy on the report to avoid cluttering the report with all those field buttons. To do so, set the IsIncluded property of the PivotField objects you want to hide to False. For example, to show only the first three levels of the Product hierarchy, use the following code:

 Set pview = PivotTable1.ActiveView Set fsProd = pview.FieldSets("Product") pview.RowAxis.InsertFieldSet fsProd For nField = 3 To fsProd.Fields.Count - 1     pview.FieldSets("Product").Fields(nField).IsIncluded = False Next 

To add or remove fields of a fieldset, simply set the IsIncluded property of the respective field to True or False.

You might also want to set the Expanded property for outer fields on an axis so that they automatically expand to show the child members. You can adjust the Expanded property of the entire field and of each individual member within the fieldset. For example, to expand the first level of the Product fieldset to show the children beneath each top-level item, use the following code:

 Set fsProd = pview.FieldSets("Product") fsProd.Fields(0).Expanded = True 

If you add fieldsets to an axis, you probably will also want to remove them at some point. Removing a fieldset from an axis is fairly simple and, not surprisingly, involves the RemoveFieldSet method:

 PivotTable1.ActiveView.RowAxis.RemoveFieldSet FieldSet 

The FieldSet parameter is actually quite lenient—you can specify the name of the fieldset, a reference to a PivotFieldSet object, or the index of the fieldset on the axis.

If you want to clear everything from the view of an OLAP data source, you can use the AutoLayout method as described earlier. If you are working with a tabular data source and want to completely remove all detail fields, use the following code:

 For Each fs In PivotTable1.ActiveView.DataAxis.FieldSets     PivotTable1.ActiveView.DataAxis.RemoveFieldSet fs.Name Next 

To manipulate both the detail fieldsets and the totals on the data axis of the PivotTable component, use the ActiveView.DataAxis property. The PivotDataAxis object returned by the DataAxis property has both InsertFieldSet and InsertTotal methods—along with their "Remove" counterparts. Use the *FieldSet flavor when working with detail fieldsets and the *Total flavor when working with totals.

Creating New Totals for Tabular Sources

If your data source is tabular, the PivotTable component will not have any totals available for the report by default. To see subtotal and grand total values, you must create new totals for fieldsets that do exist in the returned data. In an OLAP data source, an existing set of defined measures can be exposed as totals; however, no such set exists for tabular data.

The following code, taken from the PivotTableList.htm file on the companion CD, shows how to create a new total:

 Set c = PivotTable1.Constants Set pview = PivotTable1.ActiveView  On Error Resume Next pview.DataAxis.InsertTotal _ pView.AddTotal("Sales", pview.FieldSets("ExtendedPrice").Fields(0), _     c.plFunctionSum) pview.DataAxis.InsertTotal _ pView.AddTotal("Total Qty", pview.FieldSets("Quantity").Fields(0), _     c.plFunctionSum) 

This code snippet creates two totals, one for the ExtendedPrice fieldset and one for the Quantity fieldset. The code also adds those new totals to the data axis so that they are displayed in the report. The AddTotal method creates a new total for a given fieldset, but you also must add it to the data axis before it will appear in the report. This code uses the summary function plFunctionSum—just one of the summary functions available. The other functions supported are plFunctionMin, plFunctionMax, and plFunctionCount, which are PivotTotalFunctionEnum constants. The first release of the Office Web Components does not contain an average function and does not support custom summary functions.

Deleting totals is as simple as removing fieldsets from an axis. To do so, simply use the DeleteTotal method of the PivotView object and pass the total's name, index, or object reference.

Sorting

Now that you know how to load data into the PivotTable component and lay out the view to produce a simple report, you might want to set an initial sorting order or adjust the sorting of the report in response to a specialized user interface you have supplied. The PivotTable control allows users to perform all the sorting operations through default user interface mechanisms, such as toolbar buttons and context menus. However, you can do anything in code that the user can do in the default user interface.

There are two areas of the report that you might want to sort. When working with a tabular data source, you can sort the detail data. When working with either type of data source, you can sort the members of a grouped field by their captions or by a total—for example, sorting product promotions by the sales they generated. You can even sort members in a particular scope instead of sorting by the grand totals. Let's look at the simpler scenarios first.

The following code, taken from the SortingDetails.htm file on your companion CD, shows you how to sort the detail data in your report. You might want to run the file right now to see what this code does:

 Sub btnSort_onClick()     ' Local variables     Dim pview    ' Active view     Dim c        ' Constants          ' Grab the active view and the Constants object     Set pview = PivotTable1.ActiveView      Set c = PivotTable1.Constants               ' Set the SortDirection property for the fields to be sorted     pview.Fieldsets(cbxSort1.value).Fields(0).SortDirection = _         c.plSortDirectionAscending     pview.Fieldsets(cbxSort2.value).Fields(0).SortDirection = _         c.plSortDirectionAscending     pview.Fieldsets(cbxSort3.value).Fields(0).SortDirection = _         c.plSortDirectionAscending          ' Set the DetailSortOrder property to our array     PivotTable1.ActiveView.DetailSortOrder = Array(cbxSort1.value, _         cbxSort2.value, cbxSort3.value)      End Sub 'btnSort_onClick() 

Two elements determine sorting of the detail data: the SortDirection properties of the individual fields and the DetailSortOrder array of the active view. To sort the detail data by a field or set of fields, first set the SortDirection property of each field you want to sort. The SortDirection property takes a constant that can mean ascending, descending, or ordered naturally (which is the default). The example above sorts all the fields in ascending order, but you can use any combination of ascending and descending orders for the fields. This example also supports only three levels of sorting, but of course, you can sort every field in the report if you want.

Remember to Set the SortDirection Property!

If you fail to set the SortDirection property to a value other than the default (plSortDirectionDefault) for any field indicated in the DetailSortOrder array, the PivotTable component will not generate an error—it simply will not sort the field. I found this out the hard way when building the SortingDetails.htm sample and was thoroughly confused about why the component wasn't sorting the report even though the code seemed to run without error. The programming model help file (Msowcvba.chm) actually notes this, but of course, I didn't read the file because I thought I already knew how to do it!

If you are sorting by multiple columns at once, the second piece of information the PivotTable component needs is the order it should sort the columns in. This allows you to display the fields in one order but sort by a different order. To specify the order, you build an array of field names and hand it to the DetailSortOrder property of the active view. The preceding example uses the handy Array function in Microsoft VBScript to build a temporary array.

You also might want to sort the members of a grouped field, either by caption or by a total value. The following code, taken from the SortingMembers.htm sample file on the companion CD, shows how to do this. This technique can be somewhat confusing because you have two axes of information, so you might want to run and experiment with this file while looking at the code:

 Sub btnSort_onClick()     ' Local variables     Dim pview    ' Active view     Dim c        ' Constants     Dim fs       ' Temporary fieldset pointer          ' Grab the active view and the Constants object     Set pview = PivotTable1.ActiveView      Set c = PivotTable1.Constants          ' Get the fieldset the user wants to sort     Set fs = pview.Fieldsets(cbxDim.value)          ' Since this report has only one field per fieldset on an     ' axis, we can grab the first field and sort by it     ' If you have a hierarchy and want to sort a particular level,     ' use the corresponding field object     If cbxDir.value = "A" Then         fs.Fields(0).SortDirection = c.plSortDirectionAscending     Else         fs.Fields(0).SortDirection = c.plSortDirectionDescending     End If          ' If the cbxBy select's value is something other than     ' an empty string, get the total of that name and tell     ' the PivotTable control to sort by that total instead of     ' sorting by the member caption     If Len(cbxBy.value) > 0 Then         Set fs.Fields(0).SortOn = pview.Totals(cbxBy.value)     Else         ' To sort by the member caption, set SortOn to Nothing         Set fs.Fields(0).SortOn = Nothing     End If     ' Set the SortOnScope property to the cbxScope value     fs.Fields(0).SortOnScope = Array(cbxScope.value)      End Sub 'btnSort_onClick() 

As in detail data sorting, the first step in sorting members in a grouped field is to set the field's SortDirection property to ascending or descending. Again, setting this property to the default will return the field to its natural order. If you stop after setting the SortDirection property, the PivotTable component will sort the members alphabetically, by their captions. To sort the members by a total—for example, sorting product promotions by the amount of sales they generated—set the SortOn property. Setting this property to a PivotTotal reference causes the PivotTable component to sort the field's members by their respective values for the specified total. You must set this property to a PivotTotal object (rather than the total's name or its unique name), so you will need to look it up in the PivotTotals collection (which is returned by the Totals property) as the above code does.

By default, sorting by a total will sort the members by the grand total for the members' grid row or column. Sometimes you might want to sort the members by a total but only within a certain scope. For example, you might want the report to show the product promotions sorted by sales to men only rather than by the grand total of sales to men and women. To do this, set the SortOnScope property, which takes an array of unique member names from a field on the other axis. For instance, if you want to sort the product promotions by the sales generated from men, set the SortOnScope property to "[Gender].[All Gender].[M]", which is the unique name for the male member in the Gender fieldset. (Remember, you can retrieve this unique name from the PivotMembers collection dynamically.) If you want to sort the genders by sales made because of a particular product promotion, set the SortOnScope property of the Gender field to be the unique member name of the specified product promotion.

This concept will be clearer once you see an example of it in action. To do so, run the SortingMembers.htm sample file on the companion CD and adjust the last two drop-down lists to see the difference in results. You also can explore what the SortOnScope property does by using the built-in user interface of the control to perform complex sorting and then breaking into your debugger to see the values for the SortDirection, SortOn, and SortOnScope properties for each field in the view.

Filtering

After setting the sort order for your report, you might want to set some initial filtering for it so that only a subset of data is displayed. You can perform two types of filtering in a report: including or excluding a set of members for a field on the row or column axis, and filtering for a single member in a field on the filter axis. The first approach limits what appears down the rows or across the columns in your report; the second approach slices the data by a certain value and shows data only for that member.

For example, suppose you want to show a small subset of the product promotions in your report. If your Promotions dimension has numerous members, you might need to check that the dimension is filtered before the PivotTable component executes a query and retrieves results. The following code, taken from the FilteringMembers.htm sample file on the companion CD, shows how to set an initial filter for use when the PivotTable control queries the database:

 ' Grab references to the active view and to the Constants object Set pview = PivotTable1.ActiveView Set c = PivotTable1.Constants       ' Include only the following four members in the Promotions field Set fld = pview.FieldSets("Promotions").Fields(0) fld.FilterFunction = c.plFilterFunctionInclude fld.FilterMembers = Array("Cash Register Lottery", _                           "Free For All", _                           "Price Savers", _                           "Two for One") 

Filtering in the PivotTable component is conceptually similar to filtering in the Spreadsheet component (discussed in Chapter 2); however, the way you specify which items to include or exclude differs slightly. First, set the FilterFunction property to one of the filter function constants: plFilterFunctionInclude or plFilterFunctionExclude. Second, set the FilterMembers property to an array of member names, unique names, or PivotMember object references. The code above uses member names, but as discussed earlier, you should do this only when certain that the friendlier name is unique across the field.

To set the initial value for a fieldset on the filter axis, write the following code:

 ' Set the initial Product filter field selection to ' "Dairy", which is contained within "Drink". The first member ' of this fieldset is the All member, so look in its ' ChildMembers collection to find the "Drink" member. ' Then look in its ChildMembers collection to find the ' "Dairy" member. Set mems = pview.FilterAxis.Fieldsets(0).Members pview.FilterAxis.Fieldsets(0).FilterMember = _     mems(0).ChildMembers("Drink").ChildMembers("Dairy") 

This code, also taken from the FilteringMembers.htm sample file on the companion CD, sets the FilterMember property for the entire fieldset. When filtering on a set of members within a field, use the field's FilterMembers property; when setting the selected member for a fieldset on the filter axis, set the fieldset's FilterMember property.

This example uses the Members collection of the fieldset to get a reference to the desired PivotMember object. Although you can use the unique name here as well, I want to show you how to dynamically locate a member using the Members collection. This collection is available for every fieldset and returns a hierarchical collection of PivotMember objects. If the fieldset has an All member, representing the total for all members (most do), the top level of the PivotMembers collection will have one item representing the All member. Each member has a ChildMembers collection and a ParentMember property, allowing you to navigate up and down the member hierarchy. You can retrieve child members either by name or by index.

Although the PivotTable component does not yet support top or bottom N filtering, you can simulate these techniques by retrieving the top or bottom N member names and using the FilterMembers property to show only those members. If you already have retrieved all members from the server, you can use the top and bottom N filtering techniques described in Chapter 2. However, if you want to include only the top or bottom N items (and not retrieve the rest), consider using MDX or ADO MD (the multidimensional extensions to ADO) to execute a top or bottom N MDX query to get the member names. Then set the FilterMembers property to include only those members. For an example of this technique, see Chapter 7.

Customizing the View

We have not discussed a few elements of the PivotTable component yet—specifically the toolbar and the title bar. As you know, the toolbar is the row of command buttons across the top of the control, and the title bar is the strip of text beneath the toolbar that gives you a title for your report.

The toolbar can be either visible or hidden; by default, it is visible. Although the toolbar offers many useful commands, you might want to hide it and show your own user interface for manipulating the control. To do so, simply set the DisplayToolbar property at the top-level interface of the control to False.

The title bar can also be visible or hidden. You can customize the text in it, the background and text colors, and the font settings (font name, font size, and the bold, underline, and italic settings). Typically, you will want to set the caption of the title bar to an appropriate title for your report. To do so, you need only one line of code:

 PivotTable1.ActiveView.Titlebar.Caption = "My Report" 

The Titlebar property returns a PivotLabel object, with which you can adjust all the other font and color settings just mentioned.

A few other properties control the overall look of the report. The DisplayExpandIndicator property toggles on and off the small plus (+) and minus (-) signs next to the member labels. These expand indicators offer a one-click method for expanding and collapsing a member to show or hide its child members. If you turn the expand indicators off, the user can still double-click the member or use the Expand toolbar button to expand and collapse.

Two modeless windows can be displayed floating above the PivotTable component: the PivotTable Field List and the Property Toolbox that was first mentioned in Chapter 2. Both of these windows can also be displayed or hidden programmatically by toggling the DisplayFieldList and DisplayPropertyToolbox properties. Since these are Boolean properties, you can also use them to discover whether the two windows are being displayed. Automatically displaying the field list is useful when a user first opens a report that does not yet have any fields or totals in the view.

Applying Formatting Programmatically

You can format a number of the elements in a PivotTable report differently than their default appearance. However, the PivotTable component does not yet support per-cell formatting in the report. You can set the number format for totals shown in the report, make the subtotal numbers look different than the other numbers, adjust the formatting of the member labels, change the appearance of the field buttons, and change the formatting of the various drop areas. Chapter 7 will explain how to automatically apply formatting based on your web page's current style sheet. For now, let's take a look at some of the simpler formatting tasks.

Most likely, you first will want to format the numbers in your report. By default, the numeric values appear without any number formatting, but you can apply any of the number formats supported in the Spreadsheet component. (See Chapter 2 for more information on this.) For example, if you are analyzing sales data, you generally want to format the numbers in a specific currency format. To do this, you would write code like so:

 PivotTable1.ActiveView.Totals("Store Sales").NumberFormat = "$#,##0.00" 

Although you specify the number format for the PivotTotal object, the font and color settings for the totals in your report are not exposed by this object. Instead, you must set them using the active view properties named TotalBackColor and TotalFont.

You'll probably want to make your grand totals stand out in the report—for example, by making the background color something other than white and making the numbers bold. To do so, you would write code like this:

 Set pfld = pt.ActiveView.RowAxis.FieldSets(0).Fields(0) pfld.SubtotalBackColor = "Wheat" pfld.SubtotalFont.Bold = True Set pfld = pt.ActiveView.ColumnAxis.FieldSets(0).Fields(0) pfld.SubtotalBackColor = "Wheat" pfld.SubtotalFont.Bold = True 

The grand total of the row or column axis is actually a subtotal of the very first included field of the very first fieldset on the particular axis. To adjust its formatting, you establish a reference to this field and then modify the SubtotalBackColor and SubtotalFont properties.

You can use this same technique to format the subtotals at any inner level so that they are different than the numbers contributing to them. To adjust the subtotal formatting, first establish a reference to the field to which the subtotal belongs and adjust the SubtotalBackColor and SubtotalFont properties.

Keep in mind that these properties are carried with the field as the user or your code moves it around the report. If your intention is merely to format the grand totals to look a certain way regardless of what fields are placed on the row or column axis, you need to reapply formatting during the QueryComplete event, which is raised whenever the report's layout is changed.

How Do I Select the Entire Component to Format It?

When applying formatting using the Property Toolbox, you might not be sure how to select the entire control to set control-level formatting such as the text in the title bar or various display settings for the entire view. To select the whole control, click the title bar. To make the title bar visible, use the Show/Hide section of the Property Toolbox.

Using AutoFit

You might notice in all the samples in this chapter that the PivotTable component seems to automatically grow and shrink when you change the view. This behavior, which is called AutoFit, is on by default. When AutoFit is on, the PivotTable control will resize itself to show all data in the report without internal scroll bars. This is especially useful in web pages since they already contain scroll bars. The PivotTable control will resize until it reaches the MaxHeight and MaxWidth property settings and then will show internal scroll bars again.

If you are working in a fixed forms environment such as Visual Basic, you likely will want to turn this behavior off so that the control stays the desired size and does not extend outside the form. (The form will crop the control when it extends past the form's edge.) To do so, set the AutoFit property at the control's top-level interface to False.

The PivotTable component has another level of AutoFit; by default, this control resizes the column widths of the detail grid so that no numbers get truncated. Again, each column will grow until it reaches the limits imposed by the DetailMaxWidth and DetailMaxHeight properties. You can disable the AutoFit behavior of the detail grid by setting the ActiveView's DetailAutoFit property to False.

What Do You Mean You Want to Resize Yourself During Scrolling?

The Internet Explorer developers found the PivotTable component's AutoFit behavior nothing less than bizarre. They had never seen a control that automatically resized itself and did so while you scrolled the document, making more of the report visible. Mike Coulson, the extraordinary developer who programmed the user interface portion of the PivotTable component, spent quite a long time optimizing this control so that it retrieves only the data currently shown onscreen. This allows the control to display data quickly even if it still has to read 10,000 more rows in the background. To keep the AutoFit feature from slowing this process down, Mike dynamically adjusted the control's size as new data scrolled into view (if the data was larger than that shown previously).

Since the Internet Explorer team never thought that a control might resize itself while scrolling in the document, this behavior exposed quite a few bugs in the Internet Explorer code base. Thankfully, the Internet Explorer team was responsive to fixing those bugs for us. However, I think the PivotTable control still qualifies as one of the most complex controls a container can hold, especially when using the AutoFit feature.