asked on

Help me Sync 2 Slicers with Different Data Sources

I've attached a Sample workbook with the code that I'm using, here is a description of my process:
1. Import External Data into Excel Table called "CleaningTable"
2. Clean data, Add calculated columns, etc...
3. Export CleaningTable to PowerPivot called "CleanedData"
4. Build Slicer1 off of RegionCode in CleanedData
5. Build Slicer2 off of RegionCode in CleaningTable
7. Attempt to sync slicers using below code:

Private Sub Worksheet_PivotTableUpdate _
    (ByVal Target As PivotTable)
Dim wb As Workbook
Dim scShort As SlicerCache
Dim scLong As SlicerCache
Dim siShort As SlicerItem
Dim siLong As SlicerItem

On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ThisWorkbook
Set scShort = wb.SlicerCaches("Slicer_RegionCode")
Set scLong = wb.SlicerCaches("Slicer_RegionCode1")

scLong.ClearManualFilter

For Each siLong In scLong.VisibleSlicerItems
    Set siLong = scLong.SlicerItems(siLong.Name)
    Set siShort = Nothing
    On Error Resume Next
    Set siShort = scShort.SlicerItems(siLong.Name)
    On Error GoTo errHandler
    If Not siShort Is Nothing Then
        If siShort.Selected = True Then
            siLong.Selected = True
        ElseIf siShort.Selected = False Then
            siLong.Selected = False
        End If
    Else
        siLong.Selected = False
    End If
Next siLong

exitHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

errHandler:
    MsgBox err.Description
    Resume exitHandler

End Sub

Open in new window

I keep getting "Application-define or object-defined error".

End-User selections would always be in Slicer_RegionCode on the Lead worksheet. I would like Slicer_RegionCode1 on the Follow worksheet to mimic the selections. Both data sources have the same fields and data, the only reason I'm doing this is because you can't "showdetails" on data in PowerPivot that has been filtered by more than 1 Slicer.
SyncSlicerProblem.xlsm

-Polak

ASKER

Things I've learned in the past Day....
For Slicers connected to PowerPivot (OLAP) tables the .VisibleSlicerItemsList property is only one that can be viewed.

Add this code to my sample workbook and run it after making a selection in Slicer_RegionCode.

Sub View VisibleSlicerItemsList
Dim scLong As SlicerCache
Dim v As Variant

Dim wb As Workbook
Dim siLong As SlicerItem
Dim s As Slicer
Set wb = ThisWorkbook
Set scLong = wb.SlicerCaches("Slicer_RegionCode")
v = scLong.VisibleSlicerItemsList
Dim ss As String
ss = v(1)
MsgBox ss
End Sub

Open in new window

According to what I'm reading I can then Split the value of the VisibleSlicersItemsList to my other non-OLAP slicers. (I don't know how to write the VBA to do that though)... but before you try and do it for me....

It doesn't appear that the property value for VisibleSlicersItemsList can articulate multiple filtering critera. Ala, if your selection is A & D in the Lead Slicer's VisibleSlicersItemsList it will only provide you with

[CleanData].[RegionCode].&[A]

Open in new window

I believe I've come full circle with the problem I was trying to work around.... MDX not allowing multiple filtering critera to show details on OLAP data...

If an expert would confirm that I'm shit-out-of-luck; I'll award points and begin the process of rebuilding my workbook so that all slicers data sources are based off of the CleaningTable rather than PowerPivot. This really sucks.

-Polak

ASKER

Learned more things today... appearently you can write an array for VisbileSlicerItemsList to pull out mulitple selections in the OLAP slicer. For example,

sC.VisibleSlicerItemsList = Array(“[Date].[Calendar Year].&[2005]”, “[Date].[Calendar Year].&[2006]”)

Open in new window

However, adapting this all to my sample workbook code is all a way bit over my head! Anybody out there that knows what I'm talking about?

-Polak

ASKER

Progress!
I've gotten this far with my code, (updated Sample workbook attached), it correctly passes some of the OLAP slicer's sliceritems to the non-OLAP slicer; however, not all the behavior follows correctly. For example....

When any single selection is made from the OLAP slicer, the Regular Slicer follows it correctly. When you hold CTRL when all Slicer Items are selected, only deselecting D & E will Follow correctly; whereas, deselecting A,B,or C will not Follow into the Regular Slicer. Selecting A and then holding CTRL and Selecting B will Follow correctly; however, selecting B and then holding CTRL and Selecting C will not Follow correctly. There are many other instances that do not Follow correctly.

Can anyone help me get across the finish line!?!?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim scOLAP As SlicerCache
Dim scList As SlicerCache
Dim sO As Slicer
Dim sL As Slicer
Dim si As SlicerItem
Dim i As Integer
Dim svalue As String

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_RegionCode")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_RegionCode1")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)

For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)

svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[CleanedData].[RegionCode].&[", ""), "]", "")

For Each si In scList.SlicerItems
If svalue = si.SourceName Then
si.Selected = True
Else
si.Selected = False
End If

Next

Next

End Sub

Open in new window

SyncSlicerProblem-07.05-2015.xlsm

ASKER CERTIFIED SOLUTION

-Polak

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

-Polak

ASKER

This bit of code will sync your OLAP Slicers to your Non-OLAP slicers.