Advanced Pivot Table Techniques

You may be a pivot table pro and never have run into some of the really advanced techniques available with pivot tables. The next four sections discuss such techniques.

AutoShow Feature to Produce Executive Overviews

If you are designing an executive dashboard utility, you might want to spotlight the top five markets.

As with the AutoSort option, you could be a pivot table pro and never have stumbled across the AutoShow feature in Excel. This setting lets you select either the top or bottom n records based on any data field in the report.

The code to use AutoShow in VBA uses the .AutoShow method.

 ' Show only the top 5 Markets PT.PivotFields("Market").AutoShow Top:=xlAutomatic, Range:=xlTop, _     Count:=5, Field:="Sum of Revenue" 

When you create a report using the AutoShow method, it is often helpful to copy the data and then go back to the original pivot report to get the totals for all markets. In the following code, this is achieved by removing the Market field from the pivot table and copying the grand total to the report. Listing 12.6 produces the report shown in Figure 12.18.

Listing 12.6. Code Used to Create the Top 5 Markets Report

 Sub Top5Markets()     ' Produce a report of the top 5 markets     Dim WSD As Worksheet     Dim WSR As Worksheet     Dim WBN As Workbook     Dim PTCache As PivotCache     Dim PT As PivotTable     Dim PRange As Range     Dim FinalRow As Long     Set WSD = Worksheets("PivotTable")     ' 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:="Line of Business"     ' Set up the data fields     With PT.PivotFields("Revenue")         .Orientation = xlDataField         .Function = xlSum         .Position = 1         .NumberFormat = "#,##0"         .Name = "Total Revenue"     End With     ' Ensure that you get zeroes instead of blanks in the data area     PT.NullString = "0"     ' Sort markets descending by sum of revenue     PT.PivotFields("Market").AutoSort Order:=xlDescending, _         Field:="Total Revenue"     ' Show only the top 5 markets     PT.PivotFields("Market").AutoShow Type:=xlAutomatic, Range:=xlTop, _         Count:=5, Field:="Total Revenue"     ' Calc the pivot table to allow the date label to be drawn     PT.ManualUpdate = False     PT.ManualUpdate = True     ' Create a new blank workbook with one worksheet     Set WBN = Workbooks.Add(xlWBATWorksheet)     Set WSR = WBN.Worksheets(1)     WSR.Name = "Report"     ' Set up ritle for report     With WSR.[A1]         .Value = "Top 5 Markets"         .Font.Size = 14     End With     ' Copy the pivot table data to row 3 of the report sheet     ' Use offset to eliminate the title row of the pivot table     PT.TableRange2.Offset(1, 0).Copy     WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats     LastRow = WSR.Cells(65536, 1).End(xlUp).Row     WSR.Cells(LastRow, 1).Value = "Top 5 Total"     ' Go back to the pivot table to get totals without the AutoShow     PT.PivotFields("Market").Orientation = xlHidden     PT.ManualUpdate = False     PT.ManualUpdate = True     PT.TableRange2.Offset(2, 0).Copy     WSR.Cells(LastRow + 2, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats     WSR.Cells(LastRow + 2, 1).Value = "Total Company"     ' Clear the pivot table     PT.TableRange2.Clear     Set PTCache = Nothing     ' Do some basic formatting     ' Autofit columns, bold the headings, right-align     WSR.Range(WSR.Range("A3"), WSR.Cells(LastRow + 2, 6)).Columns.AutoFit     Range("A3").EntireRow.Font.Bold = True     Range("A3").EntireRow.HorizontalAlignment = xlRight     Range("A3").HorizontalAlignment = xlLeft     Range("A2").Select     MsgBox "CEO Report has been Created" End Sub 

Figure 12.18. The Top 5 Markets report contains two pivot tables.

The Top 5 Markets report actually contains two snapshots of a pivot table. After using the AutoShow feature to grab the top five markets with their totals, the macro went back to the pivot table, removed the AutoShow option, and grabbed the total of all markets to produce the Total Company row.

ShowDetail to Filter a Recordset

Take any pivot table in the Excel user interface. Double-click any number in the table. Excel inserts a new sheet in the workbook and copies all the source records that represent that number. In the Excel user interface, this is a great way to ad-hoc query a dataset.

The equivalent VBA property is ShowDetail. By setting this property to true for any cell in the pivot table, you will generate a new worksheet with all the records that make up that cell:

 PT.TableRange2.Offset(2, 1).Resize(1, 1).ShowDetail = True 

Listing 12.7 produces a pivot table with the total revenue for the top three stores and ShowDetail for each of those stores. This is an alternative method to using the Advanced Filter report. The results of this macro are three new sheets. Figure 12.19 shows the first sheet created.

Listing 12.7. Code That Uses the ShowDetail Method to Provide Detail for the Top Three Customers

 Sub RetrieveTop3StoreDetail()     ' Retrieve Details from Top 3 Stores     Dim WSD As Worksheet     Dim WSR As Worksheet     Dim WBN As Workbook     Dim PTCache As PivotCache     Dim PT As PivotTable     Dim PRange As Range     Dim FinalRow As Long     Set WSD = Worksheets("PivotTable")     ' 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:="Store", ColumnFields:="Data"     ' Set up the data fields     With PT.PivotFields("Revenue")         .Orientation = xlDataField         .Function = xlSum         .Position = 1         .NumberFormat = "#,##0"         .Name = "Total Revenue"     End With     ' Sort Stores descending by sum of revenue     PT.PivotFields("Store").AutoSort Order:=xlDescending, _         Field:="Total Revenue"     ' Show only the top 3 stores     PT.PivotFields("Store").AutoShow Type:=xlAutomatic, Range:=xlTop, _         Count:=3, Field:="Total Revenue"     ' Ensure that you get zeroes instead of blanks in the data area     PT.NullString = "0"     ' Calc the pivot table to allow the date label to be drawn     PT.ManualUpdate = False     PT.ManualUpdate = True     ' Produce summary reports for each customer     For i = 1 To 3         PT.TableRange2.Offset(i + 1, 1).Resize(1, 1).ShowDetail = True         ' The active sheet has changed to the new detail report         ' Add a title         Range("A1:A2").EntireRow.Insert         Range("A1").Value = "Detail for " & _             PT.TableRange2.Offset(i + 1, 0).Resize(1, 1).Value & _             " (Store Rank: " & i & ")"     Next i     MsgBox "Detail reports for top 3 stores have been created." End Sub 

Figure 12.19. Pivot table applications are incredibly diverse. This macro created a pivot table of the top three stores and then used the ShowDetail property to retrieve the records for each of those stores.

Create Reports for Each Region or Model

A pivot table can have one or more page fields. A page field goes in a separate set of rows above the pivot report. It can serve to filter the report to a certain region, a certain model, or a certain combination of region and model.

To set up a page field in VBA, add the PageFields parameter to the AddFields method. The following line of code creates a pivot table with Region in the page field:

 PT.AddFields RowFields:="Model", ColumnFields:="Data", PageFields:="Region" 

The preceding line of code sets up the Region page field set to the value (All), which returns all regions. To limit the report to just the North region, use the CurrentPage property:

 PT.PivotFields("Region").CurrentPage = "North" 

One use of a page field is to build a user form where someone can select a particular region or a particular product. You then use this information to set the CurrentPage property and display the results of the user form.

Another interesting use is to loop through all PivotItems and display them one at a time in the page field. You can quickly produce top 10 reports for each region using this method.

To determine how many regions are available in the data, use PT.PivotFields("Region").PivotItems.Count. Either of these loops would work:

 For i = 1 To PT.PivotFields("Region").PivotItems.Count     PT.PivotFields("Region").CurrentPage = _             PT.PivotFields("Region").PivotItems(i).Name     PT.ManualUpdate = False     PT.ManualUpdate = True Next i For Each PivItem In PT.PivotFields("Region").PivotItems     PT.PivotFields("Region").CurrentPage = PivItem.Name     PT.ManualUpdate = False     PT.ManualUpdate = True Next PivItem 

Of course, in both of these loops, the three region reports fly by too quickly to see. In practice, you would want to save each report while it is displayed.

So far in this chapter, you have been using PT.TableRange2 when copying the data from the pivot table. The TableRange2 property includes all rows of the pivot table, including the page fields. There is also a .TableRange1 property, which excludes the page fields. You can use either statement to get the detail rows:

 PT.TableRange2.Offset(3, 0) PT.TableRange1.Offset(1, 0) 

Which you use is your preference, but if you use TableRange2, you won't have problems when you try to delete the pivot table with PT.TableRange2.Clear. If you were to accidentally attempt to clear TableRange1 when there are page fields, you would end up with the dreaded "Cannot move or change part of a pivot table" error.

Listing 12.8 produces a new workbook for each region, as shown in Figure 12.20.

Listing 12.8. Code That Creates a New Workbook per Region

 Sub Top5ByRegionReport()     ' Produce a report of top 5 stores for each region     Dim WSD As Worksheet     Dim WSR As Worksheet     Dim WBN As Workbook     Dim PTCache As PivotCache     Dim PT As PivotTable     Dim PRange As Range     Dim FinalRow As Long     Set WSD = Worksheets("PivotTable")     ' 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:="Store", ColumnFields:="Data", _         PageFields:="Region"     ' Set up the data fields     With PT.PivotFields("Revenue")         .Orientation = xlDataField         .Function = xlSum         .Position = 1         .NumberFormat = "#,##0,K"         .Name = "Total Revenue"     End With     ' Sort stores descending by sum of revenue     PT.PivotFields("Store").AutoSort Order:=xlDescending, _         Field:="Total Revenue"     ' Show only the top 5 stores     PT.PivotFields("Store").AutoShow Type:=xlAutomatic, Range:=xlTop, _         Count:=5, Field:="Total Revenue"     ' 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     Ctr = 0     ' Loop through each region     For Each PivItem In PT.PivotFields("Region").PivotItems         Ctr = Ctr + 1         PT.PivotFields("Region").CurrentPage = PivItem.Name         PT.ManualUpdate = False         PT.ManualUpdate = True         ' Create a new blank workbook with one worksheet         Set WBN = Workbooks.Add(xlWBATWorksheet)         Set WSR = WBN.Worksheets(1)         WSR.Name = PivItem.Name         ' Set up Title for Report         With WSR.[A1]             .Value = "Top 5 Stores in the " & PivItem.Name & " Region"             .Font.Size = 14         End With         ' Copy the pivot table data to row 3 of the report sheet         ' Use offset to eliminate the page & title rows of the pivot table         PT.TableRange2.Offset(3, 0).Copy         WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats         LastRow = WSR.Cells(65536, 1).End(xlUp).Row         WSR.Cells(LastRow, 1).Value = "Top 5 Total"         ' Do some basic formatting         ' Autofit columns, bold the headings, right-align         WSR.Range(WSR.Range("A2"), WSR.Cells(LastRow, 3)).Columns.AutoFit         Range("A3").EntireRow.Font.Bold = True         Range("A3").EntireRow.HorizontalAlignment = xlRight         Range("A3").HorizontalAlignment = xlLeft         Range("B3").Value = "Revenue"         Range("A2").Select     Next PivItem     ' Clear the pivot table     PT.TableRange2.Clear     Set PTCache = Nothing     MsgBox Ctr & " Region reports have been created" End Sub 

Figure 12.20. By looping through all items found in the Region page field, the macro produced one workbook for each regional manager.

Manually Filter Two or More Items in a PivotField

In addition to setting up a calculated pivot item to display the total of a couple of products that make up a dimension, it is possible to manually filter a particular PivotField.

For example, you have one client who sells shoes. In the report showing sales of sandals, he wants to see just the stores that are in warm-weather states. The code to hide a particular store is

 PT.PivotFields("Store").PivotItems("Minneapolis").Visible = False 

You need to be very careful to never set all items to False; otherwise, the macro will end with an error. This tends to happen more than you would expect. An application may first show products A and B, then on the next loop show products C and D. If you attempt to make A and B not visible before making C and D visible, you will be in the situation of having no products visible along the PivotField, which causes an error. To correct this, always loop through all PivotItems, making sure to turn them back to Visible before the second pass through the loop.

This process is easy in VBA. After building the table with Line of Business in the page field, loop through to change the Visible property to show only the total of certain products. Use the following code:

     ' Make sure all PivotItems along line are visible     For Each PivItem In _         PT.PivotFields("Line of Business").PivotItems         PivItem.Visible = True     Next PivItem     ' Now - loop through and keep only certain items visible     For Each PivItem In _         PT.PivotFields("Line of Business").PivotItems         Select Case PivItem.Name             Case "Copier Sale", "Printer Sale"                 PivItem.Visible = True             Case Else                 PivItem.Visible = False         End Select     Next PivItem