My Last Post on Filtering Pivottable Source Data

Posted on by Dick Kusleika

Based on the comments from these three posts, there appears to be exactly zero people interested in this topic, so I’ll make it my last post on the matter.

In short, I wrote some code that subverts the default action of double clicking on a Pivot Cell (creating a new sheet with the underlying data) and instead filtering the source data where it lives. It worked reasonably well except for grouped dates. When a row or column contains a date and it is grouped, it is impossible to filter the source data column. There, I said it. The only way to determine if something is truly impossible is to declare it impossible. About nine times our of ten, someone will prove that it can be done. This will be a one-in-ten case. Gauntlet thrown.

My rambling analysis of why this is impossible starts with how Pivot Tables work. When Excel creates a PivotTable, it first creates a PivotCache that holds all the data. In this way, the PivotTable class doesn’t have to keep track of where the data came from (Excel range, External source, etc.). It has this PivotCache structure that it can rely on to be the same every time.

The PivotCache knows where it got the data so that it can refresh itself when the PivotTable tells it to. But it doesn’t know (or expose) the relationship between its fields and those in the source data. There’s really no reason it should, other than I want it to for this particular application.

If I click on “2010” when I’ve grouped InvoiceDate on Months and Years, there’s no way for me to know that 2010 relates to InvoiceDate or some other date field. Excel knows, it’s just not telling me. I attempted to brute-force it, but it would be faster to write out the source data by hand on a legal pad, so that’s out.

Finally I took a different tack. I let the PivotTable make its extra sheet, then used the data on that sheet to filter the source. This method has its own problems, but here’s the code.

Here’s the basics: The Workbook_SheetBeforeDoubleClick event fires when a cell is double-clicked. If that cell is within a Pivot Table, a module level variable is created to hold a reference to the PivotTable. I don’t “Cancel” the double click action causing a new sheet to be created and activated. When that happens, the Workbook_SheetActivate event is fired. It checks that module level variable to see if there’s a PivotTable in there. If so, the largest and smallest values from each column are captured and a Filter is applied to the source data with those values. Lastly, the module level variable is cleared and the sheet is deleted.

I ignore data fields for filtering, because that just don’t make any sense. The problem with this method is errors. When I capture the largest and smallest values, I ignore cells with errors, which can lead to results that aren’t accurate (more rows in the source data are shown than should be).

Thus ends my wholly unsatisfying journey through filtering source data based on a PivotCell double click. Back to potentially interesting Excel stuff tomorrow.

