Share this Page URL

Chapter 11. Analyzing Data with PivotTab... > Using Visual Basic to Create a Pivot... - Pg. 409

Analyzing Data with PivotTables and PivotCharts .DataAxis.InsertFieldSet fset Set pvtfld = .FieldSets("AmountSpent")Fields("AmountSpent") .AddTotal "SumofAmountSpent", pvtfld, plFunctionSum Set totl = .Totals("SumofAmountSpent") .DataAxis.InsertTotal totl End With frm.PivotTable.DisplayFieldList = False Set frm = Nothing End Sub 409 The first several statements in the procedure declare the object variables the procedure needs: an Access form object; a PivotFieldSet object, which represents the fields used in a PivotTable; a PivotField object, which represents a single field in a PivotTable; and a PivotTotal object, which represents a field in a PivotTable that displays aggregate data (a sum, an average, a count of records, and so on). The procedure also declares two string variables. The first, strSourceName, is used to hold the SQL expression that defines the new form's record source. The second, frmName , is used to hold the name of the form the procedure creates. The SQL statement that's assigned to strSourceName selects the fields Country, Expense- Type, and AmountSpent from the ExpenseReport query. The procedure then uses Create- Form, a method of the Access Application object. In the statement Set frm = CreateForm , the form that's created by the CreateForm method is assigned to the variable frm . The variable frmName is assigned the value of the new form's Name property, which at this point would be Form1, the default name given to a new form by Access. The first DoCmd statement, which uses the OpenForm method, opens the form in PivotTable view. The variable frm is then set again by assigning it a reference to the open form using the variable frmName . The code next sets the form's RecordSource property using the SQL statement held in the variable strSour- ceName and the form's Caption property, which will display Expenses_PivotTable in the form's title bar. The procedure then calls the Save method to save the form. In the With... End With statement, the procedure builds the PivotTable, using various proper- ties and methods of a PivotTable object. The ActiveView property returns a PivotView object that represents the PivotTable's layout. Each field in the form's record source is referred to through the FieldSets property and assigned to the variable fset . The PivotTable drop areas are represented by the ColumnAxis , RowAxis , and DataAxis properties. The procedure uses the InsertFieldSet method to assign, in turn, Country as the PivotTable's column field, Expen- seType as its row field, and AmountSpent as its data field. To complete the PivotTable, we'd like to show summary data as well as individual expense items. To do this, the procedure uses the AddTotal and InsertTotal methods. The procedure first sets a reference (assigned to the variable pvtfld ) to the field containing the data we want to summarize--in this case, AmountSpent. In the statement . AddTotal "SumofAmountSpent", pvtfld, plFunctionSum, the procedure calls the AddTotal method, provides a name for the totals field, uses the variable pvtfld to indicate the field being totaled, and then refers to the Sum function so that the amounts are totaled. (You could also refer to other of the standard ag- gregate functions in this argument, such as Count or Average .) In the last two statements, the code assigns a reference to the new totals field to the variable totl and then uses the InsertTotal method to add the field to the PivotTable's data area. At the end of the procedure, the code hides the PivotTable field list. Someone viewing the PivotTable can move the column and row fields to pivot the data, but the field list isn't required to work with this PivotTable, which is meant only for viewing data temporarily. Open the ExpenseViews form in Form view, select PivotTable in the View Expenses By option group, and then click the View Expenses button. You'll see that Access creates the new form (it will be minimized at the bottom of screen) and then displays the PivotTable shown here: 4.