AutoFilter PivotTable source data rather than ShowDetails?

[lcluff2000]

On a pivotchart mouse event, I use the underlying pivottable showdetail to bring up the filtered source data (I have the row=arg1 and column=arg2 of the pivot table data from the mouse event):

ActiveChart.PivotLayout.PivotTable.DataBodyRange. _
Cells(Arg2, Arg1).ShowDetail = True

This command opens a new sheet with the filtered source data.

Rather than have a new sheet open, I would like to AutoFilter the pivottable source data that already exists on it's own sheet.

Any ideas of how I could do this based on knowing the row/col of the pivottable??
</pre>

 

[Jerry Sullivan]

Hi and Welcome to the Board,

That's an interesting idea. There are several parts that would need to be done...

Get the corresponding datafield and rowfield(s) of the datavalue
Get any PageField filters currently applied
Get the data source range
Go To the data source range and apply the gathered filters to an AutoFilter

From your question, it looks like you have some VBA experience.
Which part(s) do you want help with?

I'm not aware of a PivotChart mouse event - could you explain that?
Do you mean a WorkSheet_Double-click event on a PivotTable that normally brings up the ShowDetails?

 

[lcluff2000]

Thank you for the response. The pivotchart mouse event is calling the WorkSheet_Double-click event on a PivotTable. To do this, I basically combined information from these two links (since I had multiple charts on one sheet I needed to enable chart events):
http://itknowledgeexchange.techtarget.com/beyond-excel/chart-drill-down/
http://www.computorcompanion.com/LPMArticle.asp?ID=221

This works great, except when I click on the chart, it brings up a new sheet with source data.

My goal is to simply autofilter the source data (rather than bringing up a whole new sheet).

So on your list, I need help with...
(yes) Get the corresponding datafield and rowfield(s) of the datavalue
(yes) Get any PageField filters currently applied
(no, I know the data source range) Get the data source range
(yes) Go To the data source range and apply the gathered filters to an AutoFilter

 

[Jerry Sullivan]

Thanks for sharing those two links. Jon Peltier's article on Chart Events is really nicely done.

Sorry that I haven't been able to follow up on this yet. I have bits and pieces of the steps described above and I hope to be able to cobble it together tomorrow into some code that you can test.

 

[Jerry Sullivan]

Below is some code for you to try. It builds on the code examples in the two links you referenced.

Replace any previous code in your existing Class Module: CEventChart with this...

Option Explicit
 
Public WithEvents EvtChart As Chart
 
Private Sub EvtChart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
                          ByVal x As Long, ByVal y As Long)
 
    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    On Error GoTo ErrHandler
    ActiveChart.GetChartElement x, y, ElementID, Arg1, Arg2
 
    If ElementID <> xlSeries Then Exit Sub
    With ActiveChart.PivotLayout
        If .PivotTable.PivotCache.SourceType <> xlDatabase Then Exit Sub
        Call GoTo_Filtered_SourceData(PT:=.PivotTable, _
        lDataRow:=Arg2, lDataCol:=Arg1)
    End With
    Exit Sub
ErrHandler:
    If Err.Number <> 0 Then MsgBox _
        "Chart_MouseUp - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
End Sub

Append this code to your existing Standard Module: MChartEvents
This should come after Class declarations, Sub Set_All_Chart() and Sub Reset_All_Chart()

Public Function GoTo_Filtered_SourceData(PT As PivotTable, lDataRow As Long, _
        lDataCol As Long)
 
    Dim sSourceDataA1 As String
    Dim pvtField As PivotField
    Dim vVisible As Variant
 
  '---Get source data range and Goto it
    sSourceDataA1 = Application.ConvertFormula(PT.SourceData, _
            xlR1C1, xlA1)
    Application.Goto Range(sSourceDataA1)
 
  '---Apply filters
    With Range(sSourceDataA1)
        '---Clear any existing autofilters
        .Parent.AutoFilterMode = False
 
      '---Filter for PageField VisibleItems
        For Each pvtField In PT.PageFields
            vVisible = Store_FilterItems(pvtField)
            Call Filter_AutoFilterField(rData:=.Cells, _
                sHeader:=pvtField.SourceName, vItems:=vVisible)
        Next
    End With
 
