Totals Related Members

The PivotTable object has two properties that affect the display of totals.

ColumnGrandproperty (R/W Boolean)

When this property is True, the PivotTable shows grand column totals.

RowGrand property (R/W Boolean)

When this property is True, the PivotTable shows grand row totals.

To illustrate, referring to the pivot table in Figure 20-6, the code:

ActiveSheet.PivotTables("Sales&Trans").

ColumnGrand = False ActiveSheet.PivotTables("Sales&Trans"). RowGrand = False produces the pivot table in Figure 20-9, with no grand totals.

Figure 20-9. No grand totals

1

Year

2

3

-eriod

4

Store City

Store Type

Data

1

?

3

4

POSTO N

Company

S jrr of Trartactions

28218

m 14

20672

26602

6

S jrr of Sales

446 76

46927

46256

4 6223

7

Franchise

i jrr of Transactions

13933

13942

13275

13210

S

S jrr of Sales

21816

21739

18632

18325

9

BOSTON Bum or Trans actions

H2A 1

4255t:

a mi

1 ¡812

10

BOSTON Sum or Sales

66494

eoeee

64888

6454S

11

LOS ANC:

Company

S jrr ol Transactions

345 SS

3593Ö

35DÛ1

12

S jrr of Stales

1234 re

129564

1263¿0

125^18

13

hrancFsse Sjrr oí I rar sachons

! Ii) lí 3

'204/

75e'9

,■■4392

14

Suffi of Sedès

202431

268274 279325

1

15

LOS ANGE

.LE5 Sum oi Transections

100 1 7 !

losses

111311

109393

10

LOS ANGELES Sum & Sales

3850 DO

307538 4096B5

300940

17

NEW VOR

Company

Sum of Traisacti ans

24': 10

20104

770' 5

20854

IS

Sum of Sales

78069

85251

87305

88059

19

Franchise

S jm of Tnsnsacti Tins

53273

54351

51822

51218

20

S jrr of Sales

176353

187461

170537

167446

21

NEW YORK Sum of Transectons

77660

30-155

78%7

76072

22

NEW YORK Sum of Sales

254442

267712 25S4¿2

255504

We can also suppress the display of individual pivot-field totals, such as the totals for Store City in Figure 20-9. This is a property of the particular PivotField object, so we will discuss it when we discuss this object later in the chapter. As a preview, however, the display of field totals is governed by the Subtotals property of the PivotField object. For instance, the following code turns off all field totals in Figure 20-9:

ActiveSheet.PivotTables("Sales&Trans"). PivotFields("Store City").Subtotals(i) = False Next

(There are 12 types of totals, and we must turn them all off.) This produces the pivot table in Figure 20-10.

Figure 20-10. No totals at all

A

B

C

0

E

F

G

1

Year

ÍAIf w

2

3

Period

4

Store City

Store Type

Data

1

2

3

4

5

BOSTON

Company

Sum of Transacions

2824Ô

28714

28672

28607

6

Sum of Sales

44673

■165 27

4 6256

462; 3

7

Franchise

Sum of Transactions

13993

13942

13275

13210

ÍJ

Sum of Sale^

21316

21739

18632

13S25

S

LOS ANGE

Company

Sum of Transactions

345SS

35Ö3S

35692

35Û0 1

10

;um ot Üales

1234

126340

1254Iy

11

Franchise

i-urr: itf Transscions

71583

72E4 7

75619

743¿i

12

Sum of ¿ales

262431

268274

279325

274531

13

NFW YOP

Company

Sum of Transitions

24016

26104

27015

2B854

U

Sum of Sales

78039

85251

07905

eso53

15

Franch ise

Sum of Transactions

53273

5435'

51822

51218

16

Sum of Sales

176353

132401

170537

0 -1

Post a comment