Pivot Fields

The columns in the data source are referred to as fields. When the fields are used in a PivotTable, they become PivotField objects and belong to the PivotFields collection of the PivotTable object. The PivotFields collection contains all the fields in the data source and any calculated fields you have added, not just the fields that are visible in the PivotTable report. Calculated fields are discussed later in this section.

You can add PivotFields to a report using two different techniques. You can use the AddFields method of the PivotTable object, or you can assign a value to the Orientation property of the PivotField object, as shown here:

Sub

AddFieldsToTable()

'Adds new fields to an existing PivotTable

'Access existing PivotTable

With ActiveSheet.PivotTables(l)

'Add new State field to rows

.AddFields RowFields:="State", AddToTable:=True

'Add Date as new page field

.PivotFields("Date").Orientation = xlPageField

End With

End

Sub

If you run this code on the example PivotTable, you will get the result shown in Figure 7-4.

The AddFields method can add multiple row, column, and page fields. These fields replace any existing fields, unless you set the AddToTable parameter to True, as in the previous example. However, AddFields can't be used to add or replace data fields. The following code redefines the layout of the fields in the existing Table, apart from the data field:

Sub RedefinePivotTable()

'Reorganize an existing PivotTable Dim pvt As PivotTable

'Access existing PivotTable

Set pvt = ActiveSheet.PivotTables(l)

'Specify arrangement of row, column and page fields

pvt.AddFields RowFields:=Array("Product", "Customer")

ColumnFields:="State", _

PageFields:="Date"

End Sub

s- P-iCfiEfctsi

, -

A

B

e

E

F

3

i

1

□ at?

(AH)

*

3

