[Previous] [Contents] [Next]

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 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 19 shows the first sheet created.

Listing 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

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.

[Previous] [Contents] [Next]