Auto ShowRelated Members

The AutoShow method is used to restrict the display of pivot items for a given pivot field. The syntax is:

PivotFieldObject.AutoShow(Type, Range, Count, Field) All parameters are required for this method.

The Type parameter has two possible values: xlAutomatic activates the remaining parameters and, thereby, causes the restrictions to take effect, and xlManual disables the remaining parameters and causes Excel to remove any restrictions caused by a previous call to this method with Type equal to xlAutomatic.

The other parameters can be described by the following sentence: restrict pivot items to the top (Range =xlTop) or bottom (Range =xlBottom) Count pivot items based on the value in pivot field Field.

Thus, for instance, referring to Figure 20-10, the code:

ActiveSheet.PivotTables("Sales&Trans"). PivotFields("Store Type").AutoShow _ xlAutomatic, xlTop, 1, "Sale"

shows the top (Range =xlTop and Count=1) Store Type based on the value of Sale. The result is shown in Figure 20-24.

Figure 20-24. Illustrating AutoShow

A.

B

C

D

E F G

1

rear liAil)

2

3

Period

m

Store City

Store Type

Data

1

2 2 4

BOSTON

Company

T.'fcns Sale

28248 4467©

28714 28672 28602 46927 4S256 46223

los angele:

Franchise

Trsn^ Sale

71533 26^431

72947 75619 74392 263274 2793:5 274511

NEW YORK

Franchise

T'-^ris Sale

53273 176353

54351 51822 51213 132461 170537 167446

As you can see, the top sales in Boston are from the company stores, whereas the top sales in the other cities are in franchise stores.

The same code as the previous but with Type set to xlManual will remove the restrictions and restore the original pivot table:

ActiveSheet.PivotTables("Sales&Trans"). PivotFields("Store Type").AutoShow _ xlManual, xlTop, 1, "Sale"

The following properties are associated with AutoShow:

AutoShowCount property

The read-only AutoShowCount property returns the number of items that are automatically shown in the pivot field (this is the Count parameter of the AutoShow method).

AutoShowField property

This read-only property returns the name of the data field used to determine which items are shown (this is the Field parameter of the AutoShow method).

AutoShowRange property

This read-only property returns xlTop or xlBottom. This is the value of the Range parameter of the AutoShow method.

AutoShowType property

This read-only property returns xlAutomatic if AutoShow is enabled for the pivot field and xlManual if AutoShow is disabled.

0 0

Post a comment