Additional Members of the Pivot Table Object

Let us take a look at some additional members of the PivotTable object.

20.5.5.1 AddFields method

We have seen this method in action earlier in this chapter.

20.5.5.2 CalculatedFields method

It is possible to add calculated fields to a pivot table. These are fields that are not part of the original source data, but instead are calculated from source fields using a formula.

The CalculatedFields method returns the CalculatedFields collection of all calculated fields. To add a new calculated field, we use the Add method of the CalculatedFields collection. The syntax is:

CalculatedFieldsObject .Add(Name, Formula)

where Name is the name of the field and Formula is the formula for the field.

To illustrate, the following code creates a calculated field and displays it in the pivot table from Figure 20-10. The results are shown in Figure 20-21.

With ActiveSheet.PivotTables("Sales&Trans"). CalculatedFields.Add("Average", "= Sales/Transactions") .Orientation = xlDataField .Name = "Avg Check" .NumberFormat = "##.#" End With

Figure 20-21. Illustrating a calculated field

A

B

C D

E

F

G

1

Year

(All)

2

3

Per od

5

Store City

Store Type

Date

1

2

3

4

BOSTC N

Company

Trans

. 4

28714

23672

M

6

Ssle

441:78

46927

46256

46223

7

Ava Check

1 6

1 5

1 .6

1.6

8

Franchise

Tran 5

13993

13942

13275

13210

9

Sate

21816

2 1739

18632

16325

10

Avg Check

1;;

1 6

' 4

1.4

1 1

LOS ANCF

Company

Trans

34-583

35938

35692

35001

12

Sate

123479

129564

126340

125418

13

Ava Check

3.6

3.S

3.5

3.6

14

Franchise

Tran?

71583

72947

7561t

74392

15

Sate

262431

263274

279325

27453 I

16 17

Avg r\- (■(. k

3 7

37

3.7

3.7

NEW YÛR

Company

Trans

24610

26 IM

27015

26354

18

Sate

7308S

85251

87905

88058

19

Avg Check

S 2

3 3

3.3

3.3

20

Franchise

Tran 5

53273

54351

51822

51218

21

Sate

176353

182401

170537

H57446

22

Avg Check

3 3

34

3.3

3.3

We should make a brief remark about the arithmetic of the calculated field. The calculated field is computed directly from the data in the pivot table. The source data is not involved directly. This is why we did not specify an aggregate function for the calculated field. (Such a function would have been ignored.) Thus, for instance, the value in cell D7 is obtained by dividing the value in cell D6 by the value in cell D5.

Finally, we note that the ListFormulas method can be used to create a list of all calculated fields on a separate worksheet.

20.5.5.3 Errors-related properties

When the DisplayErrorString property is True, the PivotTable displays a custom error string in cells that contain errors. (The default value is False.) As the Excel help file observes, this property is particularly useful for suppressing divide-by-zero errors.

The ErrorString property can be used to set the string that is displayed in cells that contain errors when DisplayErrorString is True. (The default value is an empty string.)

20.5.5.4 Null-related properties

When the DisplayNullString property is True, the PivotTable displays a custom string in cells that contain null values. (The default value is True.)

The NullString property can be used to set the custom string for such cells. (The default value is an empty string.)

20.5.5.5 EnableDrillDown property

One of the features of a PivotTable is the DrillDown feature. To illustrate, if we double-click on a data cell, such as cell D5 in the pivot table of Figure 20-10, Excel will create a new worksheet, as shown in Figure 20-22. This worksheet shows the original source rows that contribute to the value in the double-clicked cell D5. (Note that the rows are complete, although the Sales column does not contribute to cell D5.)

Figure 20-22. Illustrating DrillDown

A

B C

D

E

F

G

1

Year

Period Store Code

Store City Store Type Transactions

Sales

2

1998

1 BO-1

BOSTON

Company

3SS1

6243

3

1998

1 BO-2

BOSTON

Company

o739

5iJ22

4

1938

1 BO-:-

BOSTON

Company

3S7?

6:^73

5

1996

1 BO-4

BOSTON

Company

3362

Li 123

6

1997

1 BG-4

BOSTON

Company

3218

5102

7

1997

1 BO-3

BOSTON

Company

3230

5231

8

1997

1 BO-2

BOSTON

Company

3157

4763

9

1997

1 BO-1

BOSTON

Company

3234

5206

By now you have probably guessed that the read-write Boolean property EnableDrillDown is used to enable or to disable this feature for the pivot table.

20.5.5.6 Formatting properties and methods

The read-write HasAutoFormat property is True (its default value) if the PivotTable is automatically formatted when it is refreshed or when some of its fields are moved.

The labels for the rows, columns, subtotals, and totals in Figure 20-6 are not merged with adjacent blank cells. (The borders are adjusted so it may appear so, however.) To merge the labels with adjacent blank cells, we can set the MergeLabels property to True.

The read-write PreserveFormatting property is True (its default value) if PivotTable formatting is preserved when the PivotTable is refreshed or recalculated by operations such as pivoting, sorting, or changing page-field items.

The TableStyle property returns or sets the style name (as a string) used in the PivotTable body. The default value is a null string, so no style is applied.

20.5.5.7 Refreshing a pivot table

When the ManualUpdate property is True, the PivotTable is recalculated only at the user's request. The default value is False.

The RefreshTable method refreshes the PivotTable from the original source data. The method returns True if it is successful.

The RefreshDate property returns the date on which the PivotTable or pivot cache was last refreshed. It is read-only.

The RefreshName property returns the user name of the person who last refreshed the PivotTable data or pivot cache.

20.5.5.8 PageField-related properties

As discussed earlier in the chapter, the PageFieldOrder property returns or sets the order in which page fields are added to the PivotTable layout. It can be one of the following XlOrder constants:

xlDownThenOver or xlOverThenDown. The default constant is xlDownThenOver.

Recall also that the PageFieldWrapCount property returns or sets the number of PivotTable page fields in each column or row.

The PageFieldStyle property returns or sets the style used in the page field area.

20.5.5.9 Name property

This property returns or sets the name of the pivot table as a string.

20.5.5.10 SaveData property (R/W Boolean)

When this property is True, its default value, data for the PivotTable is saved with the workbook. If it is False, only the PivotTable definition is saved.

20.5.5.11 ShowPages method

This method creates a new PivotTable for each item in the specified page field. Each new PivotTable is created on a new worksheet. The syntax is:

PivotTableObject .ShowPages( PageField)

For instance, if we apply this method to the pivot table in Figure 20-10 with the code:

ActiveSheet.PivotTables("Sales&Trans").ShowPages "Year"

we will get two new worksheets. One worksheet, named 1997, will contain the original pivot table, but with the Year page field set to 1997. The other worksheet will contain the same pivot table with the Year field set to 1998. (We can still change the Year field on any of the pivot tables. In other words, each pivot table contains the data for all of the Year field values.)

20.5.5.12 SourceData property

This read-only property returns the data source for the PivotTable. For instance, when that source is a single Excel worksheet, the SourceData method returns a string describing the source range. The code:

debug.print ActiveSheet.PivotTables("Sales&Trans").SourceData returns the string:

'Company Both'!R1C1:R145C7

0 0

Post a comment