Programming Pivot Tables for Access Forms

Article
09/13/2006

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Rick Dobson

Pivot tables represent a powerful way to convey the data in Access databases, and they empower users to perform many tasks that used to require custom programming. In this article, Rick Dobson shows how you can enable your users to manipulate data with pivot tables.

Pivot tables are popular with financial and operational analysts because they serve as a "slicer and dicer" for data. More specifically, the analysts use pivot tables to view data in a cross table layout with two axes, rows and columns. In addition, pivot tables routinely offer a third dimension, sometimes called a filter axis, for organizing data. By adding a third axis (for instance, years), you can enable analysts to focus on the orders obtained by salespersons from customers in each year. Pivot tables commonly support aggregation and filtering along with other common data analysis features such as sorting. Pivot tables derive their name from the fact that they permit analysts to pivot, or drag, variables between row, column, and filter axes. This rich combination of features permits database developers to design pivot tables for analysts who can reuse the pivot tables without repeated requests for new arrangements of the same data.

Starting with Access 2002, Microsoft upgraded Access forms to substantially improve their ability to present data in a pivot-table format. These capabilities build on features initially introduced in Access 2000 for Office Web Components.

Programmatically creating a pivot table

Before starting to program pivot tables for Access forms, you need a reference to the Office Web Components library (owc10.dll). In Access 2002, the library file is available in the \Program Files\Common Files\Microsoft Shared\Web Components\10 path. Happily, Access 2003 uses the same library file as Access 2002. In Access 2002, the Microsoft Office XP Web Components item doesn't appear in the References dialog box. Therefore, it's critical that you know the path to the library file (owc10.dll) so that you can browse to it when specifying a reference.

Creating a pivot table programmatically for an Access form can require several steps:

First, you need to create a form that will hold your pivot table. You can do this with the CreateForm method.

Next, you'll typically want to change the default name assigned to the form by the CreateForm method. This practice lets your Access form name reflect its role in an application.

Finally, you need to configure the active view for a pivot table on an Access form so that it organizes the data to display into appropriate row, column, and filter axes. You do this by assigning column names from an Access table or query to axes for the active view of a pivot table.

The Office Web Components object model uses terms such as RowAxis and ColumnAxis to designate axes for an active view. Any data to be organized belongs on the DataAxis of an active view. You can designate multiple columns of data as belonging to row, column, filter, and data axes.

Creating a pivot table based on a table

The Access file with the samples for this article is available in the accompanying Download. The file contains a form, named frmPVTDesigner, with five buttons. The top button, labeled Make Orders PivotTable, creates a simple pivot table based on the Orders table imported from the Northwind.mdb file. The pivot table (see the left pane in Figure 1) categorizes OrderID by ShipCountry. The code behind the button saves the pivot table in the pvtOrders form. After invoking the click event procedure for the button, you need to delete the pvtOrders form before you can re-run the click event procedure (or change the code in the event procedure so that it saves the new form with a different name).

With a pivot table like the one in the left pane of Figure 1, an analyst can display a count of the number of orders for each ShipCountry. The right panel in Figure 1 shows that there are 16 orders with a ShipCountry column value of "Argentina". Users can generate this subtotal by right-clicking the OrderID column heading in the pivot table and choosing AutoCalc | Count. In addition, by clicking the Hide Details button on the PivotTable toolbar, users can elect to suppress the display of the individual OrderID values so that just the counts appear.

By calling three other procedures, the Click event procedure for the top button creates a form for the pivot table, assigns the custom name pvtOrders to the form, and configures the pivot table. The application uses a couple of module-level variables, strFormName and strRecordSource:

Dim strFormName As String
Dim strRecordSource As String

Private Sub cmdOrdersPivotTable_Click()
Dim strDefaultName As String

strRecordSource = "Orders"
strDefaultName = CreatePivotTable

strFormName = "pvtOrders"
If (AssignPivotTableName(strDefaultName, _
    strFormName)) = False Then
    Exit Sub
End If

ConfigureOrdersPivotTable

End Sub