Sum o( NumberSold Column Labels -

4

Row Labels

I" Apples

Mangoes Oranges

Pears Grand Total

5

- Kee

10462

14904

13029

14875

53270

6

NSW

2059

2452

2779

792

8082

^

NT

1353

2364

1722

1283

6722

3

OLD

526

2257

1043

1259

5985

9

EA

2559

207 G

1612

2919

9166

10

TAS

1562

994

499

3976

7031

11

VIC

1037

297G

356

1917

7586

12

WA

566

1785

3618

2729

3698

13

Pradesh

15068

13018

13619

11688

53393

14

NSW

2108

1112

2318

1929

7E47

15

NT

2285

2369

1681

4563

10898

16

OLD

2176

1352

1632

5160

17

SA

2795

1329

1589

1734

7447

TAS

2065

1272

1433

724

5494

"19

VIC

1593

3807

2618

1911

9929

2D

WA

1966

1777

2348

827

6918

21

- Roberts

13202

16376

13541

10075

53194

22

NSW

274

2807

1738

3127

7946

23

NT

2791

1483

1056

14G0

6790

24

QLD

2053

1045

2740

1852

7G30

25

SA

3572

184G

2942

8360

26

TAS

876

3407

1599

781

6663

2?"

VIC

3015

2428

291

2317

8051

2S

WA

621

3360

3175

533

7694

29

- Smith

8551

12222

14581

13563

4891Í

30

NSW

1182

1B8G

2711

1069

6648

H

► w Sheet!

ÍS-6: J.

Note that you can use the Array function to include more than one field in a field location. The result is shown in Figure 7-5.

You can use the Orientation and Position properties of the PivotField object to reorganize the Table. Position defines the hierarchy of fields within the Table, counting from the top level down. The following code, added to the end of the RedefinePivotTable code, would move the Customer fields above the Product fields as shown in Figure 7-6, for example:

pvt.PivotFields("Customer").Position = 1

- £.. : 7;t<rl :

A

B

£

D

E

F

ti

1

2

Data

(Aili

T

Sum of NumberSold Column Labels -

4

How Labels

- NSW

NT

QLD

SA

TAS

VIC

WA

Grand Total

ä

Apples

5703

7660

5538

10141

5587

9206

3448

47283

6

" Kes

2ÔS9

1353

526

2559

1562

1837

566

10462

7

Pradesh

2188

2285

2170

2795

2065

1593

19S6

15068

B

Roberts

274

2791

2053

3572

876

3015

621

13202

S

Smith

1182

1231

793

1215

1084

2761

295

8551

10

- Mangoes

8257

8663

6490

7112

5970

11171

8857

56520

V

KfiB

2452

2364

2257

2G76

994

2976

1735

14904

12

Pjndesh

1112

2369

1352

1329

1272

3807

1777

1301 a

13

Roberts

2807

1483

1045

1346

3407

2423

3350

16376

14

Smith

1886

2447

1836

1961

297

1960

1936

12222

15

- Oranges

9546

7585

10974

6733

4891

5110

9931

54770

Kes

2779

1722

1943

1612

499

856

3618

13029

17

Pradesh

2318

1S81

1632

1589

1433

261B

2348

13619

13

Roberts

1738

1056

2740

2942

1599

291

3175

13541

19

Smith

2711

3126

4659

590

1360

1345

790

14581

2D

- Peary

6917

11682

4253

8287

6607

7423

5032

50201

21

Kb®

792

1283

1259

2919

3976

1917

2729

14875

si

Pradesh

1929

4563

1734

724

1911

827

116M

23

Roberts

3127

1460

1852

781

2317

538

10075

24

Smith

1069

4376

1142

3634

1126

1273

938

13563

25

Grand Total

30423

35590 27255 32273

23055 32910 27260

208774

sheetl

Figure 7-5

Figure 7-6

You can use the Function property of the PivotField object to change the way a data field is summarized, and the NumberFormat property to set the appearance of the numbers. The following code, added to the end of RedefinePivotTable, adds Revenue to the data area, summing it and placing it second to

NumberSold by default. The next lines of code change the position of NumberSold to the second position, and change "Sum of NumberSold" to "Count of NumberSold", which tells you how many sales transactions occurred:

Sub AddDataField()

Dim pvt As PivotTable

Set pvt = ActiveSheet.PivotTables(l)

'Add and format new Data field With pvt.PivotFields("Revenue") .Orientation = xlDataField .NumberFormat = "0" End With

'Edit existing Data field With pvt.DataFields("Sum of NumberSold") .Position = 2 .Function = xlCount .NumberFormat = "0" End With

End Sub

Note that you need to refer to the name of the data field in the same way as it is presented in the Table— "Sum of NumberSold". If any further code followed, it would need to now refer to "Count of NumberSold". Alternatively, you could refer to the data field by its index number or assign it a name of your own choosing.

The result of all these code changes is shown in Figure 7-7.

f",:n = bisl

A

fi

c

O

fi -

F

6

1

Date

(All!

»

5

Row Labels 1

Column Labels ■ NSW

Sum of Revenue

Count of NumberSold

Sum of Revenue

Count of NumberSold

OLD SA Sum of Revenue Count of NumberSold Su

6

- Kee

130719

14

113117

13

103522

16

7 B 9 1(1 IT

Apples Mangoes Orangss Pears Pradesh

25738 49040 41635 14255 119082

16

16913 47230 25530 23094 183292

3 S 3 2 51

6575 45140 29145 22662 78720

1 7 4 4 10

16

Apples Mangoas Oranges Pears - Roberta

27350 22240 34770 34722 141921

5 13

28563 47380 25216 62134 10666«

120999

14

21

Apples Mangoes □ ranges Pears - Smith

3425 56140 26070 56285 112402

4 15

34888 29660 15840 26280 169986

6 4 4 3 21

25663 20900 41100 33336 136949

15

24 35

Apples Mangoes Oranges Pears

14775 37720 40665 19242

5 3

15388 48940 46690 78760

3 5 S 7

9788 36720 698B5 20556

3 6 2

26 27"

Grand Total

504124

58

593061

72

440189

55

■ ft Sheetl

Eaa!

Figure 7-7

0 0

Post a comment