none
Synchronizing two Slicers that are connected to two different pivot tables connected to two different OLAP cubes RRS feed

  • Question

  • I have done quite a bit of searching for a solution to synchronizing two slicers linked to two different OLAP sources, but I've only found solutions to syncing one OLAP slicer to one non-OLAP slicer.

    One solution to synchronize an OLAP based slicer and a non-OLAP based slicer was posted on the Experts-Exchange website, Titled - "Help-me-Sync-2-Slicers-with-Different-Data-Source"

    The code from the above site is below:

    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

    Dim ar() 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)

    ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))

    For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)

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

    ar(i) = svalue

    Next

    For Each si In scList.SlicerItems

     If UBound(Filter(ar, si.SourceName)) < 0 Then

      si.Selected = False

     End If

    Next

    End Sub

    My specific question is, if the variable "scList" were connected to an OLAP Slicer Cache instead of a non-Olap slicer cache, what modification would be needed to the code above?  I know that the property "SlicerItems" returns an error for Olap slicers, so this code will error out at:

    For Each si In scList.SlicerItems

    Thank you in advance for your help.

    Tuesday, August 2, 2016 2:12 PM

Answers

  • >>>I know that the property "SlicerItems" returns an error for Olap slicers

    According to your description, 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. For OLAP data sources, use the SlicerItems property of the SlicerCacheLevel object instead. So you could modify your codes like below:
    Set SL = scList.SlicerCacheLevels(1)
    For Each sI In SL.SlicerItems
      .......
    Next
    For more information, click here to refer about SlicerCache.SlicerItems Property (Excel)

    In addition you could refer to Accessing the Slicer through VBA

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    • Proposed as answer by David_JunFeng Thursday, August 11, 2016 1:54 PM
    • Marked as answer by David_JunFeng Tuesday, August 16, 2016 2:02 PM
    Thursday, August 4, 2016 6:54 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards

    Emi Zhang

    TechNet Community Support

    Please mark the reply as an answer if they help and unmark them if they provide no help.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.


    Wednesday, August 3, 2016 5:28 AM
  • >>>I know that the property "SlicerItems" returns an error for Olap slicers

    According to your description, 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. For OLAP data sources, use the SlicerItems property of the SlicerCacheLevel object instead. So you could modify your codes like below:
    Set SL = scList.SlicerCacheLevels(1)
    For Each sI In SL.SlicerItems
      .......
    Next
    For more information, click here to refer about SlicerCache.SlicerItems Property (Excel)

    In addition you could refer to Accessing the Slicer through VBA

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.
    • Proposed as answer by David_JunFeng Thursday, August 11, 2016 1:54 PM
    • Marked as answer by David_JunFeng Tuesday, August 16, 2016 2:02 PM
    Thursday, August 4, 2016 6:54 AM