PDA

View Full Version : [SOLVED] Pivot Table Multiple Value Filter Help



BenChod
06-20-2017, 06:15 AM
Hi All -

I created a pivot cache so I can used the same data set to create multiple pivot tables. As I add items to the page field, I want to filter on 3 items out of 10. I tried to use the pivot items set to visible for the three criteria I want to filter on and it's not filtering. If I set the pivot items to false on the criteria I want to filter out, that works. The problem I may face later on is what when the list to filter out items goes to 15 or 20. I don't want to write out each item to filter out. I would rather set the pivot items to be visible for the 3 I want to see. I have attached the snipped of the code and hopefully someone can provide a quick solution. The pivot item field is called "Phase Found In"


With ActiveSheet
Lastrow = ActiveSheet.UsedRange.Rows.Count
End With
Let copyrange = Lastrow + 8

Set pt = pc.CreatePivotTable( _
Tabledestination:=Range("A" & copyrange), _
TableName:="OCE_SEV1_PIVOT")

Set pf = pt.PivotFields("Severity")
pf.Orientation = xlPageField
pf.CurrentPage = "Severity 1"

Set pf = pt.PivotFields("Blocking")
pf.Orientation = xlPageField
pf.CurrentPage = "Y"

Set pf = pt.PivotFields("Stream")
pf.Orientation = xlPageField
pf.CurrentPage = "OCE"

Set pf = pt.PivotFields("Status")
pf.Orientation = xlPageField

Set pf = pt.PivotFields("Phase Found In")
pf.Orientation = xlPageField
pf.EnableMultiplePageItems = True
pf.ClearAllFilters
pf.PivotItems("Assembly Test").Visible = False
pf.PivotItems("Not a Testing Phase").Visible = False
pf.PivotItems("Performance/Load Testing").Visible = False
pf.PivotItems("Production Testing").Visible = False
pf.PivotItems("PVT").Visible = False
pf.PivotItems("Regression Testing").Visible = False
pf.PivotItems("Unit Testing").Visible = False

Set pf = pt.PivotFields("Assigned to App")
pf.Orientation = xlRowField

Set pf = pt.PivotFields("Assigned To Team")
pf.Orientation = xlColumnField

Set pf = pt.PivotFields("Assigned to App")
pf.Orientation = xlDataField
pf.Function = xlCount

MINCUS1308
06-20-2017, 07:53 AM
I have experienced days of frustration while trying to filter pivot tables with macros - I know your pain.
You might try creating an array of your pivot items and then looping through the array and setting visibility for each item.

MINCUS1308
06-20-2017, 07:55 AM
on a side note... the statement:

With ActiveSheet
Lastrow = ActiveSheet.UsedRange.Rows.Count
End With
Is redundant.
you should be able to accomplish the same thing with

Lastrow = ActiveSheet.UsedRange.Rows.Count

BenChod
06-20-2017, 08:07 AM
Thanks for responding and I hear you. It's a real pain in the arse. Here's what I came up with based on your recommendation but I am getting an error: Compile error: Variable required - can't assign to this expression


For Each pf.PivotItems In _
pt.PivotFields("Phase Found In")
Select Case pf.PivotItems
Case "Integrated Systems Testing", "User Acceptance Test (UAT)", "End to End Testing (ETE)"
pf.PivotItems.Visible = True
Case Else
pf.PivotItems.Visible = False
End Select
Next pf.PivotItems

MINCUS1308
06-20-2017, 08:29 AM
ahh...
The debug is landing on your for statement - correct?
Not the let statement in your initial post?

A quick search led me here:
https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/variable-required-can-t-assign-to-this-expression

I think the increment on your loop is the problem.
Like I said I HATE WORKING WITH PIVOT TABLES because of stupid stuff like this

BenChod
06-20-2017, 08:32 AM
Correct, the debug is landing on pf.PivotItems

MINCUS1308
06-20-2017, 08:46 AM
I'm positive that there is a "correct" method to resolving your problem #GuruPleaseHelpMe!
As my tag line reads: I have no idea what I'm doing...

But there is always more than one way to skin a cat.
Somewhere you have data that is your "pivotitems".
You could step through the data and create an array of the items.
Then step through your array just as you tried to do with the pivot Items.
Its not pretty but that would work.

Paul_Hossler
06-20-2017, 11:14 AM
Thanks for responding and I hear you. It's a real pain in the arse. Here's what I came up with based on your recommendation but I am getting an error: Compile error: Variable required - can't assign to this expression


For Each pf.PivotItems In _
pt.PivotFields("Phase Found In")
Select Case pf.PivotItems
Case "Integrated Systems Testing", "User Acceptance Test (UAT)", "End to End Testing (ETE)"
pf.PivotItems.Visible = True
Case Else
pf.PivotItems.Visible = False
End Select
Next pf.PivotItems



The For Each loop is wrong

Not tested, but try something like this




Dim PI as PivotItem

For Each PI in In pt.PivotFields("Phase Found In") .PivotItems

Select Case PI.Caption

Case "Integrated Systems Testing", "User Acceptance Test (UAT)", "End to End Testing (ETE)"
PI.Visible = True
Case Else
PI.Visible = False
End Select

Next

Paul_Hossler
06-20-2017, 11:20 AM
Can you post a workbook with enough dummy data to show the issue?

This seems to be the area causing problems??



Set pf = pt.PivotFields("Phase Found In")
pf.Orientation = xlPageField
pf.EnableMultiplePageItems = True
pf.ClearAllFilters
pf.PivotItems("Assembly Test").Visible = False
pf.PivotItems("Not a Testing Phase").Visible = False
pf.PivotItems("Performance/Load Testing").Visible = False
pf.PivotItems("Production Testing").Visible = False
pf.PivotItems("PVT").Visible = False
pf.PivotItems("Regression Testing").Visible = False
pf.PivotItems("Unit Testing").Visible = False



I did a very simple Recorder macro and it seems to work so maybe something else is happening



Sub Macro1()
With ActiveSheet.PivotTables("PivotTable1").PivotFields("AAA")
.EnableMultiplePageItems = True
.CurrentPage = "(All)"
.PivotItems("A").Visible = False
.PivotItems("C").Visible = False
.PivotItems("E").Visible = False
End With
End Sub



19548

BenChod
06-20-2017, 03:40 PM
Thanks, that worked. Really appreciate the help. Now I don't have to list out all items that I want to exclude.