Please Help- How To Retrieve All Pivot Table Filters

[sergioza]

Hi,

I can't know what fields will be filtered on so, so I need to extract all filters at run time. I need some VBA line(s) to do it please.

I tried activeCell.PivotCell.PivotTable.ActiveFilters and this returns 0 although there are definitely filters available.

The workaround which I'm not is very good I'm considering is:

activecell.PivotTable.PageFields.Items collection which also behaves weird:

activecell.PivotTable.PageFields.Count returns 2 at the same time it has 3 elements:
activecell.PivotTable.Item(0);activecell.PivotTable.Item(1);activecell.PivotTable.Item(2)


Many Thanx

 

[Jerry Sullivan]

Hi sergioza,

Can you describe more detail about the desired output:
1. Which Fields do you need: All Fields, a specified Field, Fields from a one section of the Report (PageFields, RowFields, ColumnFields,DataFields)?

2. Do you want all Visible Items, all Hidden Items or both?

3. Should the output be VBA Array or Values written to a Range?

Also, perhaps if you describe how you plan to use this data, there might be a more direct way to accomplish the same thing without making these lists.

 

[sergioza]

Hi,

I need to capture "selection criteria" chosen by a user.
I can get to "mdx" property of a user chosen chosen cell, with
strMDX=activecell.pivotcell.mdx
but if users had any additional filters at the top of the page it's not being captured.

I tried to use "ActiveFilters" property to get to this, but it returns 0.

Thank You

 

[Jerry Sullivan]

If you can reply to all the questions I listed, I will be better able to help you.

Just let me know if you need some clarification of what I was asking, or if you are unfamiliar with any terms.

 

[sergioza]

Hi,
1. Which Fields do you need: All Fields, a specified Field, Fields from a one section of the Report (PageFields, RowFields, ColumnFields,DataFields)?

I need filters for PageFields

2. Do you want all Visible Items, all Hidden Items or both?

All Items

3. Should the output be VBA Array or Values written to a Range?

output can be captured in a variable.

Thank You

S

 

[Jerry Sullivan]

Thanks that's very helpful.

Could you explain how you will use this too? You noted that the outbut can be captured in a variable, however there would need to be a two-dimensional array for each Field with listing PivotItem and Visible State. If you describe what you want to do with this, I'll try to put it in a structure that is easiest for you to use.

Also, is the datasource an OLAP cube?

 

[sergioza]

Hi,

Let's say this is pivot Table with Filters:

Page Filters:
Countries:
France
Germany
Spain

Pivot Table:
Year Revenue
2010 9999
2011 8888

-----------
User clicked on 8888 and Filtered Countries were France and Spain
-----------------------------------------------------

All I need to determine in my variable that:

var= "Country= 'France' or Country='Spain'"

or it can be an array which I'll parse later
arr(0)="Country= 'France'"
arr(1)="Country= 'Spain'"
Thank You

 

[Jerry Sullivan]

You can try the code below.

Since you only had one PageField in your example, this just lists filters for the first PageField in a PivotTable.
It could be extended to handle multiple PageFields.

How to use:
'---Select any cell on the PivotTable before running the macro.
'---The code will create an array in which the sArray(0) is the field name,
'--- and items sArray(1) and greater are Visible Items

Sub Get_Current_PageFilters_OnePageField()
    Dim PT As PivotTable
    Dim sArray() As String
    Dim i As Long
    On Error Resume Next
    
    Set PT = ActiveCell.PivotCell.Parent
    If PT Is Nothing Then
        MsgBox "Select a Cell in the Pivot Table before running macro"
        Exit Sub
    End If
    If PT.PageFields.Count < 1 Then
        MsgBox "There are no PageFields in this Pivot Table"
        Exit Sub
    End If
    
    With PT.PageFields(1)
        '---store field name in sArray(0)
        ReDim sArray(0)
        sArray(0) = .Name
        If .EnableMultiplePageItems Then
            '---store visible items in sArray(1) and up
            For i = 1 To .PivotItems.Count
                If .PivotItems(i).Visible Then
                    ReDim Preserve sArray(UBound(sArray) + 1)
                    sArray(UBound(sArray)) = .PivotItems(i)
                End If
            Next i
        Else
            ReDim Preserve sArray(UBound(sArray) + 1)
            sArray(UBound(sArray)) = .CurrentPage
        End If
    End With
    
    '---display results in Immediate Window
    For i = 0 To UBound(sArray)
        Debug.Print sArray(i)
    Next i
End Sub

Please let me know if this does what you wanted.

 

[sergioza]

Thank You Jerry,

Appreciated and It works great!

A few questions if I may; I'm more of .NET and SQL dveloper and VBA is not my daily diet :{

1. Do you happen to know what PivotTableActiveFilters property is?

2. Are Excel 2010 collections such as PivotFields are 0 or 1 based, bacause although visually there are only 2 page Fields in my tab and Count property returns 2: code
returns PivotFields(0);PivotFields(1);PivotFields(2) where
PivotFields(0);PivotFields(1) are the same?

Many Thanx

S

 

[Jerry Sullivan]

You're welcome.

1. Do you happen to know what PivotTableActiveFilters property is?

I haven't used that before.

Playing around with it a little it appears to work with Value fields that are filtered with a criteria. Somewhat like PivotTable filter criteria.

Filtering data values > 200, shows output using the code below.
Looks like it doesn't include Page, Row or Column filters that are checked off.

Sub ActiveFilters_Test()
    Dim PT As PivotTable
    Set PT = ActiveSheet.PivotTables(1)
    Dim i As Long
      
    For i = 1 To PT.ActiveFilters.Count
        Debug.Print "Filter item: " & i
        With PT.ActiveFilters(i)
            Debug.Print .PivotField
            Debug.Print .FilterType
            Debug.Print .Description
            Debug.Print .IsMemberPropertyFilter           
            Debug.Print .Name
            Debug.Print .Parent
            Debug.Print .Value1
            Debug.Print .Value2
        End With
    Next i
End Sub

There's also a .MemberPropertyField that has multiple sub-properties

2. Are Excel 2010 collections such as PivotFields are 0 or 1 based, bacause although visually there are only 2 page Fields in my tab and Count property returns 2: code
returns PivotFields(0);PivotFields(1);PivotFields(2) where
PivotFields(0);PivotFields(1) are the same?

2. I haven't used xl2010, but in xl2007 collections are 1 based. The reason you are getting 2 fields is that it is counting all PivotFields not just pagefields.


Cheers!