Programming PivotTable Reports in Microsoft Access 2002

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.

 

Keith Fink, Frank C. Rice
Microsoft Corporation

October 2002

Applies to:
     Microsoft® Access 2002

Summary: Discover the elements of a PivotTable report and learn how to programmatically build and manipulate PivotTable reports in Microsoft Access 2002. The event model for PivotTable reports will also be discussed. (29 printed pages)

Download odc_acprogpvt.exe

Contents

Introduction
PivotTable Report Elements
PivotTable Object Model
PivotTable Report Collections
PivotTable Report Filters
PivotTable Events
Conclusion
Appendix: PivotTable Properties

Introduction

PivotTable reports are a special kind of table used in Microsoft Office applications such as Microsoft Excel and Microsoft Access to summarize information from selected fields in a data source. By using a PivotTable report, you can dynamically change the layout of the table to view and analyze data in different ways. For example, you can rearrange row headings, column headings, and filter fields until you achieve the desired layout. Each time you change the layout, the table immediately recalculates the data based on the new arrangement.

In Access, you can build and manipulate PivotTable reports through the user interface, or programmatically through an object model with Visual Basic® for Applications (VBA). There is also an event model that fire specific events in Access as you interact with PivotTable reports.

In this article, we will first discuss the different elements that comprise a PivotTable report. Then, we will examine the PivotTable object model in Access 2002. Next, we will demonstrate how to refer to PivotTable reports contained within forms, and describe the various objects and collections that can be manipulated. And finally, we will describe the events associated with a PivotTable report and present code samples demonstrating several of the events.

Many of the code samples presented in this article have also been included in the Access .mdb files that are included in the self-extracting executable file that you can download from the MSDN.

PivotTable Report Elements

Before learning how to programmatically build and manipulate PivotTable reports, you must understand the different elements and areas that make up a PivotTable report. For the purposes of this explanation, there are two main elements of a PivotTable report: Axes and FieldList members.

Axes

An axis is an area of the PivotTable window that may contain one or more fields of data. Within the user interface, axes are also known as drop zones, because you can drag and drop fields into them.

There are four main axes on a PivotTable report. Each axis has a different purpose. The figure below shows the layout of a blank PivotTable report in Access 2002.

Figure 1. PivotTable report drop zones

There are four main axes: Row Fields, Column Fields, Filter Fields, and Totals or Detail Fields.

The Row Fields axis defines which fields should be listed down the left side of the PivotTable report, very similar to the Row Headings field in a crosstab query.

The Column Fields axis defines which fields should be listed across the top of the PivotTable report, very similar to the Column Headings field in a crosstab query.

Note   Use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information — one down the left side of the datasheet and another across the top. Crosstab queries cannot be pivoted.

The Filter Fields axis defines which fields you can use to filter the PivotTable report, without having to display it on another axis. For instance, you may want to filter the data by ShipVia field, but do not want to display that field within the PivotTable report.

The Totals or Detail Fields axis defines which fields should be displayed at the intersection of each row and column. This is very similar to the Value field in a crosstab query. The table below demonstrates the properties used for accessing each axis.

Axis	Property
Row Fields	RowAxis
Column Fields	ColumnAxis
Totals or Detail Fields	DataAxis
Filter Fields	FilterAxis

Field List

The PivotTable field list is similar in functionality to a field list you would use in a query or form. It is used to display which fields are available for use in the PivotTable report, based on the form's RecordSource property. There are some distinctions to the PivotTable field list, however, that are important to understand. The figure below displays a typical field list for an object in PivotTable view.

Figure 2. PivotTable field list

There are three main elements in the field list: Fieldsets, Fields, and Totals.

Fieldsets

Note that the field list displays expandable nodes that represent each field available on the form. When a node is expanded, the members are visible underneath. For example, the Order Date by Month node can be expanded to show a number of members, such as Years, Quarters, Months, etc. Also, note the LastName node is expanded to show only one member: LastName.

The top-level node is known as a Fieldset. A Fieldset may contain one or more members. In most cases, Fieldsets will contain only one member as demonstrated by the LastName field. For a form in PivotTable view, the field list contains a Fieldset for each field that has been placed on the form in Design view. For tables and queries in PivotTable view, the field list contains the fields available in that table or query.

Each Fieldset contains at least one member that represents the actual field from the underlying table or query. If there are date/time fields available, there will be additional FieldSets that contain multiple members, which allow grouping based on a particular portion of the date.

Fields

Fields are the lowest members of a FieldSet. For instance, the Last Name FieldSet contains one field: LastName. The Order Date by Month FieldSet contains seven fields, each one representing a different portion of time that can be grouped. These fields actually represent the object that we will be adding programmatically to the PivotTable report.

Totals

Note that there is a Totals node near the top of the field list. The Totals node can be expanded to show all aggregate functions, or calculations involving aggregate functions that have been created in the PivotTable report. In this particular PivotTable report, the user has created only one total field: Sum of Freight.

All aggregate functions created by the user, such as Sum, Average, Min, Max, and Standard Deviation will be displayed under the Totals node.

PivotTable Object Model

In the following sections, we will introduce the PivotTable object model in Access 2002. We will also demonstrate how to refer to PivotTable reports contained within forms.

PivotTable Property

To access the PivotTable object exposed by a form, use the PivotTable property. It is the top level of the PivotTable object model. It is the interface for accessing all collections, objects, methods, and properties of the PivotTable report. The following example references the PivotTable property of a form, and sets the BackColor property of the PivotTable report to the color Red.

'Requires reference to Microsoft Office Web Components 10.0
'library: OWC10.dll
Dim pTable as OWC10.PivotTable
Set pTable = Forms("Invoices").PivotTable
pTable.BackColor = 255

