B
BobT36
Hi there, I'm experimenting with programmatically filtering fields on a
PivotTable through VBA for a work project, but I'm having trouble with
filtering Date Subgroups, specifically Quarter and Month.
I've managed to filter a single fieldset in a fieldset fine, or the top level
field in a subgroup, but I just can't get it to also filter the subsequent
fields in a subgroup. So far I've only been practicing this with Date fields
though since I don't have any other data that has multiple fields in a
fieldset.
I've been using parts of the Northwind database to create a simple pivottable
and filter it to show Subtotal by a specified ShipDate by year/quarter/month,
at a specific ShipCountry.
Here's the code I was trying: (Just pasting the date code first so ya know
what I've been using)
'Set Display Date Field by Years and Add to Row Axis.
Set pFieldset = pTableView.FieldSets("ShippedDate By Month")
For Each pField In pFieldset.Fields
pField.IsIncluded = False
Next
pFieldset.Fields("Years").IsIncluded = True
pFieldset.Fields("Quarters").IsIncluded = True
pFieldset.Fields("Months").IsIncluded = True
pTableView.RowAxis.InsertFieldSet pFieldset
And here's the filter code that I call after I've built my chart: (The
commented section is what I can't get to work)
Private Sub FilterData()
Dim pTable As PivotTable
Dim pTableView As PivotView
Dim pFieldset As PivotFieldSet
Dim pField As PivotField
Dim FilterArray As Variant
'Set Pivot Table Object Properties.
Set pTable = Forms(FormName).PivotTable
Set pTableView = pTable.ActiveView
'Filter the Years Rows.
FilterArray = Array("1998")
Set pFieldset = pTableView.RowAxis.FieldSets("ShippedDate By Month")
Set pField = pFieldset.Fields("Years")
pField.IncludedMembers = FilterArray
'Filter the Quarters Rows.
' FilterArray = Array("Qtr1")
' Set pFieldset = pTableView.rowaxis.FieldSets("ShippedDate By Month")
' Set pField = pFieldset.Fields("Quarters")
' pField.IncludedMembers = FilterArray
'Filter the ShipCountry Column.
FilterArray = Array("Argentina")
Set pFieldset = pTableView.ColumnAxis.FieldSets("ShipCountry")
Set pField = pFieldset.Fields("ShipCountry")
pField.IncludedMembers = FilterArray
End Sub
I can build and format the pivottable fine, and also the filter code works
great to filter the data by Country and Year, however I just can't for the
life of me get it to also filter by a specific Quarter and Month. Am I using
the wrong Syntax / String, or can you just not programmatically filter by
subgroups, only the toop level field? It should be possible since right
clicking on a quarter in the pivottable and selecting "Filter by Selection"
on "Qtr1" works fine.
I've had a loot at alsorts of guides and websites but I just can't find the
Syntax for filtering by quarter/month/day/whatever, they only tell how to
filter by the top level field in a fieldset, or only filter by Year. Can
anyone help me out?
PivotTable through VBA for a work project, but I'm having trouble with
filtering Date Subgroups, specifically Quarter and Month.
I've managed to filter a single fieldset in a fieldset fine, or the top level
field in a subgroup, but I just can't get it to also filter the subsequent
fields in a subgroup. So far I've only been practicing this with Date fields
though since I don't have any other data that has multiple fields in a
fieldset.
I've been using parts of the Northwind database to create a simple pivottable
and filter it to show Subtotal by a specified ShipDate by year/quarter/month,
at a specific ShipCountry.
Here's the code I was trying: (Just pasting the date code first so ya know
what I've been using)
'Set Display Date Field by Years and Add to Row Axis.
Set pFieldset = pTableView.FieldSets("ShippedDate By Month")
For Each pField In pFieldset.Fields
pField.IsIncluded = False
Next
pFieldset.Fields("Years").IsIncluded = True
pFieldset.Fields("Quarters").IsIncluded = True
pFieldset.Fields("Months").IsIncluded = True
pTableView.RowAxis.InsertFieldSet pFieldset
And here's the filter code that I call after I've built my chart: (The
commented section is what I can't get to work)
Private Sub FilterData()
Dim pTable As PivotTable
Dim pTableView As PivotView
Dim pFieldset As PivotFieldSet
Dim pField As PivotField
Dim FilterArray As Variant
'Set Pivot Table Object Properties.
Set pTable = Forms(FormName).PivotTable
Set pTableView = pTable.ActiveView
'Filter the Years Rows.
FilterArray = Array("1998")
Set pFieldset = pTableView.RowAxis.FieldSets("ShippedDate By Month")
Set pField = pFieldset.Fields("Years")
pField.IncludedMembers = FilterArray
'Filter the Quarters Rows.
' FilterArray = Array("Qtr1")
' Set pFieldset = pTableView.rowaxis.FieldSets("ShippedDate By Month")
' Set pField = pFieldset.Fields("Quarters")
' pField.IncludedMembers = FilterArray
'Filter the ShipCountry Column.
FilterArray = Array("Argentina")
Set pFieldset = pTableView.ColumnAxis.FieldSets("ShipCountry")
Set pField = pFieldset.Fields("ShipCountry")
pField.IncludedMembers = FilterArray
End Sub
I can build and format the pivottable fine, and also the filter code works
great to filter the data by Country and Year, however I just can't for the
life of me get it to also filter by a specific Quarter and Month. Am I using
the wrong Syntax / String, or can you just not programmatically filter by
subgroups, only the toop level field? It should be possible since right
clicking on a quarter in the pivottable and selecting "Filter by Selection"
on "Qtr1" works fine.
I've had a loot at alsorts of guides and websites but I just can't find the
Syntax for filtering by quarter/month/day/whatever, they only tell how to
filter by the top level field in a fieldset, or only filter by Year. Can
anyone help me out?