• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple slicers control 2 pivot table with 2 Data Source

D

Dokat

Member
Hi,

I have 2 pivot tables from 2 different data sources...I am trying to synchronize multiple slicers to control both pivot tables.

I used below code from "Chiriro" for single slicer to control two pivot tables. My challenge right now is how can i modify the code so that more than one slicer controls both pivot tables with different data source.

Code: 
PrivateSub Workbook_SheetPivotTableChangeSync(ByVal Sh AsObject, ByVal Target As PivotTable)

    Const MainSlicer AsString = "Slicer_Brand"
    Const Slicer1 AsString = "Slicer_Brand1"
   Dim pvt As PivotTable
   Dim ws As Worksheet
   ForEach ws In ThisWorkbook.Worksheets
       ForEach pvt In ws.PivotTables
            pvt.ManualUpdate = True
       Next pvt
   Next ws
  
   If Target.Name = "PivotTable1"Then

        Application.ScreenUpdating = False

        Application.EnableEvents = False

       OnErrorGoTo Catch

       Dim slMainSlicer As Slicer
       Dim slMainSlicercache As SlicerCache
       Dim slItem As SlicerItem

       Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer)

        ActiveWorkbook.SlicerCaches(Slicer1).ClearManualFilter
    
       ForEach slItem In ThisWorkbook.SlicerCaches(Slicer1).SlicerItems
           If slMainSlicercache.SlicerItems(slItem.Name).Selected Then
                slItem.Selected = True
           Else
                slItem.Selected = False
           EndIf
       Next

   EndIf

Catch:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

   ForEach ws In ThisWorkbook.Worksheets
       ForEach pvt In ws.PivotTables
            pvt.ManualUpdate = False
       Next pvt
   Next ws
EndSub
 
@Docat

Question is quite interesting...But what I feel is there is no code required to manage or control pivot tables.
 
@Dokat.

Okay agree with u.

Sorry just giving u an idea rather then code as checking your question on mobile.

Here what we can do....Change this below line in your code.

If Target.Name = "PivotTable1" and Target.Name = "PivotTable2 "Then


Hope this helps
 
Upload small sample file representing your set up to get better help (and how each slicer should interact with each other).
 
Don't use your actual file exported without underlying data. That's not helpful. As you can't make any modification to PivotTable without underlying data.

Just build small sample table (25 rows or so) with appropriate fields for both your source table. Build Pivot & Slicer from it.
 
I used this code it works fine when all the items in the slicer caches matches in both Pivot table but gives error if both slicers dont have the exact items listed. Any way to work around this? Thanks

Code: 
Dim mbNoEvent As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oScSlicer_Brand As SlicerCache
    Dim oScSlicer_Manufacturer As SlicerCache
    Dim oScSlicer_Category As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem

    Dim bUpdate As Boolean
   
    If mbNoEvent Then Exit Sub
    mbNoEvent = True
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oPT In oSc.PivotTables
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                If oSc.Name Like "*Slicer_Category*" Then
                    Set oScSlicer_Category = oSc
                ElseIf oSc.Name Like "*Slicer_Brand*" Then
                    Set oScSlicer_Brand = oSc
                ElseIf oSc.Name Like "*Slicer_Manufacturer*" Then
                    Set oScSlicer_Manufacturer = oSc
                End If
                Exit For
            End If
        Next
        If Not oScSlicer_Category Is Nothing And Not oScSlicer_Brand Is Nothing And Not oScSlicer_Manufacturer Is Nothing Then Exit For
    Next
    If Not oScSlicer_Category Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Category.Name, 7, 3) And oSc.Name <> oScSlicer_Category.Name Then
               
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Category.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScSlicer_Manufacturer Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Manufacturer.Name, 7, 3) And oSc.Name <> oScSlicer_Manufacturer.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Manufacturer.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScSlicer_Brand Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Brand.Name, 7, 3) And oSc.Name <> oScSlicer_Brand.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Brand.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    mbNoEvent = False
    Application.ScreenUpdating = bUpdate
End Sub
 
Upload sample that accurately represent your actual data and set up. Your uploaded sample does not meet the requirement.

Also, please stop posting same question in multiple threads and starting new one. It makes it hard to follow and may waste someone's time if you jump from one to the other.
 
Hi,

I attached the sample data source and the workbook that includes the pivot tables. Actual Source data includes 700 K rows of data. This would be the most accurate representation of original data source and the pivot tables. Please let me know if this meets your requirements?

I am trying to control both pivot tables with same slicer. Thank you for your help
 

Attachments

  • 2 Slicers Source Data.xlsm
    42.4 KB · Views: 2
  • 2 Slicer.xlsm
    34.7 KB · Views: 2
Did you upload wrong workbook? Source isn't connected or related to pivot table generated...

What I need is something that mirrors your workbook's set up. With enough data to demonstrate your issue (both the source and pivottable workbook).

