PivotLabelTotals (Collection)

Member of:

PivotLabelTotal object

Description:

Consists of pivot label total objects.

Tip:

All collections have the “Item(NameOrIndex)” method. This is the default method for all collections that returns collection items at a particular index or by name. Use brackets ([]) to represent calls to the Item (Method). For example, these statements are identical: myItem = Documents[1] myItem = Documents.Item(1) myItem = Documents["StartUp.bqy"] myItem = Documents.Item("StartUp.bqy")

Example 1:

This example shows how to create a Totals (Collection) under SideLabel and TopLabel instances:

ActiveDocument.Sections["Pivot"].TopLabels["Product Name"].Totals
ActiveDocument.Sections["Pivot"].SideLabels["Region"].Totals

Example 2:

This example shows how to create an Add() method under each Totals (Collection). The Add() method always creates the SideLabel Totals row or TopLabel Totals column using the default totals data function. The default totals data function is dependent on the Pivot data function setting. To change the totals data function, add a SideLabel Totals row or a TopLabel Totals column and apply the DataFunction property as described in the DataFunction (Property).

ActiveDocument.Sections["Pivot"].SideLabels["Quarter"].Totals.Add()
ActiveDocument.Sections["Pivot"].TopLabels["Region"].Totals.Add()

Example 3:

This example shows how to create an Item() method under each Totals (Collection):

ActiveDocument.Sections["Pivot"].SideLabels["Quarter"].Totals.Item()
ActiveDocument.Sections["Pivot"].TopLabels["Region"].Totals.Item()

Example 4:

This example shows you to remove all totals under each Totals (Collection) using the RemoveAll() method:

ActiveDocument.Sections["Pivot"].SideLabels["Quarter"].Totals.RemoveAll()
ActiveDocument.Sections["Pivot"].TopLabels["Region"].Totals.RemoveAll()

Example 5:

This example shows how to create a Count property under each Totals (Collection):

ActiveDocument.Sections["Pivot"].SideLabels["Quarter"].Totals.Count
ActiveDocument.Sections["Pivot"].TopLabels["Region"].Totals.Count

Example 6:

This example shows how to create a DataFunction property under each instance of the Totals (Collection) that can be assigned the value from the constant group BqDataFunction. The DataFunctions property has the same effect as clicking the ALT key, clicking the total side or top label cell, and selecting a data function from the right-click or Pivot menu. The pivot data functions are as follows: sum, average, count, maximum, minimum, %column, %row, %grand, increase, %increase, non-null average, null count, and non-null count:

ActiveDocument.Sections["Pivot"].SideLabels["Quarter"].Totals[1].DataFunction = BqDataFunction
ActiveDocument.Sections["Pivot"].TopLabels["Region"].Totals[1].DataFunction = BqDataFunction

Methods:

Add(), Item(Value As NameOdIndex) RemoveAll()

Properties:

Read-only: Property Count As Number

Constants:

The PivotLabelTotals collection uses the BqDataFunction constant group. which consists of these values: