Amazon VBA User Manual

277
Creating Report Percentages
.Function = xlAverage
.Position = 3
.NumberFormat = “#,##0”
.Name = “Average Revenue”
End With
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlMin
.Position = 4
.NumberFormat = “#,##0”
.Name = “Smallest Order”
End With
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlMax
.Position = 5
.NumberFormat = “#,##0”
.Name = “Largest Order”
End With
The resultant pivot table provides a number of statistics about the average revenue, largest
order, smallest order, and so on.
Creating Report Percentages
In addition to the available choices, such as Sum, Min, Max, and Average, you can use
another set of pivot table options called the calculation options. They allow you to show a par-
ticular field as a percentage of the total, a percentage of the row, a percentage of the
column, or as the percent difference from the previous or next item. All these settings are
controlled through the
.Calculation property of the page field.
The valid properties for
.Calculation are xlPercentOf, xlPercentOfColumn, xlPercentOfRow,
xlPercentOfTotal, xlRunningTotal, xlPercentDifferenceFrom, xlDifferenceFrom, xlIndex,
and
xlNoAdditionalCalculation. Each has its own unique set of rules. Some require that
you specify a
BaseField, and others require that you specify both a BaseField and BaseItem.
The following sections provide some specific examples.
Percentage of Total
To get the percentage of the total, specify xlPercentOfTotal as the .Calculation property
for the page field:
‘ Set up a percentage of total
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Caption = “PctOfTotal”
.Function = xlSum
.Position = 2
.NumberFormat = “#0.0%”
.Calculation = xlPercentOfTotal
End With
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 277

Amazon VBA Specifications

General

Brand	Amazon
Model	VBA
Category	Software
Language	English