To see a list of all properties and methods associated with the PivotTable object, please see the appendix at the end of this article.

ActiveView Object

The ActiveView property represents the layout of the PivotTable report. This is the main object we will use for building and manipulating PivotTable reports. Setting a variable to the active view of the PivotTable report provides you with a convenient method to make changes to its layout.

The following example demonstrates how to use the ActiveView property to access properties of the FilterAxis within a PivotTable report. In this case, we set the Caption property of the FilterAxis to a custom string.

'Requires reference to OWC10.dll
Dim pTableView As OWC10.PivotView
Set pTableView = Forms("Invoices").PivotTable.ActiveView
pTableView.FilterAxis.Label.Caption = _
   "Please insert your Filtering Fields here!"

PivotTable Report Collections

In the following sections, we discuss some of the collections associated with PivotTable reports and ways of manipulating PivotTable reports programmatically by using the objects in those collections.

Fieldsets and Fields Collections

The Fieldsets collection of the ActiveView object is a collection of Fieldset objects. Each Fieldset object represents a top-level node in the field list. Each Fieldset object contains a Fields collection, which contains PivotField objects. Each PivotField object represents a detail field in the Fieldset. For example, the Fields collection of the Last Name FieldSet in Figure 2 above contains only one PivotField, LastName. The Fields collection of the Order Date by Month FieldSet in Figure 2 above contains seven PivotField objects (Years, Quarters, Months, etc.).

This sample code enumerates through each FieldSet in the PivotTable report's ActiveView, prints the name of the FieldSet, and then the number of PivotField objects in the Fields collection.

Sub EnumerateFieldsets()
   'Requires reference to OWC10.Dll
   Dim pTable As OWC10.PivotTable
   Dim pTableView As OWC10.PivotView
   Dim pFieldset As OWC10.PivotFieldSet
   Dim pField As OWC10.PivotField
    
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTable = Forms("Invoices").PivotTable
   Set pTableView = pTable.ActiveView

   'Loop through each Fieldset in the Field List
   'and print its name.
   For Each pFieldset In pTableView.FieldSets
      Debug.Print "The '" & pFieldset.Name & _
         "' FieldSet contains " & _
         pFieldset.Fields.Count & " field(s):"

      'Loop through each PivotField object in the Fields
      'collection and print its name.
      For Each pField In pFieldset.Fields
         Debug.Print vbTab & pField.Name
      Next
      Debug.Print
   Next
End Sub

Output
=======
The 'OrderID' FieldSet contains 1 field(s):
    OrderID

The 'CustomerID' FieldSet contains 1 field(s):
    CustomerID

The 'EmployeeID' FieldSet contains 1 field(s):
    EmployeeID

The 'OrderDate' FieldSet contains 1 field(s):
    OrderDate

The 'OrderDate By Week' FieldSet contains 6 field(s):
    Years
    Weeks
    Days
    Hours
    Minutes
    Seconds
...

Adding Calculated Fields to the Field List

Sometimes it may be necessary to add calculated fields to the field list. It is possible to do this by using the AddFieldSet and AddCalculatedField methods. You must first create a blank Fieldset object by using the AddFieldSet method. This new Fieldset does not contain any fields, so you must then use the AddCalculatedField method to add a calculated field to the Fieldset.

The AddFieldSet method is very straightforward. It only accepts one argument, which is the name of the new Fieldset to create. The AddCalculatedField method is more complex. It accepts a number of arguments: Name, Caption, Data Field, and Expression. The Name argument represents the name of the new calculated field within the Fields collection. The Caption argument represents how the field will be displayed in the field list, similar to the Caption property in a table. The DataField argument is the name of the field to create in the PivotTable report's underlying ActiveX® Data Object (ADO) Recordset object. Finally, the Expression argument represents the actual expression that should be computed.

The example below creates a new Fieldset named Price. It then creates a calculated field named CalculatedPrice, with an expression of UnitPrice*Quantity*(1-Discount).

Sub AddCalculatedFieldset()
   'Requires reference to OWC10.Dll
   Dim pTable As OWC10.PivotTable
   Dim pTableView As OWC10.PivotView
   Dim pFieldset As OWC10.PivotFieldSet
   Dim pField As OWC10.PivotField
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTable = Forms("Invoices").PivotTable
   Set pTableView = pTable.ActiveView
   On Error GoTo AddCalculatedFieldset_Err
   Set pFieldset = pTableView.FieldSets("Price")
   pFieldset.DisplayInFieldList = True
   Set pField = _
      pFieldset.AddCalculatedField("CalculatedPrice", _
      "Calculated Price", "calcPrice", _
      "UnitPrice*Quantity*(1-Discount)")
AddCalculatedFieldset_Exit:
   Exit Sub
AddCalculatedFieldset_Err:
   Select Case Err.Number
      Case 9  'Subscript out of range
         Set pFieldset = pTableView.AddFieldSet("Price")
          Resume
      Case -2147467259  'Field already exists in fieldset
         Resume Next
   End Select
End Sub

Figure 3. Field list with calculated fieldset

Clearing a PivotTable Report

There are a couple of methods for clearing a PivotTable report. The easiest approach is to reset the form's RecordSource property. This automatically causes Access to remove all Fieldsets from all axes of the PivotTable report, as well as removing any totals or calculated fields from the field list. This will also reset any custom formatting you have done, so this method should be used with caution. This code snippet sets the form's RecordSource property to itself, which keeps the form bound to the same data, but resets the PivotTable views completely.

...
Me.RecordSource = Me.RecordSource
...

In some situations, it may be desirable to remove Fieldsets from PivotTable axes while keeping custom formatting, totals, and calculated fields. This sample code removes all Fieldset objects from each axis, and all Total objects from the Data axis. However, neither the custom formatting nor the items in the field list are removed.

