Excel: List and/or Modify Pivot Table Filter(s) 1

[kjv1611]

I was looking at an Excel Pivot table and chart for moving to MS SQL Report Builder, and in that process, I came across one that had a filter with MANY items checked, and MANY items unchecked.

So for easier viewing and discussion, I thought it'd be nice to do a Debug.Print on all the items (or maybe would be better to list out in a new worksheet.

Regardless of where to put the list, I set off to finding how to do that. I did not find any direct results in a blog or forum, so I thought I'd go to testing on my own. I did find a couple of pretty neat methods for listing fields from a pivot table here:

http://www.contextures.com/excel-vba-pivot-table-field-list.html

However, those examples still don't give me what I'm after - nifty, and could be useful at times, but not for what I want to do.

Mostly what I've been able to do so far is get "application defined error" and others, trying to mess with a few objects.

I didn't keep all my attempts... tried, then deleted when didn't work and started from scratch. But regardless, here is my last attempt - please do not laugh.

Sub PivotTableExplorer()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pfl As PivotFilter
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    Set pt = ws.PivotTables("PivotTable11")
    Set pt = ActiveSheet.PivotTables("PivotTable11")

    For Each pf In pt.PivotFields
        'If pf.IsCalculated Then
            For Each pfl In pf.PivotFilters
                Debug.Print pfl.Description
            Next pfl
        'End If
    Next pf    
End Sub

What I got from that was a few lines of nulls or null strings.

Thanks in advance. This isn't something I have to have right now, b/c ended up logically eliminating the filter altogether. However, I'd like to figure this one out for future cases I might run into, and some could come pretty soon, as I'm converting loads of reports from Excel to Reporting Services.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Steve,

Here's a function I recently wrote to display the selected Pivot Items. The rng argument is the reference of a PivotField NAME cell. It will return ('item1','item2'...). You can use on the sheet just as you would any spreadsheet funcion...

Function VisiblePivotItems(rng As Range) As String
    Dim pvi As PivotItem, pvt As PivotTable
    
    Application.Volatile
    
    For Each pvt In ActiveSheet.PivotTables
        If Not Intersect(rng, pvt.TableRange2) Is Nothing Then
            If rng.Offset(0, 1).Value = "(All)" Then
                VisiblePivotItems = "ALL"
            Else
                For Each pvi In pvt.PivotFields(rng.Value).PivotItems
                    With pvi
                        If .Visible Then VisiblePivotItems = VisiblePivotItems & .Value & "; "
                    End With
                Next
            
                VisiblePivotItems = Left(VisiblePivotItems, Len(VisiblePivotItems) - 1)
            End If
        
            Exit Function
        End If
    Next
    
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Oh man, that is PERFECT!

I'm SO happy you recently wrote that for the same purpose. I hope I don't HAVE to use it, but I sure am glad that you've provided that example, so I can.

Thank you! Thank you! Thank you!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Well, the weird thing is, and maybe it's just some glitch in this workbook (I wouldn't be surprised) is I cannot find any of the values (well, I didn't try ALL of them, b/c too many to try) that I'm printing out when I do a Debug.Print on the VisiblePivotItems list. I've tried searching within the filter itself, and I've tried searching the whole workbook.

Weird, but I can't really dwell on this one right now, else my goose 'll be cooked!

Still, it's soemthing I'm going to come back to and figure out.

One guess is that somehow it's pulling stuff from memory/RAM rather than what's actually stored in the specified workbook, worksheet, and pivot table. Then again, it's probably just something I'm missing.

Thanks again, regardless, b/c this has gotten me much farther in digging through that piece than anything thus far!


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57