weaverjohn
New Member
- Joined
- Oct 18, 2011
- Messages
- 9
Hi Gang:
I am working with an OLAP Pivot Table that I need to filter for a variable number of items using VBA. The first issue is that one of the fields is a key and the label is actually the real world value I'm matching. I managed that problem with the following code where I loop through a named range, take the text value of the project number, filter the PT for that label, and then record the key value in a cell on the worksheet that I can use later. (For those of you that have posted questions on how to filter labels - this is what you have been looking for)
The key there is filtering the PT Captions and then getting the actual key (which in this case is the PivotItems(1).Name)
I haven't been able to create a Caption filter for more than one caption at a time. If anyone knows how to do that I would really apprecaite any clues you might have.
The alternate path is that once I have the key values I can filter the table. The only way I have been able to accomplish this is to have a Select statement for each number of projects - like so:
Notice that the parameters after the '=Array(' portion is the list of values that I need to filter for and there are a variable number of them. The case statement works, but then the code is very long and I need a case statement for every possible number of variables, Not something that would be robust code, but more brute force and I could perhaps code up to a potential of 100 projects in the list.
A better solution would be to either figure out how to make the VisibleItemList additive, where I could simple call the array with each value I needed - Which I tried but couldn't make work. The other potential I chased was to create a loop - which doesn't work, because the array is a list of values separated by commas.
So the actual questions are these:
1) Does anyone know how to filter for multiple captions?
2) Does anyone know how I could create the required array with a loop and pass it?
3) Does anyone know how to make either the captions filter or the VisibleItemList additive (in other words sort of set multiple filters)?
4) Does anyone have an alternate solution that I haven't thought of?
Thanks for taking the time to read this far and for any assistance you might provide a VBA rookie.
John
I am working with an OLAP Pivot Table that I need to filter for a variable number of items using VBA. The first issue is that one of the fields is a key and the label is actually the real world value I'm matching. I managed that problem with the following code where I loop through a named range, take the text value of the project number, filter the PT for that label, and then record the key value in a cell on the worksheet that I can use later. (For those of you that have posted questions on how to filter labels - this is what you have been looking for)
Code:
For i = 1 To Control.Range("arprojects").Count
strProj = Control.Cells(i + 2, 2)
Control.Cells(y, 6).Value = Control.Cells(i + 2, 2).Value
'set the filter and record the index key
Labor.PivotTables("pvtLabor").PivotFields( _
"[Project_Task - Lowest Level Task Only].[Project]").ClearAllFilters
Labor.PivotTables("pvtLabor").PivotFields( _
"[Project_Task - Lowest Level Task Only].[Project]").PivotFilters.Add Type:= _
xlCaptionEquals, Value1:=strProj
Control.Cells(i + 2, 7) = Labor.PivotTables("pvtLabor").PivotFields("[Project_Task - Lowest Level Task Only].[Project]").PivotItems(1).Name
y = y + 1
Next i
The key there is filtering the PT Captions and then getting the actual key (which in this case is the PivotItems(1).Name)
I haven't been able to create a Caption filter for more than one caption at a time. If anyone knows how to do that I would really apprecaite any clues you might have.
The alternate path is that once I have the key values I can filter the table. The only way I have been able to accomplish this is to have a Select statement for each number of projects - like so:
Code:
Select Case Control.Range("arprojects").Count
Case 1
'Where we have just one Label we can filter for a single label.
strFilters = Control.Cells(3, 6)
ActiveSheet.PivotTables("pvtLabor").PivotFields( _
"[Project_Task - Lowest Level Task Only].[Project]").ClearAllFilters
ActiveSheet.PivotTables("pvtLabor").PivotFields( _
"[Project_Task - Lowest Level Task Only].[Project]").PivotFilters.Add Type:= _
xlCaptionEquals, Value1:=strFilters
Case 2
Labor.PivotTables("pvtLabor").PivotFields( _
"[Project_Task - Lowest Level Task Only].[Project]").ClearAllFilters
Labor.PivotTables("pvtLabor").PivotFields( _
"[Project_Task - Lowest Level Task Only].[Project]").VisibleItemsList = Array( _
Control.Cells(3, 7).Value, _
Control.Cells(4, 7).Value)
Case 3
Labor.PivotTables("pvtLabor").PivotFields( _
"[Project_Task - Lowest Level Task Only].[Project]").ClearAllFilters
Labor.PivotTables("pvtLabor").PivotFields( _
"[Project_Task - Lowest Level Task Only].[Project]").VisibleItemsList = Array( _
Control.Cells(3, 7).Value, _
Control.Cells(4, 7).Value, _
Control.Cells(5, 7).Value)
End Select
Notice that the parameters after the '=Array(' portion is the list of values that I need to filter for and there are a variable number of them. The case statement works, but then the code is very long and I need a case statement for every possible number of variables, Not something that would be robust code, but more brute force and I could perhaps code up to a potential of 100 projects in the list.
A better solution would be to either figure out how to make the VisibleItemList additive, where I could simple call the array with each value I needed - Which I tried but couldn't make work. The other potential I chased was to create a loop - which doesn't work, because the array is a list of values separated by commas.
Code:
Labor.PivotTables("pvtLabor").AllowMultipleFilters = True
For i = 1 To Control.Range("arprojects").Count
Labor.PivotTables("pvtLabor").PivotFields("[Project_Task - Lowest Level Task Only].[Project]").VisibleItemsList = Array( _
Control.Cells(i + 2, 7).Value)
Next i
So the actual questions are these:
1) Does anyone know how to filter for multiple captions?
2) Does anyone know how I could create the required array with a loop and pass it?
3) Does anyone know how to make either the captions filter or the VisibleItemList additive (in other words sort of set multiple filters)?
4) Does anyone have an alternate solution that I haven't thought of?
Thanks for taking the time to read this far and for any assistance you might provide a VBA rookie.
John
Last edited: