Retrieve VisibleItemsList from OLAP PivotTable

[twoodsmall79]

I have a sheet with a set of Olap Pivot tables that I would like to be able to update with the push of a button to the VisibleItemsList in 1 of the cubes. The below code works to grab the name of the Pivot Field if there is only 1 item selected but there are times when we will need to grab more than 1 hierarchy. Any help is greatly appreciated.

myCustomer = ws1.PivotTables("HGPLHistory").PivotFields("[Soldto].[Customer Hierarchy].[Hierarchy1]").CurrentPageName

 

[Jerry Sullivan]

You can set the VisibleItemsList of PivotField of one PivotTable after reading the VisibleItemsList property from another.

Here is a snippet that relies on some assumptions about the two tables having matching field names and PivotItems.
Validation and error handling should be added before using this for an application.

 '--example showing how to set the visibleItemList property of one
 '    OLAP pivottable's field based on the visibleItemList of another
 
 '--this example assumes both pivots have matching field names and that
 '    all pivotItems in VisibleItemList exist.
 
 Dim pvtSource As PivotTable, pvtDest As PivotTable
 
 Const sFIELDNAME As String = "[tblSales].[product_name].[product_name]"
 
 With ActiveSheet
   Set pvtSource = .PivotTables("PivotTable2")
   Set pvtDest = .PivotTables("PivotTable1")
 End With
 
 pvtDest.PivotFields(sFIELDNAME).VisibleItemsList = _
   pvtSource.PivotFields(sFIELDNAME).VisibleItemsList

At the risk of stating the obvious, if you want that field of multiple PivotTables that share the same PivotCache to be sync'd, it's better an easier to use Slicers.

If the Pivots don't share the same PivotCache, but do have the same PivotField MDX reference string and PivotItems, then this technique could be useful.

Be aware that setting a PivotField's VisibleItemsList property will fail if just one of the items in the list doesn't exist.

 

[twoodsmall79]

Jerry I have seen many of your posts and was hoping you would be able to help. You are a great asset here.

I have a bad habit of moving down 1 path and not seeing a much easier solution. The slicer was the perfect solution and works great. Thanks for your help.

 

[TomDV]

First of all, thanks a lot for the initial answer. This already helped me a great deal in the past.
I couldn't work with slicers as I had to group one of the dimensions in a few different ways, so I was forced to create a new data source connection for every Pivot Table.

Unfortunately I have run into an issue now.
Part of the dimensions in the OLAP cube I am using are a layered hierarchy, for example:

L1: "[Items].[Product Hierarchy].[Business Group]"
L2: "[Items].[Product Hierarchy].[Retail Department]"
L3: "[Items].[Product Hierarchy].[Retail Group]"
L4: "[Items].[Product Hierarchy].[Item]"

When I record a macro and set this filter to a certain value, it seems to set each of these levels individually.
In the example below I only selected something on the first level, which is why it shows an empty array on the lower levels.

ActiveSheet.PivotTables("BELGIUM_RT_DATE_PROCOM").PivotFields( _
        "[Items].[Product Hierarchy].[Business Group]").VisibleItemsList = Array( _
        "[Items].[Product Hierarchy].[Business Group].&[Beauty Make-Up]")
    ActiveSheet.PivotTables("BELGIUM_RT_DATE_PROCOM").PivotFields( _
        "[Items].[Product Hierarchy].[Retail Department]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("BELGIUM_RT_DATE_PROCOM").PivotFields( _
        "[Items].[Product Hierarchy].[Retail Group]").VisibleItemsList = Array("")
    ActiveSheet.PivotTables("BELGIUM_RT_DATE_PROCOM").PivotFields( _
        "[Items].[Product Hierarchy].[Item]").VisibleItemsList = Array("")

However when I try to use your code to sequentially set each of the levels, the code runs without an error, but it didn't actual change the report filter of the destination pivot.
I tried the same on a dimension that isn't layered using the same Pivot Tables and that worked like a charm.

Dim pvtSource As PivotTable, pvtDest As PivotTable
 
 Const sFIELDNAME1 As String = "[Items].[Product Hierarchy].[Business Group]"
 Const sFIELDNAME2 As String = "[Items].[Product Hierarchy].[Retail Department]"
 Const sFIELDNAME3 As String = "[Items].[Product Hierarchy].[Retail Group]"
 Const sFIELDNAME4 As String = "[Items].[Product Hierarchy].[Item]"
 Const sFIELDNAME5 As String = "[Items].[Business Group].[Business Group]"
 
Set pvtSource = Slicer.PivotTables("FAKE_SLICER")
Set pvtDest = TEST.PivotTables("TEST")

 'Trying to set the layered dimension:
 pvtDest.PivotFields(sFIELDNAME1).VisibleItemsList = _
   pvtSource.PivotFields(sFIELDNAME1).VisibleItemsList
 pvtDest.PivotFields(sFIELDNAME2).VisibleItemsList = _
   pvtSource.PivotFields(sFIELDNAME2).VisibleItemsList
 pvtDest.PivotFields(sFIELDNAME3).VisibleItemsList = _
   pvtSource.PivotFields(sFIELDNAME3).VisibleItemsList
 pvtDest.PivotFields(sFIELDNAME4).VisibleItemsList = _
   pvtSource.PivotFields(sFIELDNAME4).VisibleItemsList
   
'Setting a dimension that isn't layered:
 pvtDest.PivotFields(sFIELDNAME5).VisibleItemsList = _
   pvtSource.PivotFields(sFIELDNAME5).VisibleItemsList
 
End Sub

Is this something that just isn't supported in Excel 2013? Or am I doing something wrong?
I'm using Excel 2010 in case that would be relevant.

Thanks in advance to anyone trying to figure this out.

PS.
The fact I changed the code from using the ActiveSheet to VBA sheet names isn't the problem, as setting the non-layered dimension in the same sub worked.

 

[TomDV]

Is this something that just isn't supported in Excel 2013? Or am I doing something wrong?
I'm using Excel 2010 in case that would be relevant.

I only just noticed I talked about 2 different Excel versions. As I can't seem to find an "edit post" button, I'll do it this way.
To be clear, I am using Excel 2010.

 

[TomDV]

Never mind, I made a quite silly mistake.
On the source pivot for the layered dimension, I didn't have the "Select Multiple Items" checkbox marked.
Apparently that doesn't throw an error message when running the code.

 

[Delmundo]

Hi Jerry and All,
Can you please help me understand each of these code ? Why is the Array filled in the first

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Organization].[ServiceLine].[Level6]").VisibleItemsList = Array( _
"[Organization].[ServiceLine].[Level6].&[247495394]")

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Organization].[ServiceLine].[Level7]").VisibleItemsList = Array("")