Pivot filter VBA help!!!

[deadlyliquidx]

Hi all,
I have never been confused in my life with VBA!! But first time for everything, if you guys get this, you would be gods amongst men.

I am trying to filter an OLAP cube pivot table (pivot table 5) to a range of cells ("store list") on the pivot field ( "[Location].[Location].[Store]").

Sub Test2()

    Dim pi As PivotItem
    With Worksheets("Sheet1").PivotTables("PivotTable5").PivotFields("[Location].[Location].[Store]")
        .ClearAllFilters
        For Each pi In .PivotItems
            pi.Visible = WorksheetFunction.CountIf(ActiveSheet.Range("Storelist"), pi.Name) > 0
        Next pi
    End With

End Sub

This code works perfectly when doing it on a non-cube based local pivot but on the pivot table I get this error message "unable to set visible property of pivotitem class".
It recognizes the pivot, clears filters and everything but the problem is at Pi.visible, where it for some reason cannot set the pivot filter.
I would have provided a worksheet but it wouldent have mattered because you need to be connected to my OLAP to see the problem because this code works fine on pivots connected to local spreadsheets.

Please HELP!!
Thanks Guys
XOXO

 

[Jerry Sullivan]

Hi @deadlyliquidx,

The PivotItem names for OLAP PivotTables use MDX syntax references. So for your code to work, your range "Storelist" would need to include the entire PivotItem names, like
[Location].[Location].&[Store123]
[Location].[Location].&[Store456]
[Location].[Location].&[Store789]

A simpler and more user-friendly approach is to have just the store names in the worksheet
Store123
Store456
Store789
... and to build the MDX syntax references using VBA.

When filtering an OLAP PivotTable for multiple items, it's usually more efficient to use the PivotField.VisibleItemsList property instead of iterating through each item.
One catch with that is it will fail if any of the PivotItems passed to that property don't currently exist.

Here's a function you can try that tests if each item exists....

Private Function sOLAP_FilterByItemList(ByVal pvf As PivotField, _
   ByVal vItemsToBeVisible As Variant, _
   ByVal sItemPattern As String) As String

'--filters an OLAP pivotTable to display a list of items,
'    where some of the items might not exist
'--works by testing whether each pivotitem exists, then building an
'    array of existing items to be used with the VisibleItemsList property
'--requires Excel 2007 or later

'--Input Parameters:
'  pvf                pivotfield object to be filtered
'  vItemsToBeVisible  1-D array of strings representing items to be visible
'  sItemPattern       string that has MDX pattern of pivotItem reference
'                     where the text "ThisItem" will be replaced by each
'                     item in vItemsToBeVisible to make pivotItem references.
'                     e.g.: "[tblSales].[product_name].&[ThisItem]"
   
 Dim lFilterItemCount As Long, lNdx As Long
 Dim vFilterArray As Variant
 Dim vSaveVisibleItemsList As Variant
 Dim sReturnMsg As String, sPivotItemName As String
 
 '--store existing visible items
 vSaveVisibleItemsList = pvf.VisibleItemsList
 
 If Not (IsArray(vItemsToBeVisible)) Then _
   vItemsToBeVisible = Array(vItemsToBeVisible)
 ReDim vFilterArray(1 To _
   UBound(vItemsToBeVisible) - LBound(vItemsToBeVisible) + 1)
 pvf.Parent.ManualUpdate = True
 
 '--check if pivotitem exists then build array of items that exist
 For lNdx = LBound(vItemsToBeVisible) To UBound(vItemsToBeVisible)
   '--create MDX format pivotItem reference by substituting item into pattern
   sPivotItemName = Replace(sItemPattern, "ThisItem", vItemsToBeVisible(lNdx))
   
   '--attempt to make specified item the only visible item
   On Error Resume Next
   pvf.VisibleItemsList = Array(sPivotItemName)
   On Error GoTo 0
   
   '--if item doesn't exist in field, this will be false
   If LCase$(sPivotItemName) = LCase$(pvf.VisibleItemsList(1)) Then
      lFilterItemCount = lFilterItemCount + 1
      vFilterArray(lFilterItemCount) = sPivotItemName
   End If
 Next lNdx
 
 '--if at least one existing item found, filter pivot using array
 If lFilterItemCount > 0 Then
   ReDim Preserve vFilterArray(1 To lFilterItemCount)
   pvf.VisibleItemsList = vFilterArray
 Else
   sReturnMsg = "No matching items found."
   pvf.VisibleItemsList = vSaveVisibleItemsList
 End If
 pvf.Parent.ManualUpdate = False

 sOLAP_FilterByItemList = sReturnMsg
End Function

Here's an untested calling example. The sItemPattern argument might need to be modified depending on the design of your cube.
The easiest way to get that correct pattern is use the macro recorder while manually filtering that pivotfield to show a few items.

