Microsoft KB Archive/326492

= HOW TO: Programmatically Add PivotTable Groupings =

Article ID: 326492

Article Last Modified on 6/29/2007

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Web Components

-



This article was previously published under Q326492



IN THIS TASK
SUMMARY
 * The AddCustomGroupField and AddCustomGroupMember Methods
 * Step-by-Step

REFERENCES



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(&quot;FoodType&quot;) Dim oPF 'OWC10.PivotField types Dim oBaked, oDairy, oJuices 'OWC10.PivotMember types Set oPF = oFldSet.AddCustomGroupField(&quot;FoodSubCategory&quot;, &quot;FoodSubCategory&quot;) Set oBaked = oPF.AddCustomGroupMember(oFldSet.Member, _               Array(&quot;Cake&quot;, &quot;Pie&quot;), &quot;Baked Goods&quot;) Set oDairy = oPF.AddCustomGroupMember(oFldSet.Member, _               Array(&quot;Ice Cream&quot;, &quot;Shakes&quot;), &quot;Dairy&quot;) Set oJuices = oPF.AddCustomGroupMember(oFldSet.Member, _               Array(&quot;Orange Juice&quot;, &quot;Apple Juice&quot;, &quot;Cranberry Juice&quot;), _                &quot;Juices&quot;) 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(&quot;FoodCategory&quot;, &quot;FoodCategory&quot;) Set oDesserts = oPF.AddCustomGroupMember(oFldSet.Member, _                             Array(oBaked, oDairy), &quot;Desserts&quot;) Set oBeverages = oPF.AddCustomGroupMember(oFldSet.Member, _                             Array(oJuices), &quot;Beverages&quot;) 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:

&quot; id=&quot;PTable&quot;>



'Connect to a data source and add fields to the row and data axes. Dim oView 'OWC10.PivotView type PTable.ConnectionString = _ &quot;Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=YOURSERVER&quot; PTable.CommandText = &quot;Select * From Orders Where CustomerID<='BONAP' and Freight>=50&quot; Set oView = PTable.ActiveView oView.RowAxis.InsertFieldSet oView.FieldSets(&quot;CustomerID&quot;) oView.DataAxis.InsertFieldSet oView.FieldSets(&quot;Freight&quot;) 'Set a variable to the field set. Dim oFldSet 'OWC10.PivotFieldSet Type Set oFldSet = oView.FieldSets(&quot;CustomerID&quot;) 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(&quot;Level3&quot;, &quot;Level3&quot;, 0) Set oA1 = oLevel3PF.AddCustomGroupMember(oFldSet.Member, _                                   Array(&quot;ALFKI&quot;, &quot;ANTON&quot;), &quot;Customers A.1&quot;) Set oA2 = oLevel3PF.AddCustomGroupMember(oFldSet.Member, _                                   Array(&quot;AROUT&quot;), &quot;Customers A.2&quot;) Set oB1 = oLevel3PF.AddCustomGroupMember(oFldSet.Member, _                                   Array(&quot;BERGS&quot;, &quot;BLAUS&quot;, &quot;BLONP&quot;), &quot;Customers B.1&quot;) Set oB2 = oLevel3PF.AddCustomGroupMember(oFldSet.Member, _                                   Array(&quot;BOLID&quot;, &quot;BONAP&quot;), &quot;Customers B.2&quot;) 'Add a custom group field for Level2 Set oLevel2PF = oFldSet.AddCustomGroupField(&quot;Level2&quot;, &quot;Level2&quot;, 0) Set oA = oLevel2PF.AddCustomGroupMember(oFldSet.Member, Array(oA1, oA2), &quot;A&quot;) Set oB = oLevel2PF.AddCustomGroupMember(oFldSet.Member, Array(oB1, oB2), &quot;B&quot;) 'Add a custom group field for Level1 Set oLevel1PF = oFldSet.AddCustomGroupField(&quot;Level1&quot;, &quot;Level1&quot;, 0) oLevel1PF.AddCustomGroupMember oFldSet.Member, Array(oA, oB), &quot;Complete&quot; Note Replace the  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  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 = _ &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;&quot;  View the Web page in your browser.  Notice that the following group fields have been added:

<ul> Level1</li> Level2</li> Level3</li></ul>

The layout for the new groups resembles the following: <pre class="fixed_text">[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 </li></ol>

back to the top

<div class="references_section">