none
Multiple pivot (from different sources) slicers controlled by a single slicer: works with regular pivot, not with PowerPivot RRS feed

  • Question

  • Hi,

    I have a worksheet  with several tabs, each one containing a table of data. The tables are similar, only the data inside
    differ. I can't  consolidate the tables into one as they are too big (several millions of lines, even with powerpivot).

    I created a pivot and a slicer for each table (the pivot selections and the slicers are identical). All the pivots and their related slicers are in a single tab for convinience.

    I want to control all the pivots in one action, i.e. when selecting an item in a 'master' slicer, this selection applies to all the other (slave) slicers.

    I have done it using regular pivot with small tables, and it works. The code is as follow (with a French Windows, "Slicer"
    sometime is named "Segment"):

    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
        If Sh.Name = "name of the sheet where the 'master' slicer is" And Target.Name = "name of the 'master' pivot" Then
            Application.EnableEvents = False
            'next line to repeat for each slave slicer, adjusting the name
            ActiveWorkbook.SlicerCaches("Segment_Name of the slave slicer").ClearManualFilter
              
            For Each Item In ActiveWorkbook.SlicerCaches("Segment_Name of the master slicer").SlicerItems
              'next line to repeat  for each slave slicer, adjusting the name
              ActiveWorkbook.SlicerCaches("Segment_Name of the slave slicer").SlicerItems(Item.Name).Selected = Item.Selected
            Next
            Application.EnableEvents = True
        End If
    End Sub

    When I apply the same code with powerpivots, I got errors.

    First, for the command: For Each Item In ActiveWorkbook.SlicerCaches("Segment_Name of the master slicer").SlicerItems
    I got the execution error '1004', error defined by the application or the object

    Searching  in the web, I found a command that applies for powerpivot and changed the syntax, changing "SlicerItems" by
    "VisibleSlicerItems" as follow:
        For Each Item In ActiveWorkbook.SlicerCaches("Segment_Name of the master slicer").VisibleSlicerItemsList
    With this, this command line executed well (no error message), and I got a new error message for the next line:
     ActiveWorkbook.SlicerCaches("Segment_Name of the slave slicer").SlicerItems.Name(Item).Selected = Item.Selected
    Execution error '424', Object required.

    I guess that there is a syntax difference using powerpivot for Item.Selected, no ?

    I noticed also that with regular pivot, the total code applies only if the master slicer is used, affecting the slave. When I select an item in a slave slicer, the other slicers (master or other slaves) are not affected. 

    With powerpivot, the command begings to be executed in all the cases, i.e. if I click on the master slicer or on a slave slicer. I guess the syntax for the first line of the code to set the master slicer is also no the correct one.

    I will appreciate any suggestion and help on this.
    Thanks
    Monday, August 31, 2015 12:02 PM

Answers

  • Hi Arnaud,

    Base on your code, you have already get the value of VisibleSlicerItemsList, to change the selected item, you need to change the value of VisibleSlicerItemsList property.

    The Query1 is just a sample, you could add watch to the SlicerCache variable to check what actual value it has for current selected item.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 2, 2015 3:29 AM
    Moderator

All replies

  • Hi ArnaudNab,

    I think the issue is in the SlicerItems property.

    The SlicerItems property of the SlicerCache object is only applicable for slicers that are based on PivotTables based on workbook ranges or lists (SlicerCache.SourceType = xlDatabase), or for slicers that are based on PivotTables based on relational data sources (SlicerCache.SourceType = xlExternal and SlicerCache.OLAP = False). Attempting to access the SlicerItems property for slicers that are connected to an external OLAP data source (SlicerCache.OLAP = True) generates a run-time error

    Please try to change the VisibleSlicerItemsList property value directly, for example:

    sc.VisibleSlicerItemsList = "[Query1].[DimDate.CalendarYear].&[2005]"

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 1, 2015 6:28 AM
    Moderator
  • Hi Starain,

    Thanks for your suggestion.

    The data are powerpivot tables that are imported from access

     I'm too new in vba to follow you so not able to adapt your example to me specific case. Should I paste this command line directly, what shoul be Query1 ?

    THanks again.

    Arnaud

    Tuesday, September 1, 2015 7:16 AM
  • Hi Arnaud,

    Base on your code, you have already get the value of VisibleSlicerItemsList, to change the selected item, you need to change the value of VisibleSlicerItemsList property.

    The Query1 is just a sample, you could add watch to the SlicerCache variable to check what actual value it has for current selected item.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 2, 2015 3:29 AM
    Moderator
  • Hi Startain

    "Base on your code, you have already get the value of VisibleSlicerItemsList, to change the selected item, you need to change the value of VisibleSlicerItemsList property."

    This is indeed my problem. I have no syntax knowledge for this. Any suggestion ?

    Thanks

    Arnaud

    Wednesday, September 2, 2015 8:16 AM
  • Hi,

    This is your code:

    ActiveWorkbook.SlicerCaches("Segment_Name of the master slicer").VisibleSlicerItemsList

    https://msdn.microsoft.com/en-us/library/office/ff193916.aspx

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, September 3, 2015 1:52 AM
    Moderator
  • Hi Startain,

    This line of command was already working, with"Visible" before SlicerItemsList.

    This is for the following line that I ask your help:

    ActiveWorkbook.SlicerCaches("Segment_Name of Slave slicer").SlicerItems.Name(Item).Selected = Item.Selected

    I got the "execution error 424" Object required.

    I tried to add also "Visible" before :

    ActiveWorkbook.SlicerCaches("Segment_Name of Slave slicer").VisibleSlicerItems.Name(Item).Selected = Item.Selected

    but same error.

    Ther should be a specific syntax to design the Item as an object.

    I appreciate your help.

    Armaud

    Thursday, September 3, 2015 8:18 AM
  • Hi Armaud,

    What I mean is that you need to change the value of VisibleSlicerItemsList property directly.

    Base on the error message, I think you can’t use SlicerItems or VisibleSlicerItems property.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 4, 2015 2:33 AM
    Moderator
  • Hi Starain

    OK, as I don't know the syntax, I think I have to search something else.

    Thanks for your advise

    I close the case

    Arnaud

    Friday, September 4, 2015 2:05 PM
  • Hi,

    As I said that you need to change VisibleSlicerItemsList property.

    sc.VisibleSlicerItemsList = "XXX"

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 7, 2015 8:52 AM
    Moderator
  • THanks Starain,

    Should "XXX" be written like this (it doesn't work) or does it represent something ?

    Arnaud

    Monday, September 7, 2015 2:53 PM
  • Hi Arnaud,

    You need to base on your pivot table to get the actual value, as I said that you could select an item manually, then check the actual value through your code.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 8, 2015 1:38 AM
    Moderator