'***To be completed...
'---Filter for RowField(s) of data item at lDataRow
    MsgBox "will Filter for Row: " & lDataRow & " of PT.DatabodyRange"
'---Filter for ColumnField(s) of data item at lDataCol
    MsgBox "will Filter for Column: " & lDataCol & " of PT.DatabodyRange"
End Function
 
Private Function Filter_AutoFilterField(rData As Range, _
    sHeader As String, vItems As Variant)
'---AutoFilters using the Field  matching sHeader
'    and criteria in array vItems.
    Dim lField As Long
    On Error Resume Next
 
    lField = Application.Match(sHeader, _
        rData.Resize(1), 0)
    If lField = 0 Then
        MsgBox "Header: " & sHeader & " not found"
    Else
        If vItems(0) = "(All)" Then vItems(0) = "*"
        rData.AutoFilter Field:=lField, _
        Criteria1:=vItems, Operator:=xlFilterValues
    End If
End Function
 
Private Function Store_FilterItems(pvtField As PivotField) As Variant
'---Returns an array of the PivotField's visible items
    Dim sVisibleItems() As String
    Dim pviItem As PivotItem
    Dim i As Long
 
    With pvtField
        If .Orientation = xlPageField And _
            .EnableMultiplePageItems = False Then
                ReDim sVisibleItems(1)
                sVisibleItems(0) = .CurrentPage
        Else
            For Each pviItem In .PivotItems
                If pviItem.Visible Then
                    i = i + 1
                    ReDim Preserve sVisibleItems(i)
                    sVisibleItems(i - 1) = pviItem
                End If
            Next
        End If
    End With
    Store_FilterItems = sVisibleItems
End Function

Still to be completed are the parts that filter for the data point's Rowfield(s) and ColumnField(s).

That code's a bit more complex because the associated fields are read differently depending on whether the report is using Compact, Tabular, Outline layouts (or some combination of those).

Please give this a try and let me know if it works as desired for going to the source data and filtering for any PageFields.

 

[lcluff2000]

Thanks so much for these code examples. I think this will be a very useful capability to have when completed.

I'm actually away from my desk until Monday. I apologize that I can't try them right away, but I'll definitely give them a try on Monday.

 

[lcluff2000]

I finally had time to look at this. I found that the EvtChart_MouseUp event is not being executed. If I use the following code to detect a Select event, then all the code seems to work fine -- it does apply the filters. Cool!

I'm not sure why the MouseUp event procedure is not being executed.

Private Sub EvtChart_Select(ByVal ElementID As Long, _
                          ByVal Arg1 As Long, ByVal Arg2 As Long)
'Private Sub EvtChart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
'                          ByVal x As Long, ByVal y As Long)

'    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    On Error GoTo ErrHandler
'    ActiveChart.GetChartElement x, y, ElementID, Arg1, Arg2

    If ElementID <> xlSeries Then Exit Sub
    With ActiveChart.PivotLayout
        If .PivotTable.PivotCache.SourceType <> xlDatabase Then Exit Sub
        Call GoTo_Filtered_SourceData(PT:=.PivotTable, _
        lDataRow:=Arg2, lDataCol:=Arg1)
    End With
    Exit Sub
ErrHandler:
    If Err.Number <> 0 Then MsgBox _
        "Chart_MouseUp - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
End Sub

Thanks for the insights this code provides! I'm still really interested in how to do the rowfields and columnfields.

 

[Jerry Sullivan]

Jon Peltier's article has a section "Using Event Procedures to Enable Chart Events" that shows how set this up so the MouseUp would work automatically.
I didn't repeat that code in my post. The method you use depends on whether you want this to be avaiable for All Charts in Workbook, in Worksheet, etc.

I'll be glad to look at the code needed for filtering the RowFields/ ColumnFields.
I probably won't be able to get to that for a couple of days.

What Report Layout are you typically using (Compact, Outline, or Tabular)?

 

[lcluff2000]

Oh, and I forgot to mention, when the following code is executed under the Select Event, it returns ElementID = 28 (ie xlNothing). That is why I commented it out in favor of the parameters passed in from the procedure arguments.

ActiveChart.GetChartElement x, y, ElementID, Arg1, Arg2

 

[lcluff2000]

I'm using Excel 2007 and I think it is defaulting to Compact Layout. But, it does seem fairly easy to change the layout to tabular or outline (if that is easier).