Pivot Table VisibleItemList Question

[weaverjohn]

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)

  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:

  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.

  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

 

[weaverjohn]

Some days it helps to rephrase your search criteria. Seems I found the answer right here to a loarge part of the puzzle.

Thanks to Andrew Poulsom who answered one of the questions in thread:
http://www.mrexcel.com/forum/showthread.php?t=543610&highlight=VisibleItemList

It turned out to be two simple lines of code

  arFilters = Application.Transpose(Control.Range(Cells(3, 7), Cells(Control.Cells(2, 6).Value + 2, 7)).Value)
  Labor.PivotTables("pvtLabor").PivotFields("[Project_Task - Lowest Level Task Only].[Project]").VisibleItemsList = arFilters

So only of the questions remain really - does anyone know how to filter for multiple captions

PivotFields("[Project_Task - Lowest Level Task Only].[Project]").PivotFilters.Add Type:= _
          xlCaptionEquals, Value1:=strProj

Many thanks in advance. Bill I have several of your books and am a regular visitor here. My thanks to Bill, Andrew and all the other folks that popup on MrExcel.com. It's a huge resource for me.