Adding Calculated Fields and Items to a Pivot Table

You can customize a PivotTable report by defining calculated fields and items. Using the contents of other numeric fields in a PivotTable, you can create a calculated field that performs the required calculation. For example, the procedure demonstrated below creates two calculated fields named Change: 2001/2000 and Change: 2000/1999 to calculate the difference in number of products sold from year to year. Figure A-8 shows the source data and the PivotTable generated by the procedure listed below.

Sub PivotWithCalcFields()

ActiveWorkbook.PivotCaches.Add( _ SourceType:=xlDatabase, _

SourceData:="Sheet1!R1C1:R4C4").CreatePivotTable _ TableDestination:="'[PivotFields.xls]Sheet1'!R4C7", _ TableName:="Piv1", _ DefaultVersion:=xlPivotTableVersion10 With ActiveSheet.PivotTables("Piv1").PivotFields("Product") .Orientation = xlRowField .Position = 1 End With

ActiveSheet.PivotTables("Piv1").AddDataField _

ActiveSheet.PivotTables("Piv1").PivotFields("2001"), _ "Sum of 2001", xlSum ActiveSheet.PivotTables("Piv1").AddDataField _

ActiveSheet.PivotTables("Piv1").PivotFields("2000"), _ "Sum of 2000", xlSum ActiveSheet.PivotTables("Piv1").AddDataField _

ActiveSheet.PivotTables("Piv1").PivotFields("1999"), _ "Sum of 1999", xlSum ActiveSheet.PivotTables("Piv1").CalculatedFields.Add _

"Change: 2001/2000", "='2001' -'2000'", True ActiveSheet.PivotTables("Piv1").CalculatedFields.Add _

"Change: 2000/1999", "='2000' -'1999'", True ActiveSheet.PivotTables("Piv1"). _

PivotFields("Change: 2001/2000"). _ Orientation = xlDataField ActiveSheet.PivotTables("Piv1"). _

PivotFields("Change: 2000/1999"). _ Orientation = xlDataField

End Sub

Notice that calculated fields are defined by using the Add method of the CalculatedFields object and supplying two arguments: the name for the new field and a formula.

ActiveSheet.PivotTables("Piv1").CalculatedFields.Add _ "Change: 2001/2000", "='2001' -'2000'", True

ActiveSheet.PivotTables("Piv1").CalculatedFields.Add _ "Change: 2000/1999", "='2000' -'1999'", True

The third (optional) argument set to True indicates that the strings in field names will be interpreted as having been formatted in standard U.S. English instead of using local settings. The default setting is False.

Sjf PivotFields. h!s

Pñr

4

A

C

D

ë

F | lj" | H I

1

Product

2000