And you are now using PowerQuery? In that case, I'd just merge the two table, using concatenated fields that generates related fields that are unique (like [PrimarySKU] & some other column).

At any rate, with PowerQuery and data being loaded to Data Model. You can leverage "VisibleSlicerItemsList". Instead of looping through each slicer items.

See sample usage in below thread.
http://chandoo.org/forum/threads/select-slicers-based-on-the-cell-values-excel-2013.30553/
 
Because of the size of the file i had to load it to Power Query rather than using external connections. I couldnt not merge the tables because its many to many relation ship. I will try to connect using concatenated fields and see if i can i get it to work. Actual dashboard is over 30 MB thus i created a sample file and uploaded only 25 rows data to see if its possible to connect slicers via vba.

Thanks
 
I couldnt not merge the tables because its many to many relation ship.
Click to expand...

Hence, suggestion for concatenated field to generate unique key/ID that can be linked.

Actual dashboard is over 30 MB thus i created a sample file and uploaded only 25 rows data to see if its possible to connect slicers via vba
Click to expand...

Yes, but you need to have set up that represent your actual data. In your sample... it is missing underlying data. Just have source data in same workbook, instead of in separate workbook (for sample). It does not impact code.

Also, you stated not all items are present in corresponding slicer to the other. Your sample has exact match between all items between corresponding slicers.
 
Ok i included the underlying data and added more sample data where it doesnt match all items between correspondings licers. I hope this works and what i am trying to achieve is doable.

Thank you
 

Attachments

  • 2 Slicers.xlsm
    773 KB · Views: 1
I created concatenated field in both tables with primaryskubrand...however i keep getting below error message due to duplications.

upload_2017-1-5_18-8-53.png
 
In those instances you need third table as Lookup table and/or need 2 or more columns to serve as related column (SQL Join). Depending on how you are building relationship/query.

However... Looking at your sample, I'm guessing that this data is brought in from database or some other source. Try and see if you can get your database people or software vendor, to build you "View" or query that will get you merged data set.
 
As for the Code to control both pivots.

What should happen if item in one slicer is selected and there's no corresponding item exist in the other? Since there isn't a way to select nothing from slicer...
 
It should just show the corresponding value in the slicer with selection.

Below code partially works but it gets stuck where it says "oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True" and keeps freezubf . I tested in smaller set of data and it worked fine but when i try with large data it freezes

Code: 
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oScSlicer_Brand As SlicerCache
    Dim oScSlicer_Manufacturer As SlicerCache
    Dim oScSlicer_Category As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem
    Dim bUpdate As Boolean
    Dim mbNoEvent As Boolean
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    For Each oPT In ws.PivotTables
        oPT.ManualUpdate = True
    Next oPT
Next ws
   
    If mbNoEvent Then Exit Sub
    mbNoEvent = True
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oPT In oSc.PivotTables
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                If oSc.Name Like "*Slicer_Category*" Then
                    Set oScSlicer_Category = oSc
                ElseIf oSc.Name Like "*Slicer_Brand*" Then
                    Set oScSlicer_Brand = oSc
                ElseIf oSc.Name Like "*Slicer_Manufacturer*" Then
                    Set oScSlicer_Manufacturer = oSc
                End If
                Exit For
            End If
        Next
        If Not oScSlicer_Category Is Nothing And Not oScSlicer_Brand Is Nothing And Not oScSlicer_Manufacturer Is Nothing Then Exit For
    Next
    If Not oScSlicer_Category Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Category.Name, 7, 3) And oSc.Name <> oScSlicer_Category.Name Then
               
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Category.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScSlicer_Manufacturer Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Manufacturer.Name, 7, 3) And oSc.Name <> oScSlicer_Manufacturer.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Manufacturer.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScSlicer_Brand Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScSlicer_Brand.Name, 7, 3) And oSc.Name <> oScSlicer_Brand.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScSlicer_Brand.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    mbNoEvent = False
    Application.ScreenUpdating = bUpdate
    For Each ws In ThisWorkbook.Worksheets
    For Each oPT In ws.PivotTables
        oPT.ManualUpdate = True
    Next oPT
Next ws


End Sub
 
You didn't answer my question...
Below example from your latest attachment.

Form slicer:
Has "LIQUID", "POWDER", "SHEETS", "UNIT DOSE".

The other Form slicer:
Does not have "SHEETS"

So when "SHEETS" is selected in first Form slicer. What should happen? There is no corresponding item in the second...

And what should happen if an item in second slicer is selected, but when there's not corresponding item in the first.
 
There are 2 separate pivot tables that's connected to the both slicers if there is a selection in one slicer and no corresponding value in second one...then table that is connected to the slicer with selection shows data and the other one preferable show 0 value. Thanks
 
then table that is connected to the slicer with selection shows data and the other one preferable show 0 value.
Click to expand...

Not possible without adding data to source table. Hence my question...
 
You must log in or register to reply here.
Back
Top