The CreatePivotTable function procedure called from this code creates a new form with a RecordSource property setting equal to strRecordSource, one of the two module-level variables. You must assign a record source for a form before attempting to configure a pivot table on the form. The CreatePivotTable procedure assigns a value to the form's DefaultView property so that the form opens with a PivotTable view when a user opens the form from the Database window. This DefaultView setting doesn't impact how the OpenForm method of the DoCmd object opens the form.

The CreatePivotTable routine returns to the cmdOrdersPivotTable_Click routine the name of the new form. This name has the format Formn, where n is an integer value. After CreatePivotTable saves the form's settings and closes the form, cmdOrdersPivotTable_Click passes the default form name and a custom form name to AssignPivotTableName, which is another one of my functions. This function assigns a new custom name to the form for the pivot table unless the custom form name already belongs to another form in the database file. A For...Each loop searches through the members of the AllForms collection to determine whether another existing form already has the name for the new form. When the new custom form name already belongs to an existing form, the function procedure deletes the form created by CreatePivotTable and returns a value of False to indicate that it didn't rename the form. In this situation, cmdOrdersPivotTable_Click terminates the application. Otherwise, the AssignPivotTableName procedure successfully renames the new form and returns a value of True to cmdOrdersPivotTable_Click:

Function CreatePivotTable() As String
Const acFormPivotTable = 3
Dim frm1 As Access.Form

Set frm1 = CreateForm
frm1.DefaultView = acFormPivotTable
frm1.RecordSource = strRecordSource

CreatePivotTable = frm1.Name
DoCmd.Close acForm, CreatePivotTable, _
    acSaveYes

End Function

Function AssignPivotTableName _
    (strDefaultName As String, _
    strFormName As String) As Boolean
Dim acc1 As AccessObject
        
AssignPivotTableName = True

For Each acc1 In CurrentProject.AllForms
    If acc1.Name = strFormName Then
        MsgBox "Choose a form name other " & _
            "than '" & strFormName & "' that " & _
            "does not match an existing form."
        AssignPivotTableName = False
        DoCmd.DeleteObject acForm, strDefaultName
        Exit Function
    End If
Next acc1

DoCmd.Rename strFormName, acForm, _
    strDefaultName

End Function

After the application creates a form and assigns a custom name to it, the ConfigureOrdersPivotTable procedure assigns columns from the form's RecordSource setting to axes of the active view for the pivot table on the form. CreatePivotTable and AssignPivotTableName can be reused when creating any form for a pivot table, but the procedure for configuring a pivot table will typically be unique for each pivot table.

The configuration procedure initially opens the form for which it specifies a pivot table. Next, it uses a With...End With statement to point at the ActiveView object for the PivotTable object on the form. Inside the With...End With statement, the procedure successively assigns columns (in this case, ShipCountry and OrderID) to a PivotFieldset object (fst1). The code inside the With...End With statement inserts the PivotFieldset in an axis for the ActiveView object. ConfigureOrdersPivotTable assigns the PivotFieldset with the ShipCountry column to the RowAxis property of the ActiveView and the PivotFieldset with the OrderID column to the DataAxis property of the ActiveView:

Sub ConfigureOrdersPivotTable()
Dim fst1 as PivotFieldset

'Open form in PivotTable view and set
'a reference to the form
DoCmd.OpenForm strFormName, acFormPivotTable
Set frm1 = Forms.Item(strFormName)

'Set PivotTable fieldsets
With frm1.PivotTable.ActiveView
    Set fst1 = .FieldSets("ShipCountry")
    .RowAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("OrderID")
    .DataAxis.InsertFieldSet fst1
End With

'Close form with its PivotTable view
DoCmd.Close acForm, frm1.Name, acSaveYes

End Sub

Creating a pivot table from a query

That simple example introduced you to the basics of programmatically creating a pivot table. However, it didn't populate all the axes of a pivot table's active view, and it didn't implement either aggregation or filtering. By populating ColumnAxis and FilterAxis axes of an active view, you have more flexibility in where to locate the columns from a table or query on a form. Aggregating the columns assigned to the DataAxis property of the ActiveView object for a PivotTable object on a form can often summarize results in a way that will remove the need to display individual column values.

