HOW TO: Programmatically Add PivotTable Groupings (326492)

SUMMARY

This step-by-step article describes how to programmatically add group fields to the PivotTable component. If you add group fields, you can create your own scheme to group data in a PivotTable report. Custom group fields are useful because they affect only the display of data on the client computer, and they do not affect the actual data source.

back to the top

The AddCustomGroupField and AddCustomGroupMember Methods

To create a group, use the AddCustomGroupField method to add a custom group field to a field set. When you have added the custom group field, use the AddCustomGroupMember method to add pivot members to the group, as in the following sample code:

Dim oFldSet 'OWC10.PivotFieldSet Type
Set oFldSet = PivotTable1.ActiveView.FieldSets("FoodType")
        
Dim oPF 'OWC10.PivotField types
Dim oBaked, oDairy, oJuices 'OWC10.PivotMember types
Set oPF = oFldSet.AddCustomGroupField("FoodSubCategory", "FoodSubCategory")
Set oBaked = oPF.AddCustomGroupMember(oFldSet.Member, _
                Array("Cake", "Pie"), "Baked Goods")
Set oDairy = oPF.AddCustomGroupMember(oFldSet.Member, _
                Array("Ice Cream", "Shakes"), "Dairy")
Set oJuices = oPF.AddCustomGroupMember(oFldSet.Member, _
                Array("Orange Juice", "Apple Juice", "Cranberry Juice"), _
                "Juices")

This sample code creates a new group field that is named FoodSubCategory. This new group field has three members:

Baked Goods
Dairy
Juices

You can use the following sample code to create another group:

Dim oDesserts, oBeverages 'OWC10.PivotMember types
Set oPF = oFldSet.AddCustomGroupField("FoodCategory", "FoodCategory")
Set oDesserts = oPF.AddCustomGroupMember(oFldSet.Member, _
                              Array(oBaked, oDairy), "Desserts")
Set oBeverages = oPF.AddCustomGroupMember(oFldSet.Member, _
                              Array(oJuices), "Beverages")

This example creates another new group field named FoodCategory. This new group field has two members:

Desserts
Beverages

The PivotTable layout resembles the following:


   [FoodCategory]          [FoodSubCategory]            [FoodType]

                                                +-------- Cake
                    +--------- Baked Goods -----+
                    |                           +-------- Pie
                    |
     Desserts ------+
                    |                           +-------- Ice Cream  
                    +--------- Dairy -----------+
                                                +-------- Shakes

                                                +-------- Orange Juice
                                                |
     Beverages -----+--------- Juices ----------+-------- Apple Juice
                                                |
                                                +-------- Cranberry Juice

back to the top

Step-by-Step

To add group fields to a PivotTable, follow these steps. The PivotTable in the following sample is bound to the Northwind sample database. You can use either the Microsoft Access Northwind sample database or the Microsoft SQL Server 2000 Northwind sample database with this code.

Use any text editor or HTML editor to create the following Web page:

<html>

<body>
<object classid="clsid:<Class Identifier of PivotTable Control>" id="PTable">
</object>
</body>

<script language='VBScript'>

    'Connect to a data source and add fields to the row and data axes.
    Dim oView 'OWC10.PivotView type
    PTable.ConnectionString = _
         "Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=YOURSERVER"
    PTable.CommandText = "Select * From Orders Where CustomerID<='BONAP' and Freight>=50"
    Set oView = PTable.ActiveView
    oView.RowAxis.InsertFieldSet oView.FieldSets("CustomerID")
    oView.DataAxis.InsertFieldSet oView.FieldSets("Freight")
    
    'Set a variable to the field set.
    Dim oFldSet 'OWC10.PivotFieldSet Type
    Set oFldSet = oView.FieldSets("CustomerID")
        
    Dim oLevel1PF, oLevel2PF, oLevel3PF 'OWC10.PivotField types
    
    'Add a custom group field for Level3
    Dim oA1, oA2, oB1, oB2 'OWC10.PivotMember types
    Set oLevel3PF = oFldSet.AddCustomGroupField("Level3", "Level3", 0)
    Set oA1 = oLevel3PF.AddCustomGroupMember(oFldSet.Member, _
                                    Array("ALFKI", "ANTON"), "Customers A.1")
    Set oA2 = oLevel3PF.AddCustomGroupMember(oFldSet.Member, _
                                    Array("AROUT"), "Customers A.2")
    Set oB1 = oLevel3PF.AddCustomGroupMember(oFldSet.Member, _
                                    Array("BERGS", "BLAUS", "BLONP"), "Customers B.1")
    Set oB2 = oLevel3PF.AddCustomGroupMember(oFldSet.Member, _
                                    Array("BOLID", "BONAP"), "Customers B.2")
    
    'Add a custom group field for Level2
    Set oLevel2PF = oFldSet.AddCustomGroupField("Level2", "Level2", 0)
    Set oA = oLevel2PF.AddCustomGroupMember(oFldSet.Member, Array(oA1, oA2), "A")
    Set oB = oLevel2PF.AddCustomGroupMember(oFldSet.Member, Array(oB1, oB2), "B")
                                    
    'Add a custom group field for Level1
    Set oLevel1PF = oFldSet.AddCustomGroupField("Level1", "Level1", 0)
    oLevel1PF.AddCustomGroupMember oFldSet.Member, Array(oA, oB), "Complete"
</script>
</html>

Note Replace the <Class Identifier of PivotTable Control> in the above code with the Class ID of the PivotTable control being used.

For Microsoft Office PivotTable 10.0 0002E552-0000-0000-C000-000000000046

Change YOURSERVER in the connection string to a computer that is running SQL Server that contains the Northwind sample database. If you do not have access to a computer that is running SQL Server, you may use the Access Northwind sample database instead, as in the following example:
```
    PTable.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Path to Access Northwind Sample Database>;"
```
View the Web page in your browser.

Notice that the following group fields have been added:

Level1
Level2
Level3

The layout for the new groups resembles the following:

[Level1]   [Level2]        [Level3]          [CustomerID]

                                         +----- ALFKI
                   +--- Customers A.1 ---|         
           +- A ---+                     +----- ANTON
           |       | 
           |       +--- Customers A.2 ---+----- AROUT
Complete --+  
           |
           |                             +----- BERGS
           |                             |
           |       +--- Customers B.1 ---+----- BLAUS
           |       |                     |
           +- B ---+                     +----- BLONP
                   | 
                   |                     +----- BOLID
                   +--- Customers B.2 ---|
                                         +----- BONAP