15 thoughts on “My Last Post on Filtering Pivottable Source Data”

  1. Hi Dick,

    I must admit, I see articles on PivotTables and cringe.
    I’ve re-read your previous 3 posts, and think you’re onto something!

    I’ve set up a pivottable with two Date columns, each grouped by Year.
    The properties of RowField (wks.PivotTables(1).RowFields) reveal interesting properties:

    ItemPositionCaption
    12Date
    24Other Date
    35Rep
    41Years
    53Years2

    I’m probably missing something, but couldn’t you use the Position attribute to get at the proper Date column?

  3. I find this all highly interesting and useful, (the final product and the process). Quite often my boss will be standing over my shoulder while having me manipulate a pivot table and then he’ll ask to see the actual underlying data for a particular intersection in the pivot table. More importantly, I’ve learned a lot of VBA working through Hector’s original code and your process here. It was great to print out all the posts and make notes as I go. I hate trying to learn by reading through VBA object model help files. It was a big help to have printouts of the example pivot table and layout wizard screen shots and make annotations of the various objects. Thank you very much for the posts. I’ll try to comment more. Not sure I have much to add yet beyond appreciation.

  4. @Dick

    I am totally intrigued in this topic, and have read your struggles with interest – I would love to get this feature working, but I do use grouped dates a lot so the solution would need to be handle them. I too cringe when working with pivot tables via vba – I have clients that I have successfully automated the pivot tables, but getting my head around them again is non-trivial. The double-click on a pivot cell to see the original data in the context of the other data is very valuable – maybe Microsoft will add it to the enhancement list

    @Rob – it is possible to only have the years or months and not have the actual date field in the row area, so relying on the date appearing in the row section is not sufficient.

    –Charlie

  7. Please do not give up on the topic, need to go through your previous posts first in order to assess the topic first, then I will give you a more detailed feedback.

  8. Dick,

    In your datasource, if in a data range, you need to add 2 helper columns. ‘_Row’, with each cell set to ‘=ROW()’ and ‘_Filter’, leave those cells blank for now. In the Thisworkbook module, add the folowing code below, with a slight varation of some of your code. This code was only tested in Excel 2007 and 2010. Do not use a table name as your data source in Excel 2010 since the data source column range is not included, but is in Excel 2007.

    Dim mrngDataSource As Range
    ‘
    ‘ Note: Excel 2007 and 2010 create’s a range as a Table or ListObject when double-clicking inside a pivot
    ‘ table data field
    Private Sub Workbook_NewSheet(ByVal Sh As Object)
        Dim rng As Range, rngHRow As Range, rngData As Range, col As Long
        Application.ScreenUpdating = False
        If Sh.ListObjects.Count > 0 Then
            Set rngData = mrngDataSource
            ‘Check to make sure _Row and _Filter col’s exist
            With rngData
                If WorksheetFunction.CountIf(.Rows(1), “_Row”) = 0 Or WorksheetFunction.CountIf(.Rows(1), “_Filter”) = 0 Then
                    MsgBox “Could not find ‘_Row’ or ‘_Filter’ column(s) in “ & rngData.Parent.Name
                    Exit Sub
                End If
            End With
            With Sh.ListObjects(1)
                If WorksheetFunction.CountIf(.HeaderRowRange, “_Row”) = 0 Or WorksheetFunction.CountIf(.HeaderRowRange, “_Filter”) = 0 Then
                    MsgBox “Could not find ‘_Row’ or ‘_Filter’ column(s) in “ & Sh.Name
                Exit Sub
                End If
            End With
            With rngData
                Set rngHRow = .Columns(WorksheetFunction.Match(“_Filter”, .Rows(1), 0))
            End With
            ‘ Reset _Filter col values to False
            With rngHRow
                .Parent.Range(.Cells(2), .Cells(.Rows.Count)).Value2 = False
            End With
            ‘ Set only those cells in ‘_Rows’ col in datasource that are also in sh
            For Each rng In Sh.ListObjects(1).ListColumns(“_Row”).DataBodyRange
                rngHRow.Cells(rng.Value2) = True
            Next rng
            ‘ Delete sh
            Application.DisplayAlerts = False
            Sh.Delete
            Application.DisplayAlerts = False
            ‘ Reset the datasource so it has only one filter, at ‘_Filter’ col & set to True
            With rngHRow
                .Parent.Activate
                On Error Resume Next
                .Parent.ShowAllData
                .AutoFilter field:=.Column, Criteria1:=True
            End With
            Set rng = Nothing: Set rngHRow = Nothing: Set rngData = Nothing
        End If
    End Sub
    ‘
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        Dim x As PivotCell, n As Long
        On Error Resume Next
        Set x = Target.Cells(1).PivotCell
        If Err.Number = 0 Then
            Set mrngDataSource = Range(x.PivotTable.SourceData)
        Else
            Err.Clear
            Set mrngDataSource = Nothing
        End If
    End Sub

  9. Dick,

    After much thought, I think this solution has a problem if you can’t guarantee the pivot cache is up to date with it’s underlying data source. For example, if the row count is smaller in the data source than in the pivot cache, an error will occur when trying to set the non-existant row to a TRUE value. Also, if at least one value in a row, that is referenced in the pivot table, is altered, then setting it TRUE means that you are referencing an invalid state between the Pivot Cache and it’s data source. There are other examples also. I think this is why Microsoft chose not to include this functionality.

  10. I too find this topic very interesting. Thank you for all of your posts and hard work. Like Sean said, my boss wants to initially see everything in the pivot table but then double click and see the source data so I can edit it. ex. Change due dates, statuses, etc.

    I can’t get Hector’s code working in Excel 2010. Too bad Excel doesn’t give you the option of viewing the source data when you double click.

  11. Revisiting the subject in the same way, I’m using arrays in the excel 2010 autofilter to match the filtering in the Details sheet. My code is below:

    Private mPivotTable As PivotTable

    Sub GetDetailsOnSource()

    ‘turn off updates to speed up code execution
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    End With

    On Error Resume Next
    Set mPivotTable = Selection.PivotTable
    On Error GoTo 0

    If Not mPivotTable Is Nothing Then
    If mPivotTable.PivotCache.SourceType xlDatabase Or _
    Intersect(Selection, mPivotTable.DataBodyRange) Is Nothing Then

    Set mPivotTable = Nothing
    End If
    End If

    Selection.ShowDetail = True
    GetDetailInfo

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    End With

    End Sub

    Sub GetDetailInfo()

    Dim rCell As Range
    Dim rData As Range
    Dim vMin As Variant, vMax As Variant
    Dim rSource As Range
    Dim lOldCalc As Long, sh As Worksheet
    Dim colItems As Collection, arrFilter As Variant, lLoop As Long, lLastRow As Long
    Dim bBlanks As Boolean, bNumbers As Boolean, sNumberFormat As String

    Set sh = ActiveSheet

    If Not mPivotTable Is Nothing Then

    lOldCalc = Application.Calculation
    Application.Calculation = xlCalculationManual

    Set rSource = Application.Evaluate(Application.ConvertFormula(mPivotTable.SourceData, xlR1C1, xlA1))
    rSource.Parent.AutoFilterMode = False
    rSource.AutoFilter

    lLastRow = sh.ListObjects(1).Range.Rows.Count
    sh.ListObjects(1).Unlist

    ‘Loop through the header row

    For Each rCell In Intersect(sh.UsedRange, sh.Rows(1)).Cells

    If Not IsDataField(rCell) Then
    If Application.WorksheetFunction.CountIf(rCell.Resize(lLastRow), “”) > 0 Then bBlanks = True Else bBlanks = False

    rCell.Resize(lLastRow).RemoveDuplicates Columns:=1, Header:=xlYes

    If Application.WorksheetFunction.CountA(rCell.EntireColumn) = Application.WorksheetFunction.Count(rCell.EntireColumn) + 1 _
    And Not IsDate(sh.Cells(Rows.Count, rCell.Column).End(xlUp)) Then ‘convert numbers to text
    bNumbers = True
    rCell.EntireColumn.NumberFormat = “0”
    rCell.EntireColumn.TextToColumns Destination:=rCell, DataType:=xlFixedWidth, _
    OtherChar:=”” & Chr(10) & “”, FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True
    Else
    bNumbers = False
    End If

    arrFilter = sh.Range(rCell.Offset(1), sh.Cells(sh.Rows.Count, rCell.Column).End(xlUp).Offset(IIf(bBlanks, 1, 0))).Value

    If Application.WorksheetFunction.Subtotal(3, rCell.EntireColumn) = 1 Then
    rSource.AutoFilter Field:=rCell.Column, Criteria1:=””

    Else:
    arrFilter = Application.Transpose(arrFilter)

    sNumberFormat = rSource.Cells(2, rCell.Column).NumberFormat

    If bNumbers Then _
    rSource.Columns(rCell.Column).NumberFormat = “0”

    rSource.AutoFilter Field:=rCell.Column, Criteria1:=arrFilter, Operator:=xlFilterValues

    rSource.Cells(2, rCell.Column).NumberFormat = sNumberFormat
    End If

    Set arrFilter = Nothing
    End If

    Next rCell

    ‘so it doesn’t run at next sheet activate
    Set mPivotTable = Nothing

    Application.Calculation = lOldCalc

    ‘Delete the sheet created by double click
    Application.DisplayAlerts = False
    sh.Delete
    Application.DisplayAlerts = True

    rSource.Parent.Activate

    End If
    End Sub

    Private Function IsDataField(rCell As Range) As Boolean

    Dim bDataField As Boolean
    Dim i As Long

    bDataField = False
    For i = 1 To mPivotTable.DataFields.Count
    If rCell.Value = mPivotTable.DataFields(i).SourceName Then
    bDataField = True
    Exit For
    End If
    Next i

    IsDataField = bDataField

    End Function

  12. Hi Dick,

    Not sure if you’re still interested in this topic. I’m working on an add-in that contains a feature to filter the pivot source data, and came to the same conclusion you did about this being impossible. The info exists in the pivotcache.xml file, but no way to extract it via VBA that I could find.

    One possible solution, although not straight forward at all, would be to ungroup and then re-group the data field. Before the ungroup you would have to store the orientation and positions of the page, column, and row fields in an array. When you ungroup one of the created date fields (years, months, etc.), the creator of the date groups will be added back to the pivottable. Then you do a comparison between the fields in the pivot before and after the ungroup. The field that is not found in the array of original fields is your date field (group creator). Then you would have to re-group the fields based on the original grouping, and set the orientations and positions.

    Like I said, this is not straight forward at all, and would probably run into it’s own set of issues with recreating the pivot. It would not work if there is any data in cells adjacent to the pivot that would be replaced during the ungrouping.

    Thanks for declaring this impossible, it was driving me crazy too. I guess I’ll stick with the legal pad solution for now… :)

  13. Jon: Good thought. I’m not sure how to determine how it’s grouped so I could regroup. I wonder about making a copy of the workbook to ungroup, then you won’t mess up the original.

    Another thought I had was only making it so it worked with year-month groupings, because that’s what I use anyway. I could try to build a date and use DateValue() to see if it really is a date.

  14. Okay, I finally worked up the nerve to read this series. You can determine whether a field is a grouped field by seeing if it’s Pivotfield.SourceName exists or not in the original data source. And in fact once you’d found that out, you probably wouldn’t need to go as far as making a copy and ungrouping to determine what the source is. Just take a punt that if Pivotfield.SourceName returns either “Years”, “Months”, “Quarters”, “Days”, “Hours”, “Minutes” or “Seconds” – and that none of those appear in the underlying data as columns – then the source field is going to probably be the only one with a date format applied.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.