Figure 2 shows the design view of the query SA0903Source, which joins two tables and another query, Order Subtotals, for my next pivot table sample. This query returns a total of seven columns with at least one column from each of the underlying tables and queries. Most columns from a query or a table will be mapped to PivotFieldset objects that have just one PivotField member. With a one-to-one relationship, the PivotFieldset and PivotField objects have the same name, which is based on the column name.

However, columns with a date/time data type can yield two hierarchical PivotFieldsets that contain multiple nested PivotField members, plus a PivotFieldset that holds individual date/time values in the column. For example, when the OrderDate column from the SA0903Source query participates in a pivot table, three PivotFieldsets represent the column (OrderDate By Week, OrderDate By Month, and OrderDate). The OrderDate PivotFieldset includes the individual date/time values for orders, but the other two PivotFieldsets organize values hierarchically in a way that lets the user filter by year, quarter, month, and week.

The second button on frmPVTDesigner (named cmdPivotTableSA0903) creates a form with a pivot table that includes all seven columns from SA0903Source. The cmdPivotTableSA0903_Click routine has the same basic design as the cmdOrdersPivotTable_Click, except that the last line calls the ConfigureSA0903PivotTable procedure instead of the ConfigureOrdersPivotTable procedure:

Private Sub cmdPivotTableSA0903_Click()
Dim strDefaultName As String

'Create an empty form with a PivotTable
'default view and a record source
strRecordSource = "SA0903Source"
strDefaultName = CreatePivotTable

'Rename the form from its default name
strFormName = "pvtSA0903"
If (AssignPivotTableName(strDefaultName, _
    strFormName)) = False Then
    Exit Sub
End If

'Configure the PivotTable
ConfigureSA0903PivotTable
    
End Sub

Sub ConfigureSA0903PivotTable()
Dim fst1 as PivotFieldset

DoCmd.OpenForm strFormName, acFormPivotTable
Set frm1 = Forms.Item(strFormName)

With frm1.PivotTable.ActiveView
    Set fst1 = .FieldSets("[OrderDate By Month]")
    .FilterAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("ShipCountry")
    .ColumnAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("ShipCity")
    .ColumnAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("CompanyName")
    .RowAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("Subtotal")
    .DataAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("Freight")
    .DataAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("OrderID")
    .DataAxis.InsertFieldSet fst1
End With

DoCmd.Close acForm, frm1.Name, acSaveYes

End Sub

ConfigureSA0903PivotTable demonstrates a couple of new pivot table design techniques:

First, it shows the syntax for assigning columns from a record source to all axes of a pivot table. I use the FilterAxis property of the ActiveView object for a PivotTable object on a form to assign a column to the filter axis. Assign a column to the ColumnAxis property of an ActiveView object when you want its values to appear along the columns of a pivot table.
Second, the procedure illustrates that you can assign more than one column from a record source to an axis of an ActiveView object. Column values appear along an axis in the order that you assign them. Therefore, the ShipCity column values appear nested within the ShipCountry column values. When assigning two or more column values to the RowAxis, ColumnAxis, or FilterAxis property of an ActiveView object, you'll typically want those columns to nest naturally within one another. This nesting relationship isn't common for columns assigned to the DataAxis property of an ActiveView object.

Figure 3 shows a section of the initial version of the pivot table created by the cmdPivotTableSA0903_Click procedure. The section shows the five orders for a customer in Buenos Aires, Argentina, and the data for a second customer in Salzburg, Austria. The data in the body of the pivot table includes the extended price subtotal, the freight charge, and the OrderID value for each order by a customer.