Sub RemoveFieldsetsAndTotals()
   Dim pView As OWC10.PivotView
   Dim pFieldset As OWC10.PivotFieldSet
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pView = Forms("Invoices").PivotTable.ActiveView
   With pView
      Do Until .RowAxis.FieldSets.Count = 0
         Set pFieldset = .RowAxis.FieldSets(0)
         .RowAxis.RemoveFieldSet pFieldset
      Loop
        
      Do Until .ColumnAxis.FieldSets.Count = 0
         Set pFieldset = .ColumnAxis.FieldSets(0)
         .ColumnAxis.RemoveFieldSet pFieldset
      Loop
        
      Do Until .FilterAxis.FieldSets.Count = 0
         Set pFieldset = .FilterAxis.FieldSets(0)
         .FilterAxis.RemoveFieldSet pFieldset
      Loop

      Do Until .DataAxis.Totals.Count = 0
         .DataAxis.RemoveTotal .DataAxis.Totals(0)
      Loop
   End With
End Sub

PivotTotal Objects and the Totals Collection

The Totals collection of the ActiveView object contains PivotTotal objects. Each PivotTotal object represents an aggregate function or a calculated total. An aggregate function is a calculation that calculates some result for a field across all records, very similar to aggregate functions in Access queries. Some examples of aggregate functions are Sum, Count, Min, Max.

So let's assume you have added two PivotTotal objects to the field list. FreightTotal is the name of a PivotTotal object that performs a sum of the Freight field. OrderCount is the name of a PivotTotal object that performs a count of the OrderID field.

A calculated total is an expression that uses the results of other aggregate functions within the Totals collection. To continue with our example above, a calculated total would be:

FreightAverage: FreightTotal / OrderCount

This calculated total uses the results of the FreightTotal and OrderCount aggregate functions in its expression to calculate an average. This example is just for illustration. Of course it would have been much easier to just use the Average aggregate function to begin with.

The method you use to create the PivotTotal object depends on which type of PivotTotal object you want.

Adding Aggregate Pivot Totals to the Field List

To add an aggregate PivotTotal object to the field list, the object must first be created and added to the Totals collection. To do this, use the AddTotal method. The AddTotal method requires three arguments: the name of the new PivotTotal object, a reference to a PivotField object in an existing Fieldset object, and a constant that determines what particular aggregate function should be performed. Once the PivotTotal object has been added to the Totals collection, it will display under the Totals node in the field list. The following sample code creates two totals: a sum of the Freight field named FreightTotal, and a count of the OrderID field named OrderCount.

Sub AddAggregatePivotTotals()
   'Requires reference to OWC10.dll
   Dim pTable As OWC10.PivotTable
   Dim pTableView As OWC10.PivotView
   Dim pField As OWC10.PivotField
    
   On Error GoTo AddAggregatePivotTotals_Err
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTable = Forms("Invoices").PivotTable
   Set pTableView = pTable.ActiveView
   With pTableView
      Set pField = _
         .FieldSets("Price").Fields("CalculatedPrice")
         .AddTotal "PriceTotal", pField, plFunctionSum
      Set pField = .FieldSets("OrderID").Fields("OrderID")
         .AddTotal "OrderCount", pField, plFunctionCount
   End With
Exit Sub
AddAggregatePivotTotals_Err:
   If Err.Number = 9 Then
      AddCalculatedFieldset
      Resume
   End If
End Sub

Figure 4. Field list with aggregate pivot totals

Adding Calculated Pivot Totals to the Field List

To add a calculated PivotTotal to the field list, it must first be created and added to the Totals collection. To do this, use the AddCalculatedTotal method. This method requires three arguments: the name of the new PivotTotal object, the caption to be shown in the field list, and the actual expression to be calculated.

The following sample code creates a calculated PivotTotal object named FreightAverage that divides the FreightTotalPivotTotal object by the OrderCountPivotTotal object to calculate an average. This causes the calculated PivotTotal object to be displayed in the field list.

Sub AddCalculatedPivotTotal()
   'Requires reference to OWC10.Dll
   'NOTE: This procedure assumes you have already run the
   'AddAggregatePivotTotals() procedure above.

   Dim pTable As OWC10.PivotTable
   Dim pTableView As OWC10.PivotView
   Dim pField As OWC10.PivotField
   Dim pTotal As OWC10.PivotTotal

   On Error GoTo AddCalculatedPivotTotal_Err
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTable = Forms("Invoices").PivotTable
   Set pTableView = pTable.ActiveView
   Set pTotal = pTableView.Totals("PriceTotal")
   pTableView.AddCalculatedTotal "PriceAverage", _
      "Price Average", "[PriceTotal] / [OrderCount]"
   Exit Sub
AddCalculatedPivotTotal_Err:
   If Err.Number = 9 Then
      AddAggregatePivotTotals
      Resume
   End If
End Sub

Figure 5. Field list with calculated pivot total

Adding Fieldsets and Totals to PivotTable Axes

So far we have been looking at how the Fieldsets and Totals collections work, how to create new PivotTotal objects, and how to add them to the field list. This section will demonstrate how to take the objects from the field list and place them on the PivotTable report. Now that you have learned how to refer to objects in the Fieldsets and Totals collections, adding them to the PivotTable report is straightforward.

Adding Fieldsets to PivotTable Axes

The first thing you must do before inserting a Fieldset object is to designate which axis should contain the Fieldset. To do this, you must refer to the appropriate axis property of the ActiveView object. The valid axis properties are listed in the PivotTable Elements section above. Then you must use the InsertFieldSet method to add the Fieldset to the axis.

