## 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