hnpkeeper13
New Member
- Joined
- Jun 4, 2014
- Messages
- 2
My question is how to display only specific entries in a pivot table which is connected to a SQL query.
For example, I have 20 fields. One of those fields is "name". There are 50 entries in that name field. I only want to show 3 specific names. In Excel, I simply use the filtering dropdown and deselect what I don't want. I have too many tables to perform this manually.
I have been able to do this with a pivot table which is getting data from a simple defined table. I used a named range to store the entries I wish to keep. I used "ActiveSheet.PivotTables(1).PivotFields("name").PivotItems(i).Visible = True" in a loop to iterate through all items and set them all first to display, then iterated through each item again, comparing against the desired names and setting Visible to False for those which did not match the entries in the named range.
The same code is not working with the SQL connected pivot table. This is due to changes in how it references the field, adding [Query] to the beginning of the field references.
If I record a macro for clues how to do this, it tries to create a VisibleItemsList from an array and use this to define the visible items using this command: ActiveSheet.PivotTables(1).PivotFields("[Query].[name].[name]").VisibleItemsList = Array("[Query].[name].&[ACTUAL TEXT OF ITEM]", "[Query].[name].&[ITEM2]", "[Query].[name].&[ITEM3]")
Since I have a large number of items and would have to produce such list on the fly, it seems impossible to use that method.
If I try to just do one (shown below), it fails with "unable to get pivotitems property of the pivotfields class"
ActiveSheet.PivotTables(1).PivotFields("[Query].[name].[name]").PivotItems("name of what I want").Visible = False
Is there a way to either access the pivotitems property, or a better way to do what I am doing?
For example, I have 20 fields. One of those fields is "name". There are 50 entries in that name field. I only want to show 3 specific names. In Excel, I simply use the filtering dropdown and deselect what I don't want. I have too many tables to perform this manually.
I have been able to do this with a pivot table which is getting data from a simple defined table. I used a named range to store the entries I wish to keep. I used "ActiveSheet.PivotTables(1).PivotFields("name").PivotItems(i).Visible = True" in a loop to iterate through all items and set them all first to display, then iterated through each item again, comparing against the desired names and setting Visible to False for those which did not match the entries in the named range.
The same code is not working with the SQL connected pivot table. This is due to changes in how it references the field, adding [Query] to the beginning of the field references.
If I record a macro for clues how to do this, it tries to create a VisibleItemsList from an array and use this to define the visible items using this command: ActiveSheet.PivotTables(1).PivotFields("[Query].[name].[name]").VisibleItemsList = Array("[Query].[name].&[ACTUAL TEXT OF ITEM]", "[Query].[name].&[ITEM2]", "[Query].[name].&[ITEM3]")
Since I have a large number of items and would have to produce such list on the fly, it seems impossible to use that method.
If I try to just do one (shown below), it fails with "unable to get pivotitems property of the pivotfields class"
ActiveSheet.PivotTables(1).PivotFields("[Query].[name].[name]").PivotItems("name of what I want").Visible = False
Is there a way to either access the pivotitems property, or a better way to do what I am doing?