Sub AddFieldsetsToPivotTable()
   Dim pTable As OWC10.PivotTable
   Dim pTableView As OWC10.PivotView
   Dim pAxis As OWC10.PivotAxis
   Dim pField As OWC10.PivotField
   Dim pFieldSet As OWC10.PivotFieldSet
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTable = Forms("Invoices").PivotTable
   Set pTableView = pTable.ActiveView
    
   'We want to add SalesPerson to the Row Fields drop zone,
   'so we reference the RowAxis property
   Set pAxis = pTableView.RowAxis
    
   'Use the InsertFieldset method, and reference the
   'Fieldset object we wish to add to this axis
   Set pFieldSet = pTableView.FieldSets("SalesPerson")
   pAxis.InsertFieldSet pFieldSet
   pFieldSet.Fields("SalesPerson").IsIncluded = True
    
   'We want to add CompanyName to the Column fields drop
   'zone, so we reference the ColumnAxis property
   Set pAxis = pTableView.ColumnAxis
    
   'Use the InsertFieldset method, and reference the
   'Fieldset object we wish to add to this axis
   
   Set pFieldSet = _
           pTableView.FieldSets("OrderDate By Month")
   For Each pField In pFieldSet.Fields
     pField.IsIncluded = False
   Next
   pFieldSet.Fields("Years").IsIncluded = True
   pAxis.InsertFieldSet pFieldSet
    
   'We want to add ShipCountry to the Filter fields drop
   'zone, so we reference the FilterAxis property
   Set pAxis = pTableView.FilterAxis
   Set pFieldSet = pTableView.FieldSets("ShipCountry")
   pAxis.InsertFieldSet pFieldSet
End Sub

Figure 6. PivotTable report with fieldsets added to axes

Adding Totals to PivotTable Axes

The process for inserting a PivotTotal object to a PivotTable axis is very similar to adding a Fieldset object to an axis. You must refer to the DataAxis property of the ActiveView object, since this drop zone is the only one that may contain totals. Then, use the InsertTotal method to add the PivotTotal object to the axis. Optionally, you may want to use the HideDetails method so that summary data is shown by default.

Sub AddTotalsToPivotTable()
   'Requires reference to OWC10.dll
   Dim pTable As OWC10.PivotTable
   Dim pTableView As OWC10.PivotView
   Dim pDataAxis As OWC10.PivotDataAxis
    
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTable = Forms("Invoices").PivotTable
   Set pTableView = pTable.ActiveView

   'Refer to DataAxis property
   Set pDataAxis = pTableView.DataAxis
   On Error GoTo AddTotalsToPivotTable_Err
   'Use InsertTotal method to add each Total
   pDataAxis.InsertTotal pTableView.Totals("PriceTotal")
   pDataAxis.InsertTotal pTableView.Totals("OrderCount")
   pDataAxis.InsertTotal pTableView.Totals("PriceAverage")
    
   'Format the appropriate totals as Currency
   pDataAxis.Totals("PriceTotal").NumberFormat = _
      "Currency"
   pDataAxis.Totals("PriceAverage").NumberFormat = _
      "Currency"
    
   'Hide detail data or otherwise the user will see a
   '"No Details" message in the PivotTable
   pTable.ActiveData.HideDetails
   Exit Sub
AddTotalsToPivotTable_Err:
   If Err.Number = 9 Then
      AddCalculatedPivotTotal
      Resume
   End If
End Sub

Figure 7. PivotTable report with fieldsets and totals

PivotTable Report Filters

Now that you have learned how to programmatically build a PivotTable report, this section will demonstrate how to filter a PivotTable report. The Field object that we have been using thus far has properties that allow us to set filters on the field. There are two properties: IncludedMembers and ExcludedMembers.

The IncludedMembers property returns an Array that contains all values that the field is being filtered on. The ExcludedMembers property returns an Array that contains all values that are excluded from the filter. The biggest difference between these two properties is really perception.

For instance, if you had unchecked all values manually, and then decided to filter the ShipCountry field for Belgium, Canada, and France; then these three values would be in the IncludedMembers property. Alternatively, let's say you checked all items and then decided to uncheck just USA; then this value would be in the ExcludedMembers property.

To filter a field for certain values, create an Array that contains the values you want to include or exclude. The easiest way to do this is to use the Array() function in VBA. Then, assign the Array to the IncludedMembers or ExcludedMembers property. The following procedure filters the ShipCountry field on the Filter drop zone to include only Belgium, Canada, and France. It also filters the SalesPerson field on the Row Fields drop zone to exclude Andrew Fuller, and Nancy Davolio.

Sub AddFilters()
   'Requires reference to OWC10.Dll
   Dim pTableView As OWC10.PivotView
   Dim pFieldset As OWC10.PivotFieldSet
   Dim pField As OWC10.PivotField
   Dim varArray

   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTableView = _
      Forms("Invoices").PivotTable.ActiveView
    
   'Use the Array function to build an array of
   'values we want to include in the filter
   varArray = Array("Belgium", "Canada", "France")
    
   Set pFieldset = pTableView.FieldSets("ShipCountry")
   Set pField = pFieldset.Fields("ShipCountry")
   pField.IncludedMembers = varArray
    
   'Use the Array function to build an array of
   'values we want to EXCLUDE in the filter
   varArray = Array("Andrew Fuller", "Nancy Davolio")
   Set pFieldset = pTableView.FieldSets("SalesPerson")
   Set pField = pFieldset.Fields("SalesPerson")
   pField.ExcludedMembers = varArray
End Sub

Determining Filtered Values

