Связывание слайсеров из сводных таблиц, построенных с использованием модели данных

Question

У меня есть пара сводных таблиц, которые используют разные кэши. Все эти таблицы создаются посредством подключений к данным, и все они используют модель данных. Что я пытаюсь создать мастер слайсер, который фильтрует все таблицы одновременно.

Используя VBA, я думаю заполнить массив всех видимых сводных элементов из pivotcache1 (кеш, с которым связан мой главный слайсер), а затем перебрать этот массив для фильтрации других моих кешей (pivotcache2 & pivotcache3).

Однако приведенный ниже код не работает - он ломается в цикле For Each.

Sub FilterMonth()

    Dim sc1, sc2, sc3 As SlicerCache
    Dim si As SlicerItem

    Set sc1 = ActiveWorkbook.SlicerCaches("Slicer_ClickMonth")
    Set sc2 = ActiveWorkbook.SlicerCaches("Slicer_Visit_Month1")
    Set sc3 = ActiveWorkbook.SlicerCaches("Slicer_Visit_Month")

    For Each si In sc1.SlicerItems
        If si.Selected Then
            'Add to my array
        End If
    Next 

End Sub

Я считаю, что это потому, что там синтаксис отличается для кэшей, построенных на моделях данных.

Кто-нибудь может подтвердить это и / или предоставить мне код для фильтрации всех моих кешей данных?

Answer 1

Согласно информации, которую я нашел здесь https://msdn.microsoft.com/EN-US/library/office/ff839561.aspx, свойство SlicerItems не будет работать напрямую с сводной таблицей, подключенной к модели данных (источник данных OLAP), Вместо этого мне нужно использовать SlicerItems с объектом SlicerCacheLevels.

Ниже приведен рабочий пример.

Sub SlicerMonth()

    Dim sc1, sc2, sc3 As SlicerCache
    Dim si As SlicerItem
    Dim sl1 As SlicerCacheLevel
    Dim mnth, nokey, nomatch As String

    Set sc1 = ActiveWorkbook.SlicerCaches("Slicer_ClickMonth")
    Set sc2 = ActiveWorkbook.SlicerCaches("Slicer_Visit_Month1")
    Set sc3 = ActiveWorkbook.SlicerCaches("Slicer_Visit_Month")

    Set sl1 = sc1.SlicerCacheLevels(1)

    'Clears the strings
    nokey = ""
    nomatch = ""

    'Cycles through the Master Slicer and populates the strings
    For Each si In sl1.SlicerItems
        If si.Selected Then
            mnth = Right(si.Name, 7) '7 becaucse I always have 3 letter months
            nokey = nokey & ",[No Key].[Visit Month]" & mnth
            nomatch = nomatch & ",[No Match].[Visit Month]" & mnth
            i = i + 1
        End If
    Next

    'Trims off the first comma for the strings
    nokey = Right(nokey, Len(nokey) - 1)
    nomatch = Right(nomatch, Len(nomatch) - 1)

    'Clears the filter in the slicers
    sc2.ClearManualFilter
    sc3.ClearManualFilter

    'Sets the slicers to match the Master slicer
    sc2.VisibleSlicerItemsList = Array(Split(nokey, ","))
    sc3.VisibleSlicerItemsList = Array(Split(nomatch, ","))


End Sub

Единственное замечание (которое заняло у меня около полдня, чтобы выяснить это) - убедиться, что после запятой нет пробелов при создании строк (nomatch & nokey).