Show AllItems Property

This read-write Boolean property is True if all items in the PivotTable are displayed. The default value is False, in which case the pivot items that do not contribute to the data fields are not displayed. Note that this property corresponds to the "Show items with no data" check box on Excel's PivotTable Field dialog box.

To illustrate, suppose we add a single row to our source table:

1998 1 BO-1 BOSTON AStoreType 1000 $10000.00

This row creates a new store type but adds data only for Boston. The resulting pivot table will have the default appearance shown in Figure 20-35. Since ShowAllItems is False, there are no rows for the new store type corresponding to Los Angeles or New York.

Figure 20-35. ShowAllItems = False

A

B

C

D

E

F

G

1

rear | (Ail)

2

3

Period

4

Store

Store Type

Data

1

2

3

4

5

BOS-

AStoreType

Trens

1000

e

Sal*

10000

7

Company

Trans

28248

23"? 14

23672

28602

s

Sale

44673

46927

46256

46223

9

Franchise!

Trans

1.3993

13942

13275

13210

10

Sa If

21816

21739

13632

1332Î

11

LOS.

Company

Trans

34533

35933

35692

350 0 I

12

Sale

123478

129564

I <"6340

1254 18

13

Franchise!

Trans

71583

72947

?5619

74392

14

Sale

262431

268274

279325

274531

15

NEW

Company

Trans

24616

26104

27015

26354

15

Sale

7S089

35251

87305

88058

17

Franchise!

Trans

53273

54351

51822

51213

13

Sale

176353

132461

170537

167446

14

r

On the other hand, the code:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Store Type").ShowAllItems = True will produce the pivot table shown in Figure 20-36.

Figure 20-36. ShowAllItems = True

A

B

C

D

E

F

G

1

Yearl(Ail) ▼

2

3

Period

4

Siore

Store Type

Data

1

2

3

4

5

BOS"

AStoreType

Trans

1000

6

Sale

10000

7

Company

Trans

26240

28714

23672

28602

S

Sale

44673

46927

46256

'16223

9

Franchise

Trans

1300?

13942

13275

13210

10

Saie

21016

21739

18632

18325

11

LOS,

AStoreType

Trans

12

3a ;e

13

Company

Trans

34533

35933

35 G 92

35001

14

Sale

123473

129564

126340

125413

15

Franchise

Trsns

71583

■2 ¿47

75619

74392

16

Sal©

262431

268274

279325

274531

17

NEW

AStoreType

Trans

18

Sale

19

Company

Trsns

2461©

26104

27015

26854

20

Sa®

76039

35251

37905

63053

21

Franchise

Trans

53273

54351

51322

51216

22

Sate

176853

182461

170537

M- 'Mi-

0 0

Post a comment