To determine which values are either being included or excluded in a field, you can enumerate the IncludedMembers and ExcludedMembers properties for each field. The ValuesBeingFiltered procedure below demonstrates what values are being included or excluded for each field in each axis of the PivotTable report. It calls the PrintAxisFilters procedure for each axis in the PivotTable report, and passes the axis as an argument.

Sub ValuesBeingFiltered()
   Dim pTableView As OWC10.PivotView
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTableView = _
      Forms("Invoices").PivotTable.ActiveView
   Debug.Print "RowAxis"
   Debug.Print "======="
   PrintAxisFilters pTableView.RowAxis
   Debug.Print
   Debug.Print "ColumnAxis"
   Debug.Print "=========="
   PrintAxisFilters pTableView.ColumnAxis
   Debug.Print
   Debug.Print "FilterAxis"
   Debug.Print "=========="
   PrintAxisFilters pTableView.FilterAxis
   Debug.Print
   Debug.Print "DataAxis"
   Debug.Print "========"
   PrintAxisFilters pTableView.DataAxis
End Sub
Sub PrintAxisFilters(pAxis As Object)
   Dim pFieldset As OWC10.PivotFieldSet
   Dim pfield As OWC10.PivotField
   Dim varValue As Variant
   With pAxis
      For Each pFieldset In .FieldSets
         For Each pfield In pFieldset.Fields
            Debug.Print "Field: " & pfield.Name
            On Error Resume Next
            For Each varValue In pfield.IncludedMembers
               Debug.Print varValue.Value & vbTab & _
                  "(Included)"
            Next

            For Each varValue In pfield.ExcludedMembers
               Debug.Print varValue.Value & vbTab & _
                  "(Excluded)"
            Next
         Next
      Next
   End With
End Sub
Output
------

RowAxis
=======
Field: Salesperson
Andrew Fuller   (Excluded)
Nancy Davolio   (Excluded)

ColumnAxis
==========
Field: Shippers_CompanyName

FilterAxis
==========
Field: ShipCountry
Belgium (Included)
Canada  (Included)
France  (Included)

Removing Filters from a PivotTable Report

To remove all filters from a PivotField object, set the AllIncludeExclude property for the Fieldset object to the constant plAllDefault. This will cause all values to be removed from the filter, and will reset that Fieldset object back to showing all members.

This sample code enumerates through all PivotField objects in all Fieldsets, and sets the parent Fieldset object's AllIncludeExclude property to plAllDefault. Note that this code applies to all Fieldsets in the field list, so all filters on the PivotTable report will be cleared. If you only wanted to clear the filters from a particular axis, it is possible to do this for an individual axis.

Sub ClearAllFilters()
   Dim pTable As OWC10.PivotTable
   Dim pTableView As OWC10.PivotView
   Dim pFieldset As OWC10.PivotFieldSet
   Dim pfield As OWC10.PivotField
    
   DoCmd.OpenForm "Invoices", acFormPivotTable
   Set pTable = Forms("Invoices").PivotTable
   Set pTableView = pTable.ActiveView
   For Each pFieldset In pTableView.FieldSets
        pFieldset.AllIncludeExclude = plAllDefault
    Next
End Sub

PivotTable Events

Events can be used to make PivotTable reports dynamic and interactive. As you interact with the elements of a PivotTable report or with external items such as toolbars or command buttons, Access triggers events that can launch your own custom code. For example, the PivotTableChange event fires when a PivotTable list field, field set, or total is added or deleted from the Pivot Field List. You might add custom code to this event that logs each change in a separate table as part of an accounting audit trail. Or you might add custom code that performs data validation as data is added to the PivotTable report. The following sections describe the most commonly used PivotTable events available in Access. In addition, there is also a section describing how to interact with other PivotTable events not exposed directly in Access.

Note   Some of these events also apply to PivotChart report. For more information on PivotChart reports, see the topic About Charts in Access help.

ViewChange

The ViewChange event applies to both PivotTable reports and PivotChart reports and fires whenever the PivotTable or ChartSpace objects are redrawn.

Note   Performing a single operation may cause objects within a PivotTable report or chart to redraw multiple times, which will cause this event to fire multiple times for that particular operation.

Private Sub Form_ViewChange(ByVal Reason As Long)

The Reason argument returns a PivotViewReasonEnum constant that indicates how the view was changed. However, this argument only applies to PivotTable reports. This argument always returns -1 if using this event with a ChartSpace object. In an Access form, you could use this event to detect when a user switched into PivotTable view from another form view. This event cannot be cancelled.

To take advantage of Intellisense® in the Visual Basic Editor, you can declare a variable as OWC10.PivotViewReasonEnum. This allows you to see a list of all valid constants when using the variable. The following sample code detects when the PivotTable report's details have been hidden and prints out "Hide Details" to the immediate window.

Private Sub Form_ViewChange(ByVal Reason As Long)
   Dim rsn As OWC10.PivotViewReasonEnum
   rsn = Reason
   If rsn = plViewReasonHideDetails Then
      Debug.Print "Hide Details"
   End If
End Sub

SelectionChange

The SelectionChange event applies to both PivotTable reports and PivotChart reports, and fires whenever the user makes a new selection within the object. The user cannot cancel this event. You can use the SelectionType property to determine the object type of the current selection.

Private Sub Form_SelectionChange()

The SelectionChange event accepts no arguments and cannot be canceled. The following example demonstrates how to use the SelectionChange event and the SelectionType property to determine the current selection. If the current selection is an aggregate, the event procedure returns a MsgBox with the aggregate's current value.

Private Sub Form_SelectionChange()
   Dim pAgg As OWC10.PivotAggregate
   If Me.PivotTable.SelectionType = "PivotAggregates" Then
      Set pAgg = Me.PivotTable.Selection(0)
      MsgBox "Currently selected total is: " & _
         Format(pAgg.Value, "Currency")
   End If
