How to Sync two Slicers to two Different OLAP Data Sources

[theyee02]

Hi, here is the situation.



I have one pivot table that lists products and various data associated with them such as color/size/qty etc and has a slicer that controls which Styles are selected.

I have another pivot table that I have created to show the Status of styles, such as active, discontinued, future discontinued, closeout, etc.

They both have separate OLAP data sources and I have been scouring online for a way to make it so that the Status pivot with slicer has the same items selected as the first pivot/slicer.

The closest I have found is this code that is originally for syncing a an OLAP pviot to a non-OLAP pivot. I found a comment that suggested a modification to make it OLAP to OLAP, but have been unable to make it work.

I changed the 2 items in red to match the 2 slicers I'm working with. I believe I need to change the [CleanedData].[RegionCode] portion in the code to suit my needs, but I'm not quite sure how to do so. From Debug.print, I've figured the path would be [ProductStyleColorSize].[Style].[Style], but I'm unsure how to work that in.

So far, if I run the code as I have posted, with the changes suggested, I get a Error 13 Type Mismatch at "Set sO = scOLAP.SlicerCacheLevels(1)". I assume I'll also get that at the next line, since it has the same modification.

Please Login or Register  to view this content.

The modification that was suggested on the post was

Set SL = scList.SlicerCacheLevels(1)
For Each sI In SL.SlicerItems
.......
Next

Any help would be greatly appreciated. Thank you!

[dflak]

Create a pivot table from one of the data sets where the report filter is the column the slicer controls. Set the slicer up to control both pivot tables.

Then do the same thing with the other data set.

Then create an event so that when one of the filters is changed copy the value to the other filter. Don't forget to turn off events between detection and copying or you will wind up in an infinite loop.

This technique will only work if both data sets have the same values for the field.