257
Addressing Issues with Two or More Data Fields
11
Figure 11.11
Converting 50,000 rows
of transactional data to
this useful report takes
less than two seconds if
you use the code that
produced this example.
Without pivot tables,the
code would be far more
complex.
Addressing Issues with Two or More Data Fields
So far, you have built some powerful summary reports, but you’ve touched only a portion of
the powerful features available in pivot tables. The preceding example produced a report
but had only one data field. It is possible to have multiple fields in the Σ Values section of a
pivot report. The data in this example includes not just revenue, but also a count of cus-
tomers.
When you have two or more data fields, you have a choice of placing the data fields in one
of four locations. By default, Excel builds the pivot report with the data field as the inner-
most column field. It is often preferable to have the data field as the outermost row field.
When a pivot table is going to have more than one data field, you have a virtual field named
Σ Values in the drop zones of the PivotTable Field List. In VBA, this equivalent virtual field
is named Data.
Where you place the data field in the
.AddFields method determines which view of the data
you get. The default setup, with the data fields arranged as the innermost column field, as
shown in Figure 11.12, would have this
AddFields line:
PT.AddFields ColumnFields:=Array(“Region”, “Data”)
The view shown in Figure 11.13 would use this code:
PT.AddFields RowFields:=Array(“Data”, “Product””)
One view that would make sense would have Data as the only column field:
PT.AddFields RowFields:=”Product”, ColumnFields:=”Data”
After adding a column field called Data, you would then go on to define two data fields:
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0,K”
End With
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 257
To Next Page
Loading...
Need help?
General