End Sub

DataChange

The DataChange event occurs when data related properties are changed or when certain methods are executed against the PivotTable report. For example, this event would fire if you created a calculated field or total, or if you executed the Refresh command to update the PivotTable report.

Private Sub Form_DataChange(ByVal Reason As Long)

The Reason argument contains a value from the PivotDataReasonEnum enumeration. You can use this value to determine what property was modified or method was executed. The following sample code uses the Reason argument to determine if the user modified a calculated expression in the PivotTable report.

Private Sub Form_DataChange(ByVal Reason As Long)
   Dim rsn As OWC10.PivotDataReasonEnum
   rsn = Reason
    
   If rsn = plDataReasonExpressionChange Then
      MsgBox "Just changed an expression."
   End If
End Sub

PivotTableChange

The PivotTableChange event occurs when a PivotTable list field, field set, or total is added or deleted from the Pivot Field List.

Private Sub Form_PivotTableChange(ByVal Reason As Long)

The Reason argument contains a value from the PivotTableReasonEnum enumeration. You can use this value to determine what action was taken with regard to adding or removing Totals, FieldSets, and Fields. The following sample code uses the Reason argument to determine if the user added or removed a Total from the Totals area in the Field List.

Private Sub Form_PivotTableChange(ByVal Reason As Long)
   Dim rsn As OWC10.PivotTableReasonEnum
   rsn = Reason
   If rsn = plPivotTableReasonTotalAdded Then
      MsgBox "Just added a Total."
   ElseIf rsn = plPivotTableReasonTotalDeleted Then
      MsgBox "Just deleted a Total."
   End If
End Sub

OnConnect

The OnConnect event occurs when the PivotTable list connects to a data source.

Private Sub Form_Connect()

This event procedure does not accept any arguments and is not cancelable. You can use this event to do something once the PivotTable report has connected to the data source. Note that this event will also fire when opening a form in PivotChart view, since PivotChart reports are built on data provided by the PivotTable report of a form.

Private Sub Form_OnConnect()
    MsgBox "The PivotTable connected to the data source."
End Sub

OnDisconnect

The OnDisconnect event occurs when the PivotTable report is explicitly disconnected from a data source, or the connection is changed to a different data source.

Private Sub Form_OnDisconnect()

This event procedure does not accept any arguments and is not cancelable. You can use this event to do something once the PivotTable report has disconnected from the data source, or the connection has changed to a different data source. Note that closing a form will NOT cause the OnDisconnect event to fire. However, setting the Recordsource or Recordset properties will cause this event to fire.

Private Sub Form_OnDisconnect()
   MsgBox "Disconnected from the data source."
End Sub

BeforeQuery

The BeforeQuery event occurs when the PivotTable report queries its data source.

Private Sub Form_BeforeQuery()

This event procedure does not accept any arguments and is not cancelable. Note that this event occurs quite frequently. Some examples of actions that trigger this event include adding fields to the PivotTable list, moving fields, sorting, or filtering data. Unfortunately, the object model does not provide a way to determine what query is going to be performed.

Query

The Query event occurs whenever the PivotTable report queries its data source. The query may not occur immediately; it may be delayed until the new data is displayed.

Private Sub Form_Query()

This event procedure does not accept any arguments and is not cancelable. Note that this event occurs quite frequently. Some examples of actions that trigger this event include adding fields to the PivotTable list, moving fields, sorting, or filtering data. Unfortunately, the object model does not provide a way to determine what query has been performed.

CommandEnabled

The CommandEnabled event applies to both PivotTable and ChartSpace objects, and fires whenever the enabled state of a menu bar or toolbar command changes. Since the same command is accessible from multiple locations (toolbar, menu bar, short cut menu, and so forth); the event will fire for each individual command. For example, a number of commands are available on the menu bar, toolbar, and shortcut menu of the form. Whenever one of these commands becomes enabled or disabled, the CommandEnabled event will fire at least three times.

Private Sub Form_CommandEnabled(ByVal Command As Variant, ByVal Enabled As Object)

The Command argument returns a PivotCommandId constant, so you can determine which command has been enabled or disabled.

The Enabled argument is an object, whose Value property indicates whether the command is enabled or disabled.

The following sample code demonstrates how to tell if the Expand command has been enabled or disabled.

Private Sub Form_CommandEnabled(ByVal Command As Variant,_
                                ByVal Enabled As Object)
   Dim cmd As OWC10.PivotCommandId
   Dim byRefEnabled As OWC10.ByRef
   cmd = Command
   Set byRefEnabled = Enabled
   If cmd = plCommandExpand Then
      If byRefEnabled.Value = True Then
         Debug.Print "Expand command enabled."
      Else
         Debug.Print "Expand command disabled."
      End If
   End If
End Sub

CommandChecked

The CommandChecked event applies to both PivotTable and ChartSpace objects, and fires whenever a menu bar or toolbar command becomes checked or unchecked (for a toolbar command, the state of the toolbar button would depressed or not depressed). Like the CommandEnabled event, this event can fire multiple times for the same command since the same command can be located in multiple locations (toolbar, menu bar, short cut menu, etc); the event will fire for each individual command. For example, the Sort Ascending command is available on the menu bar, toolbar, and shortcut menu of the form. Whenever the Sort Ascending command becomes checked or unchecked, the CommandChecked event will fire at least three times.

Private Sub Form_CommandChecked(ByVal Command As Variant, ByVal Checked As Object)

The Command argument returns a ChartCommandIdEnum constant, so you can determine which command has been checked or unchecked.

The Checked argument is an object, whose Value property indicates whether the command is checked or unchecked.

The following sample code demonstrates how to tell if the Sort Ascending command has been checked or unchecked.

