Pivot Table Data Crunching for Microsoft Office Excel 2007

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 prior example produced a report but had only one data field.

It is possible to have multiple fields in a pivot report.

The data in this example includes not just revenue, but also units. The CFO will probably appreciate a report by product that shows quantity sold, revenue, and average price.

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 innermost row field. It is often preferable to have the data field as the outermost row field or as a column field.

When a pivot table is going to have more that one data field, you have a virtual field 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 row field, as shown in Figure 12.11, would have this AddFields line:

PT.AddFields RowFields:=Array("Line of Business", "Data")

The view shown in Figure 12.12 would use this code:

PT.AddFields RowFields:=Array("Data", "Line of Business")

The view that you need for this report would have Data as a column field:

PT.AddFields RowFields:="Model", 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" End With With PT.PivotFields("Units Sold") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0" End With

Calculated Data Fields

Pivot tables offer two types of formulas. The most useful type defines a formula for a calculated field. This adds a new field to the pivot table. Calculations for calculated fields are always done at the summary level. If you define a calculated field for average price as Revenue divided by Units Sold, Excel first adds up the total revenue and the total quantity, and then it does the division of these totals to get the result. In many cases, this is exactly what you need. If your calculation does not follow the associative law of mathematics, it might not work as you expect.

To set up a calculated field, use the Add method with the CalculatedFields object. You have to specify a field name and a formula. Note that if you create a field called Average Price, the default pivot table produces a field called Sum of Average Price. This is misleading and downright silly. What you have is actually the average of the sums of prices. The solution is to use the Name property when defining the data field to replace Sum of Average Price with something such as Avg Price. Note that this name must be different from the name for the calculated field.

Listing 12.2 produces the report shown in Figure 12.13.

Listing 12.2. Code That Calculates an Average Price Field as a Second Data Field

Sub TwoDataFields() Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Set WSD = Worksheets("PivotTable") Dim WSR As Worksheet Dim WBO As Workbook Dim WBN As Workbook Set WBO = ActiveWorkbook ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Define input area and set up a Pivot Cache FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Application.Columns.Count). _ End(xlToLeft).Column Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _ xlDatabase, SourceData:=PRange.Address) ' Create the Pivot Table from the Pivot Cache Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _ Cells(2, FinalCol + 2), TableName:="PivotTable1") ' Turn off updating while building the table PT.ManualUpdate = True ' Set up the row fields PT.AddFields RowFields:="Market", ColumnFields:="Data" ' Define Calculated Fields PT.CalculatedFields.Add Name:="AveragePrice", Formula:="=Revenue/Units Sold" ' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" End With With PT.PivotFields("Units Sold") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0" End With With PT.PivotFields("AveragePrice") .Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = "#,##0.00" .Name = "Avg Price" End With ' Ensure that you get zeroes instead of blanks in the data area PT.NullString = "0" ' Calc the pivot table PT.ManualUpdate = False PT.ManualUpdate = True End Sub

Calculated Items

Say that in your company the vice president of sales is responsible for copier sales and printer sales. The idea behind a calculated item is that you can define a new item along the Line of Business field to calculate the total of copier sales and printer sales. Listing 12.3 produces the report shown in Figure 12.14.

Listing 12.3. Code That Adds a New Item Along the Line of Business Dimension

Sub CalcItemsProblem() Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Set WSD = Worksheets("PivotTable") Dim WSR As Worksheet ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT ' Define input area and set up a Pivot Cache FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Application.Columns.Count). _ End(xlToLeft).Column Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _ xlDatabase, SourceData:=PRange.Address) ' Create the Pivot Table from the Pivot Cache Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _ Cells(2, FinalCol + 2), TableName:="PivotTable1") ' Turn off updating while building the table PT.ManualUpdate = True ' Set up the row fields PT.AddFields RowFields:="Line of Business" ' Define calculated item along the product dimension PT.PivotFields("Line of Business").CalculatedItems _ .Add "PrinterCopier", "='Copier Sale'+'Printer Sale'" ' Resequence so that the report has printers and copiers first PT.PivotFields("Line of Business"). _ PivotItems("Copier Sale").Position = 1 PT.PivotFields("Line of Business"). _ PivotItems("Printer Sale").Position = 2 PT.PivotFields("Line of Business"). _ PivotItems("PrinterCopier").Position = 3 ' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" End With ' Ensure that you get zeroes instead of blanks in the data area PT.NullString = "0" ' Calc the pivot table PT.ManualUpdate = False PT.ManualUpdate = True End Sub

Look closely at the results shown in Figure 12.14. The calculation for PrinterCopier is correct. PrinterCopier is a total of Printers + Copiers. Some quick math confirms that 86 million + 68 million is about 154 million. However, the grand total should be 154 million + 83 million + 574 million, or about 811 million. Instead, Excel gives you a grand total of $968 million. The total revenue for the company just increased by $150 million. Excel gives the wrong grand total when a field contains both regular and calculated items. The only plausible method for dealing with this is to attempt to hide the products that make up PrinterCopier. The results are shown in Figure 12.15:

With PT.PivotFields("Line of Business") .PivotItems("Copier Sale").Visible = False .PivotItems("Printer Sale").Visible = False End With