none
Excel Slicer from 1 Olap cube (same source) 2 pivottables different grain filter RRS feed

  • Question

  • Good day,

    i have an olap cube (QMS) with 3 dimensions (Date, Item, Test). Date dimension = Year & Month

    2 Pivottables refreshing 2 charts, pivot 2 is a YTD and i need the year to be selected only

    pivot1 filters on DateYear or DateMonth, pivot2 only filters on DateYear (YTD Chart)

    When i create slicer i cant setup slicer DateMonth to skip pivot2

    Pivot1 = Slicer (DateYear, DateMonth, Item, Test)

    Pivot2 = Slicer (DateYear, Item, Test)

    Please any help or worst case scenario how to setup slicer indiviudal for pivot

    Regards

    Thursday, October 4, 2018 10:51 AM

All replies

  • Hi lsmailc1,

    This forum(Excel for Developers) is for development issues related to Excel and your issue is related to Excel Object Model.

    I will move your thread to Excel IT Pro Discussions.

    Thanks for understanding.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Friday, October 5, 2018 2:12 AM
  • Hi Ismailc1,

    Each Slicer object represents a slicer in a workbook. Slicers are used to filter data in PivotTable reports or OLAP data sources.

    Use the Add method to add a Slicer object to the Slicers collection. To access the SlicerItem object that represents the currently selected button in a slicer, use the ActiveItem property of the Slicer object.

    Please refer to the link as below:

    Slicer Object (Excel)

    The two pivot tables got data from the same source

    >>how to setup slicer indiviudal for pivot

    Please see the following link:

    Use a slicer to filter data

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, October 5, 2018 2:27 AM
  • Hi, 

    Just checking in to see if the information of Lina was helpful. Please let us know if you would like further assistance.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, October 5, 2018 9:14 AM
    Moderator
  • Hi,

    I got going using vbscript, my issue now is that my reports are designed for single selections example 1 month only

    when user click slicer clear filter my scipt crashes, i would like the slicer to clear but not refresh pivot or graphs.

    it should only act when slicer has one selection

    this is the clear filter slicer but i dont know how to test

    If ActiveWorkbook.SlicerCaches("Slicer_Plant").ClearManualFilter Then
    MsgBox ActiveWorkbook.SlicerCaches("Slicer_Plant").Slicers.Count

    this is my code to check more than one selection works but not on clear slicer filter and still refreshes pivots 

        Dim slcPlantList As Variant, currentItem3$, i3%
        slcPlantList = ActiveWorkbook.SlicerCaches("Slicer_Plant").VisibleSlicerItemsList
        currentItem3 = slcPlantList(1)
        If UBound(slcPlantList) > 1 Then
            ReDim slcPlantList(1)
            slcPlantList(1) = currentItem3
            MsgBox "Select Only One Plant", vbInformation
         '        Application.EnableEvents = True
            ActiveWorkbook.SlicerCaches("Slicer_Plant").VisibleSlicerItemsList = slcPlantList
        End If
    Please help, i just want it firstly to avoid it from crashing when clear slicer filter is selected?

    Sunday, October 7, 2018 3:29 PM
  • Hi,

    Sorry if you want to use code to get the result, please post to Excel for Developer forum:

    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


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Tuesday, October 23, 2018 2:57 AM
    Moderator