Private Sub Form_CommandChecked(ByVal Command As Variant, _
                                ByVal Checked As Object)
   Dim cmd As OWC10.PivotCommandId
   Dim byRefChecked As OWC10.ByRef
   cmd = Command
   Set byRefChecked = Checked
   If cmd = plCommandSortAsc Then
      If byRefChecked.Value = True Then
         Debug.Print "Sort Ascending command is checked."
      Else
         Debug.Print "Sort Ascending command unchecked."
      End If
   End If 
End Sub

CommandBeforeExecute

The CommandBeforeExecute event applies to both PivotTable and ChartSpace objects, and fires when a user selects a command from the menu bar or toolbar, but before the action associated with the command takes place. You can use this event to cancel the command selected or to impose certain restrictions before a command is executed. For example, you can prevent the user from changing the chart type or executing other commands from the menu bar or toolbar.

Private Sub Form_CommandBeforeExecute(ByVal Command As _

Variant, ByVal Cancel As Object)

The Command argument returns a PivotCommandId constant, so you can determine which command the user wishes to execute.

The Cancel argument returns an object, whose Value property you can use to disable the command. By setting the Value property of the Cancel argument to True, the command will not be executed.

The following sample code demonstrates how to prevent the user from executing the Show Details command.

Private Sub Form_CommandBeforeExecute(ByVal Command As_
                        Variant, ByVal Cancel As Object)
   Dim cmd As OWC10.PivotCommandId
   cmd = Command
   If cmd = plCommandShowDetails Then
      MsgBox "You are not permitted to view details."
      Cancel.Value = True
   End If
End Sub

CommandExecute

The CommandExecute event applies to both PivotTable and ChartSpace objects, and fires after a user selects a command from the menu bar or toolbar. Similar to the CommandBeforeExecute event, this event fires only after the action associated with selecting the command has taken place. You can use this event when you want to execute a set of commands after a particular command is executed.

Private Sub Form_CommandExecute(ByVal Command As Variant)

The Command argument returns a PivotCommandId constant, so you can determine which command the user executed. This event cannot be cancelled.

The following sample code checks to see if the user clicked the Sort Ascending command in the PivotTable report. If the command was executed, you receive a MsgBox indicating you have sorted in ascending order.

Private Sub Form_CommandExecute(ByVal Command As Variant)
   Dim cmd As OWC10.PivotCommandId
   cmd = Command
   If cmd = plCommandSortAsc Then
      MsgBox "Sorted Ascending"
   End If
End Sub

Accessing Other PivotTable Events in Access Forms

The PivotTable object defines other events that are not exposed in Access. For the most part, these are not necessary because Access already defines comparable events for forms. For example, there are Mouse and Key events that are identical to the form's Mouse and Key events.

Even though not all PivotTable events are exposed in Access forms, it is still possible to utilize them in Access. You can do this by using an "event sink" in the form's class module.

To do this, you can declare a module level variable as an OWC.PivotTable object, using the WithEvents keyword. The WithEvents keyword exposes a Component Object Model (COM) object's events into a custom class module. In the form's Open event, we can set the module level variable to the form's PivotTable property. This allows our module level variable to expose all the events defined by the PivotTable object, rather than being limited to what is exposed in Access. For example, the following code enables the event sink, and is using the PivotTable object's KeyDown event, which is not exposed in Access. This example simply shows the Properties window if the user presses CTRL+Q.

Option Compare Database
Option Explicit
Private WithEvents pTable As OWC10.PivotTable

Private Sub Form_Open(Cancel As Integer)
   'Only set the variable if the form is
   'in PivotTable view
   If Me.CurrentView = acCurViewPivotTable Then
      Set pTable = Me.PivotTable
   End If
End Sub

Private Sub pTable_KeyDown(ByVal KeyCode As Long, _
                                           ByVal Shift As Long)
   If Shift = 2 Then 'CTRL key is pressed
      If KeyCode = 81 Then  'Q is pressed
         Me.PivotTable.DisplayPropertyToolbox = True
      End If
   End If
End Sub

Conclusion

In this article, we have explored the elements of a PivotTable report. We demonstrated several properties, objects, collections, and methods for building and manipulating PivotTable reports programmatically. We also looked at the event model for PivotTable reports. By using the techniques and the same code described in this article, you can add additional functionality to your applications utilizing PivotTable reports.

Appendix: PivotTable Report Properties