Sub CallingExample()
'--example showing call to function sOLAP_FilterByItemList

 Dim pvt As PivotTable
 Dim sErrMsg As String, sTemplate As String
 Dim vItemsToBeVisible As Variant

 On Error GoTo ErrProc
 With Application
   .EnableCancelKey = xlErrorHandler
   .ScreenUpdating = False
   .DisplayStatusBar = False
   .EnableEvents = False
 End With
   
 '--read filter items into a 1-D array
 vItemsToBeVisible = Application.Transpose( _
   ActiveSheet.Range("Storelist").Value)

 Set pvt = Worksheets("Sheet1").PivotTables("PivotTable5")
 '--call function
 sErrMsg = sOLAP_FilterByItemList( _
   pvf:=pvt.PivotFields("[Location].[Location].[Store]"), _
   vItemsToBeVisible:=vItemsToBeVisible, _
   sItemPattern:="[B][COLOR="#0000CD"][Location].[Location].&[ThisItem][/COLOR][/B]")
 
ExitProc:
 On Error Resume Next
 With Application
   .EnableEvents = True
   .DisplayStatusBar = True
   .ScreenUpdating = True
 End With
 If Len(sErrMsg) > 0 Then MsgBox sErrMsg
 Exit Sub
 
ErrProc:
 sErrMsg = Err.Number & " - " & Err.Description
 Resume ExitProc
End Sub

 

[deadlyliquidx]

Thanks but I already found a method that takes a list and changes slicers of an OLAP
downside is I have to put the technical location location store name but it works perfectly without much compilations.
But thanks for your help.

 

[deadlyliquidx]

But actually your idea is interesting for the future, I just don't know how to implement it to my sheet. I tried with the coding you gave.
So say the Filter list as it is named on the physical slicer is in column B, the slicer is called Slicer_location, the pivot is called pivottable2 on Sheet1 the field is called location
Right now i have the reference list to the store name to the actual variable on OLAP.
If I can eliminate having to do that reference list to the technical and have it filter automatically based on the list, that would be cool.
Can you give me a code to help me out with that, thanks!

Thansk!!

 

[Jerry Sullivan]

I'm not sure I understand where your list of items to be filtered is located. It sounds your saying it's in Column B. Is it in a range named "Storelist" like in your OP example?

If so, you would just need to modify this line of the calling code example...

Set pvt = Worksheets("Sheet1").PivotTables("[B][COLOR="#0000CD"]PivotTable2[/COLOR][/B]")

As I noted previously the sItemPattern argument might need to be modified depending on the design of your cube.
The easiest way to get that correct pattern is use the macro recorder while manually filtering that pivotfield to show a few items.

If you aren't able to get that to work, please post the code generated by the macro recorder.

 

[Jerry Sullivan]

I'm not sure I understand where your list of items to be filtered is located? It sounds your saying it's in Column B. Is it in a range named "Storelist" like in your OP example.

If so, you would just need to modify this line of the calling code example...

Set pvt = Worksheets("Sheet1").PivotTables("[B][COLOR="#0000CD"]PivotTable2[/COLOR][/B]")

As I noted previously the sItemPattern argument might need to be modified depending on the design of your cube.
The easiest way to get that correct pattern is use the macro recorder while manually filtering that pivotfield to show a few items.

If you aren't able to get that to work, please post the code generated by the macro recorder.

 

[deadlyliquidx]

When I try your code, I get "no match found", maybe I am explaining this wrong.
So basically I want to filter my OLAP pivot without having to individually select stores on the filter by having it checking off all the filters based on a range with a list of stores I want to be checked off.
After a whole day of trying to figure it out I was able to do it with a slicer and this code:
Downside is that I cannot use the name for the store that is on the slicer, I have to refer to the code name that is in the system:
So I column A is filled with the technical name for each store in the [location].[location].&[store] format.

Sub SetSelection()


Dim ws As Worksheet
Dim myArr() As String
Dim i As Long
Dim j As Long

Set ws = Worksheets("Sheet1")
j = 1
For i = 1 To 300
    If IsError(ws.Range("A" & i).Value) Then GoTo skip
    If ws.Range("A" & i).Value = "" Then GoTo skip
    ReDim Preserve myArr(1 To j)
    myArr(j) = ws.Range("A" & i).Value
    j = j + 1
skip:
Next i
VisibleSlicerItemsList = myArr


    Dim sc As SlicerCache
    Dim s As Slicer
    Set sc = ActiveWorkbook.SlicerCaches("Slicer_location") ' Name of slicer
     Set s = sc.Slicers(1)
    sc.VisibleSlicerItemsList = myArr
End Sub

Disregard the other stuff, it just tells the code to ignore blanks and errors.
So basically the list vlookups the stores on a seperate sheet and column A is filled with their OLAP equivalent.
In the IDEAL world I can just use the store name as written in the filter and the code will automatically convert it to its olap form.
for example Store1 is now [location].[location]&[00001], i would rather column A just say store1 as it is on the filter and have a code that automatically translates it into [location].[location]&[00001].

Thanks!

 

[Jerry Sullivan]

See this thread for an explanation of the difference between Member Names and Member Keys.
http://www.mrexcel.com/forum/excel-...ap-pivot-using-visual-basic-applications.html

Do you have a lookup table that lists each store name and it's corresponding key? e.g. Name "Store1" has Key 00001

The code I suggested could easily be modified to do that lookup or conversion. That would be simpler and faster than the conversion code in the other thread I linked.

 

[deadlyliquidx]

Can you show me the modification, i dont understand it.
Thanks!

 

[Jerry Sullivan]

Please provide the following and I will suggest some modified code:

1. Use the macro recorder while manually filtering that pivotfield to show a few items. Then post the recorded code.

2. Post a small screen shot of the lookup table that maps each store name to its corresponding key. e.g. Name "Store1" --> Key 00001. Include a description of how that lookup table can be referenced (its sheet and range address or defined Named Range).