Figure 4 shows the same pivot table after I manually pivoted the ShipCountry and ShipCity columns to the row axis for the pivot table. I also manually computed aggregates so that the pivot table's body reports the sum of the Subtotal and Freight column values as well as the count of the OrderID column values. Finally, I filtered by year for 1997 on the OrderDate By Month hierarchical values. After filtering to show just the orders for 1997, the customer from Buenos Aires in Figure 3 has just two orders. The pivoting, aggregation, and filtering demonstrate several of the reasons why pivot tables are so popular with financial and operational analysts.

Aggregating and filtering

The third, fourth, and fifth buttons on frmPVTDesigner reformat the pivot table from pvtSA0903 in various ways. These buttons are named cmdAddpvtSa0903Aggregates, cmdShowArgentinaAustria, and cmdYear1997.

You can see the impact of a click on each of these buttons by manually deleting pvtSA0903, and then clicking, in turn, the second, third, fourth, and fifth buttons on frmPVTDesigner. Clicking the second button re-creates the initial version of pvtSA0903. Clicking the third button rearranges the columns from SA0903Source in the pivot table, aggregates all entries, and displays only the aggregate values by hiding the detail values. Clicking the fourth button filters the data for all customers, leaving only those from Argentina and Austria. A click to the fifth button filters the orders to show just those from 1997. If you open pvtSA0903 after successively clicking the second through fifth buttons, you'll discover a pivot table showing only aggregate data for 1997 for customers from Argentina and Austria. The Argentinean customer's aggregate values in Figure 4 will match one of the Argentinean customers in the final pivot table created by the fifth button.

Two primary roles for the cmdAddpvtSa0903Aggregates_Click procedure are to rearrange the pivot table's design and compute aggregates. You can reformat the column values along an axis using the same syntax that you use to specify the initial position of column values along axes of the ActiveView object for a pivot table. To add aggregates, you'll typically point a PivotField object at the set of values you want to aggregate and then call the AddTotal method for the active view of a pivot table. You must also specify the style of aggregation using the plFunction* constants. The plFunctionSum argument causes the values to be summed, for instance, while the plFunctionCount argument causes the entries to be counted (the PivotTotalFunctionEnum holds the arguments for the full range of aggregation styles available). As part of aggregating your data, you also need to specify a new member of the Totals collection for an ActiveView. The following code shows the syntax for adding an aggregate of the Subtotal PivotField (the full version of the routine can be found in cmdAddpvtSa0903Aggregates_Click in the sample database):

With frm1
    Set fld1 = .PivotTable.ActiveView. _
        FieldSets("Subtotal").Fields("Subtotal")
    .PivotTable.ActiveView.AddTotal _
        "Sum of Subtotal", fld1, plFunctionSum
    Set tot1 = .PivotTable. _
        ActiveView.Totals("Sum of Subtotal")
    .PivotTable.ActiveView.DataAxis. _
        InsertTotal tot1
End With

You can filter the items that display on a pivot table by using the IncludedMembers method of the PivotFields collection of a PivotFieldset object. The following excerpt from cmdShowArgentinaAustria_Click demonstrates the syntax for invoking this method to show just customers from Argentina and Austria. The code uses an Array function to specify the list of members to include; you can use this same syntax even when there's only a single member to include. The code also shows the syntax for disabling a user from filtering the pivot table:

With frm1.PivotTable
    .ActiveView.RowAxis. _
        FieldSets("ShipCountry"). _
        Fields("ShipCountry").IncludedMembers = _
                   Array("Argentina", "Austria")
    .AllowFiltering = False
End With

The same basic approach applies to filtering by date/time values. See the cmdYear1997_Click procedure for a sample that shows how to include just orders from 1997.

Conclusion

Pivot tables represent a powerful way to convey the data in Access databases. This is because pivot tables empower users to easily reformat data for display. When Access developers offer pivot tables to their clients as an output mode, they empower users to perform many tasks that used to require custom programming with cross-tab queries. By unleashing the creative power of users to manipulate data with pivot tables, you actually create an even greater demand for your services in two areas—in the creation of new pivot tables based on existing data sources, and in the creation of new database files to serve as the source for more pivot tables.

Download PIVOTTBL.ZIP

To find out more about Smart Access and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the December 2003 issue of Smart Access. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.