PropertyName	Description
ActiveData	Returns a PivotData object that represents the data in the active PivotTable list.
ActiveObject	Returns or sets an Object that represents the selected cell in the detail area of the PivotTable list. Use the Value property of the returned object to return the value of the selected cell.
ActiveView	Returns a PivotView object that represents the layout of the active PivotTable list. Setting a variable to the active view of the PivotTable report provides you with a convenient method to make changes to the active view.
AllowCustomOrdering	Returns or sets whether the user can reorder row axis or column axis members. Set this property to False to prevent users from row axis or column axis member reordering. The default value is True. Read/write Boolean.
AllowDetails	Specifies whether the user can expand an inner member of the specified PivotTable list to display detail records. The default value is True. Read/write Boolean.
AllowFiltering	Determines whether a field can be added to or removed from the filter area of a PivotTable list, and whether the AutoFilter command on the toolbar is enabled. The default value is True. Read/write Boolean.
AllowGrouping	Determines if the user is able to group fields on the row axis or the column axis in the specified PivotTable list. If set to False, the grouping buttons are disabled but the user can still group fields programmatically. The default value is True. Read/write Boolean.
AllowPropertyToolbox	Determines whether the user can display the Commands and Options dialog box at run time. Setting the AllowPropertyToolbox property to False disables the Commands and Options button on the toolbar. The default value is True. Read/write Boolean.
AutoFit	Determines if the number of visible columns and rows should calculate the overall size of the PivotTable list. The default value is True. If set to False, the overall size of the PivotTable list is set based on its Width and Height properties. Read/write Boolean.
BackColor	Returns or sets the background color for the specified object or area. This property can be set using either a Long value representing a red-green-blue (RGB) color value, or a String value representing a valid HTML color value. For example, to set the object color to red, you could use the Hexadecimal value &HFF, the Decimal value 255, or the String value "red." In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (for example, red is RGB(255,0,0)). Read/write Variant.
BuildNumber	Returns the Microsoft Office Web Components build number. Read-only String.
CommandText	Returns or sets the command for the PivotTable report. Read/write String.
Commands	Returns a collection that represents the collection of commands available.
Connection	Returns or sets the ADO Connection object used by the specified object. Read/write for the PivotTable object.
ConnectionString	Returns or sets the ADO connection string for a two-tier database connection. Read/write String.
Constants	Returns an object that allows Microsoft Visual Basic Script (VBScript) programmers to use named constants. This property applies to each of the top-level container objects (ChartSpace, DataSourceControl, PivotTable, and Spreadsheet). It returns an object that contains all of the named constants available in the Microsoft Office Web Components type library (no matter which object the Constants property is applied to, it always returns the complete set of named constants).
DataMember	Returns or sets the data member name (the name of the Recordset that the specified control will request from the data source). Read/write DataMember.
DataSource	Returns or sets the ADO DataSource object that represents the data source for the specified control.
DataSourceName	Returns or sets a String specifying the name of the ActiveX control that serves as the data source for the CHART control. This property can be used in containers that support the Microsoft Internet Explorer Document Object Model. Read/write String.
DisplayAlerts	Determines if certain alerts and messages are to be displayed while code is running. The default value is True. Set this property to False if you do not want to be disturbed by prompts and alert messages; any time a message requires a response, the default response is chosen. If you set this property to False, it is not automatically set back to True, and must be set to True for messages and alerts to appear. Read/write Boolean.
DisplayDesignTimeUI	Determines whether the design-time version of the Commands and Options dialog box is displayed at run time. The default value is False. Read/write Boolean.
DisplayExpandIndicator	Determines if expansion indicators are displayed for members with available child members or detail records. When the expansion indicator is hidden, the member display name appears in this space. The default value is True. Read/write Boolean.
DisplayFieldList	Determines if the Field List is displayed. The default value is True. Read/write Boolean.
DisplayOfficeLogo	Determines whether the Microsoft Office logo should be displayed on the toolbar. Read/write Boolean.
DisplayPropertyToolbox	Determined if the Command and Options dialog box should be displayed. Read/write Boolean.
DisplayScreenTips	Returns or sets whether ScreenTips are displayed. Set this property to False to prevent the display of ScreenTips. The default value is True. Read/write Boolean.
DisplayToolbar	Determines if the toolbar is displayed. The default value is False. The Access toolbar shows the appropriate commands already, however, you can display the toolbar within the component. Read/write Boolean.
EditMode	Returns a PivotEditModeEnum constant that indicates whether the PivotTable list is currently in edit mode. Read-only.
HTMLData	Returns a String that represents the specified PivotTable list or range as a properly formatted HTML String. Read-only.
HasDetails	Determines if detail records can be displayed for a given cell. This property is automatically reset whenever the data is requeried. If this property is set to False, the expansion indicators are not displayed for inner members. Read-only Boolean.
Height	Returns or sets the height of the specified object in points. Read-write Long.
Hwnd	Returns a Long indicating the top-level window handle of the PivotTable control's window. Read-only.
IsDirty	Returns or sets a Boolean that indicates whether the PivotTable list has changed since the last time it was saved. Read/write.
Left	Returns a Long that represents the left edge of the specified object. Read/write.
MajorVersion	Returns the major version of the Microsoft Office Web Components object library. Read-only Long.
MaxHeight	Returns or sets the maximum height in pixels that the specified PivotTable list can attain when the value of the AutoFit property is True. A vertical scroll bar is displayed if there is more data available that cannot be displayed within the maximum height. The default value is 32,000 pixels. Read/write Long.
MaxWidth	Returns or sets the maximum width in pixels that the specified PivotTable list can attain when the value of the AutoFit property is True. A horizontal scroll bar is displayed if there is more data available that cannot be displayed within the maximum width. The default value is 32,000 pixels. Read/write Long.
MinorVersion	Returns the minor version of the Microsoft Office Web Components object library. Read-only String.
ProviderType	Returns a ProviderType constant that represents the type of data provider for the specified PivotTable list. Read-only.
RevisionNumber	Returns the Microsoft Office Web Components revision number. Read-only String.
RightToLeft	Determines if right-to-left language support is enabled. For example, setting this property to True causes scroll bars to be displayed on the left. Read/write Boolean.
Selection	Returns a collection that represents the selected objects. Use the Count property to determine the number of selected objects. Use the Value property to return the value of a particular object.
SelectionType	Returns a String that represents the type of object currently selected in the PivotTable list. Read-only.
Top	Returns (or sets, depending on object used) a Long that represents the top edge of the specified object. Read/write.
UserMode	Returns a Boolean that indicates whether the PivotTable list is in view-only mode. Read-only.
Version	Returns the Microsoft Office Web Components version. Read-only String.
ViewOnlyMode	Determines if the Microsoft Office Web Components are in view-only mode. The Web Components will be in view-only mode if the user does not have the appropriate license installed on their computer. Read-only Boolean.
Width	Returns or sets a Long that represents the width of the specified object. Read/write.
XMLData	Returns or sets the XML data. Read/write String.