2(1(1-1

2

Prodi

soi

M 4

694

3

Piod2

456

139!

¡■55

4

ProcH3

1522

1009

1002

Product t

Data v

Total

S

Prodi

Sum of 2001

634

6

Sum oí 2000

6! 4

7

Sum of 1999

904

8

Sum o_f_Change: 2001/2000

30

a

Sum of-Change: 2000/1999

-29Ü

10

Prod2

Sum of 2001

755

11

Sum of 2000

139

ii1

Sum of 1999

456

13

Sum o_tChange: 2001 /2000

616

14

Sum of Change: 2000.1999

-3ÎZ

15

Prod3

Sum of 2001

1002

16

Sum of 2000

1009

17

Sum of1999

1522-

18

Sum ofChange: 2001 /2000

-7

10

Sum of Change: 2000/1999

t51 3

20

Total Sum of 2001

2451

21

Total Sum of 2000

1762

22

Total Sum of 1999

2882

23

Total Sum of .Chande: 2001/2000

689

24

Total Sum of'Change: 2000/1999

-1120

H <

1 h « Sheet! jj Sheet2 / Sfoáetí/ ! < ] ■ | | |

Figure A-8: You can add additional calculations to a PivotTable by defining additional fields, such as Change: 2001/2000 and Change: 2000/1999 depicted here.

A calculated field uses a formula that refers to other Pivot fields that contain numeric data. This can be a simple formula, such as addition (+), subtraction (-), multiplication (*), and division (/), or an Excel function. In the PivotWithCalcFields procedure example, we created two calculated fields:

Calculated Field Name Formula Change: 2001/2000 ='2001'-'2000' Change: 2000/1999 ='2000'-'1999'

"2001," "2000," and "1999" are the names of the fields placed in the Data area of the PivotTable. When you use multiple Pivot fields in the Data area, Excel creates a new Pivot field named Data (Figure A-8). The labels for the multiple Pivot fields in the Data area can be displayed going down the rows (as shown in Figure A-8) or across columns. You can specify the orientation of the labels by setting the Orientation property of the Data field to xlRowField or xlColumnField. The following statement:

ActiveSheet.PivotTables("Piv1"). _

PivotFields("Data").Onentation = xlColumnField will modify the PivotTable in Figure A-8 to look like this:

Once you define a calculated field, the field is added to the PivotTable field

Dala -F-

P roduct *

Sum of 2001

Sum of 2000 Sum

of 1999 Sum

of Change: 2001/2000

Sum

of Change 2000/1 999

Prodi

694

614

904

80

-290

P rotC

755

139

456

616

-317

Prod3

1002

1009

1522:

-7

-513

Grand Total

2451

1762

2662!

689

-1120

list and maintained in the PivotTable cache.

list and maintained in the PivotTable cache.

Note: You can add a calculated field manually by using the PivotTable toolbar. Click PivotTable, point to Formulas, and click Calculated Field.

You must not confuse a calculated item with a calculated field. A calculated item is a custom item you define in a PivotTable field to perform calculations using the contents of other fields and items in the PivotTable. Let's say you have created a report showing the total product sales for each of your salespeople by country. Then you want to look at the data differently and show the sales made by each salesperson on three continents. You will need three new (calculated) items under the Country field. These items will be named North America, South America, and Europe. After you create these items, you can change the name of the Country field to Continent (see Figure A-9) to make your data easier to read. The following procedure retrieves the data for this demonstration example from the Microsoft Access sample Northwind database. The code of this procedure was generated by a macro recorder. You may need to change it to point to a valid location of the example database on your computer.

Sub PivotWithCalcItems() Dim strConn As String Dim strSQL As String Dim myArray As Variant Dim destRng As Range Dim strPivot As String strConn = "Driver={Microsoft Access Driver (*.mdb)};" &_

"DBQ=" & "C:\Program Files\Microsoft Office\Office10\" & "Samples\Northwind.mdb;"

strSQL = "SELECT Invoices.Customers.CompanyName, " & _

"Invoices.Country, Invoices.Salesperson, " & _ "Invoices.ProductName, Invoices.ExtendedPrice " & _ "FROM Invoices ORDER BY Invoices.Country"

myArray = Array(strConn, strSQL) Worksheets.Add

Set destRange = ActiveSheet.Range("B5") strPivot = "PivotTable1"

ActiveSheet.PivotTableWizard _

SourceType:=xlExternal, _ SourceData:=myArray, _ TableDestination:=destRange, _ TableName:=strPivot, _ SaveData:=False, _ BackgroundQuery:=False

With ActiveSheet.PivotTables(strPivot).PivotFields("CompanyName") .Orientation = xlPageField .Position = 1 End With

With ActiveSheet.PivotTables(strPivot).PivotFields("Country") .Orientation = xlRowField .Position = 1 End With

ActiveSheet.PivotTables(strPivot).AddDataField _

ActiveSheet.PivotTables(strPivot).PivotFields("ExtendedPrice"), "Sum of ExtendedPrice", xlSum With ActiveSheet.PivotTables(strPivot).PivotFields("Salesperson") .Orientation = xlRowField .Position = 1 End With

Range("A6").Select

With ActiveSheet.PivotTables(strPivot).PivotFields("Salesperson") .Orientation = xlPageField .Position = 1 End With

Range("A3").Select

With ActiveSheet.PivotTables(strPivot).PivotFields("Salesperson") .Orientation = xlColumnField .Position = 1

End With

Range("A6").Select

ActiveSheet.PivotTables(strPivot).PivotFields("Country").CalculatedItems.

Add "North America", "=USA+Canada", True ActiveSheet.PivotTables(strPivot).PivotFields("Country").CalculatedItems.

Add "South America", "=Argentina+Brazil+Venezuela", True ActiveSheet.PivotTables(strPivot).PivotFields("Country").CalculatedItems(

"North America").StandardFormula = "=USA+Canada+Mexico" ActiveSheet.PivotTables(strPivot).PivotFields("Country").CalculatedItems. Add "Europe", _

"=Austria+Belgium+Denmark+Finland+France+Germany+Ireland+Italy" & _ "+Norway+Poland+Portugal+Spain+Sweden+Switzerland+UK", True

With ActiveSheet.PivotTables(strPivot).PivotFields("Country") .PivotItems("Argentina").Visible = False .PivotItems("Austria").Visible = False .PivotItems("Belgium").Visible = False .PivotItems("Brazil").Visible = False .PivotItems("Canada").Visible = False .PivotItems("Denmark").Visible = False .PivotItems("Finland").Visible = False .PivotItems("France").Visible = False .PivotItems("Genrany").Visible = False .PivotItems("Ireland").Visible = False .PivotItems("Italy").Visible = False .PivotItems("Mexico").Visible = False .PivotItems("Norway").Visible = False .PivotItems("Poland").Visible = False .PivotItems("Portugal").Visible = False .PivotItems("Spain").Visible = False .PivotItems("Sweden").Visible = False .PivotItems("Switzerland").Visible = False .PivotItems("UK").Visible = False .PivotItems("USA").Visible = False .PivotItems("Venezuela").Visible = False End With

Range("A6").Select

ActiveSheet.PivotTables(strPivot).PivotFields("Country").Caption = _

"Continent" Range("A5").Select

With ActiveSheet.PivotTables(strPivot). _

PivotFields("Sum of ExtendedPrice").NumberFormat = "$#,##0.00" End With

With ActiveSheet.PivotTables(strPivot).PivotFields("ProductName") .Orientation = xlRowField .Position = 2 End With

Range("B6").Select

ActiveSheet.PivotTables(strPivot). _ PivotFields("ProductName").Orientation = xlHidden End Sub

A calculated item uses a formula that refers to other items in the specified PivotTable field. For example, a PivotTable that contains a Country field listing a number of different country items (Austria, UK, Brazil, Argentina, etc.) could have a calculated item named "South America" defined as the sum of countries located on the South American continent:

Calculated Item Formula

South America =Argentina+Brazil+Venezuela

All the calculated items in the specified PivotTable are members of the CalculatedItems collection. Calculated items are defined by using the Add method of the Calculatedltems object and supplying two arguments: the name for the new item and a formula:

ActiveSheet.PivotTables(strPivot).PivotFields("Country").CalculatedItems. _ Add "South America", "=Argentina+Brazil+Venezuela", True

The third (optional) argument set to True indicates that the strings in field names will be interpreted as having been formatted in standard U.S. English instead of using local settings. The default setting is False.

I □ Microsoft Excel ■ Pivotl terns, ids

HHDl

®

File Edit ¡¿tew

Insert Farmsl Tools Qeta

Window Help

H Type a question For help

• . B x

Q^H a %

m - z v l\ so*

»

EivotTabte'

I t rfS e

Aral CE

10 v B / B *

! % i too

.00 + = + .0 »p* «p-

_ - & - ^

A12

f*

A

B Ç

D

E

F

G

1_kLTl

■1

jtl

5

Sum ot ExtendedPrlce

SatKjcersfli^l _________ _____ _______________________

6

'I jrilii itrit

Andrew fuller Anne Ood&wortri Ji

flnrt Leverllnq

Laura Caïahan

Mftrctflret Paatock

Nfchael Suvama

Nancy Da

7 S

North America

131,868.50 »18,191.51 $13,428.51 (3,232.50

»48,798.09

»28,022.68

$59,421 «3

$21,229.78

$57,71—

Siiuth America

$20,758.1 S

123,769.30

$27,20952

$11,894.39 $40,988.96

$33,3;

S 10_

Europe

1121,240.74 »55,864.03

$133,256.57

»75,070.21

$146,259 28

$91,9!

Grand Total

$166,537.75 177,308.04

$202,812.82

$128,862.27

$232,890.63

$73,913.13

$192,1 C

¡1

nr

14 •

i » n\Sheetl/ 5teet2 / 5heet3 /

hi

ftaady

/a

Figure A-9: By defining new items in a PivotTable report, you can present information summaries according to specific needs. Here the Continent field has been renamed from the Country field to present information summarized by continent. North America, South America, and Europe are calculated items in this PivotTable report.

You can modify the PivotWithCalcItems procedure by defining new calculated items in the Salesperson PivotTable field. The following report displays the sales by continent for the Female and Male teams:

S urn of E x te nde dP tic e

Salesperson

Continent ▼

Female Male

Grand Total

North America

$212,144.09 $107,312.50

$319,456.59

S outh. America

$114,368.41 $59,350.50

$173,718.91

Europe

$505,469.02 $267,148.35

$772,617.37

Grand Total

$831,981.52 $433,811.35

$1,265,792.87

You can find out if the PivotField or Pivotltem is calculated by using the IsCalculated property of the PivotField or PivotItem object. The procedure shown below prints a list of fields and items in the PivotTable to the Immediate window, indicating whether the field or item is calculated. In addition, this procedure prints the names of all calculated items and their formulas to an Excel worksheet.

Sub ListCalcFieldsItems()

Dim fld As PivotField ' field enummerator

Dim itm As PivotItem ' item enummerator

Dim r As Integer ' row number

Set pivTable = Worksheets(1).PivotTables(1)

On Error Resume Next

' print to the Immediate window the names of fields ' and calculated items For Each fld In pivTable.PivotFields If fld.IsCalculated Then

fld.Name & vbTab & "-->Calculated field"

Else

Debug.Print fld.Name End If

For Each itm In pivTable. _

PivotFields(fld.Name).CalculatedItems Debug.Print fld.Name & ":" & _

itm.Name & vbTab & "-->Calculated item" ' enter information about Calculated items ' in a worksheet r = r + 1

With Worksheets(2)

.Cells(r, 1).Value = itm.Name .Cells(r, 2).Value = Chr(39) & itm.Formula End With

Next

Next End Sub

0 0

Post a comment