CODEWITHSUNDEEP
excelvbaolap-cubeslicers
Taissa
Taissa

Reputation: 11

How do I select a slicer item that is dependent on the selected value of another slicer in VBA? (Working with OLAP cubes)

Alright, VBA masters! Newbie here again. Working with OLAP Cube data.

This time I am stuck on trying to automatically select a slicer item (from a different slicer group), based on a previously selected slicer item from another group.

To give it context, I am trying to select the same person under 2 different slicers. To make things more challenging, the slicers use different descriptors for people - that is "Smith, Bob" is used in one slicer and "123C - Smith, Bob" is used in the other slicer.

Here is my code:

Dim wb As Workbook
Dim slItem As SlicerItem
Dim slItem2 As SlicerItem
Dim sc3 As SlicerCache
Dim sc3L As SlicerCacheLevel
Dim sc4 As SlicerCache
Dim sc4L As SlicerCacheLevel

Set wb = ActiveWorkbook
Set sc3 = wb.SlicerCaches("Slicer_Primary_Account_List_Combo__BI")
Set sc4 = wb.SlicerCaches("Slicer_TM_Hierarchy")
Set sc3L = sc3.SlicerCacheLevels(1)
Set sc4L = sc4.SlicerCacheLevels(3)

sc3L.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData

' Select the first person within the Sales Cube slicer
' This selects each slicer item in the Sales Cube and iterates through them
For Each slItem In sc3L.SlicerItems
    If slItem.HasData Then   ''' This ensures the iteration is only on items with data
        sc3.ClearManualFilter
        sc3.VisibleSlicerItemsList = Array(slItem.Name)
    End If
Next

' Now ensure the same person is also selected within the BM Cube slicer
For Each slItem2 In sc4L.SlicerItems
    sc4.ClearManualFilter ''' CODE WORKS UP TO HERE
    If slItem2.Value = Mid(slItem.Value, 8, 30) Then   ''' I am trying to force the selection on the second slicer by looking for the name match. But this is NOT working. BREAKPOINT.
        slItem2.Selected = True
    End If
Next

I am getting a run time error 1004 at the Breakpoint. Saying it's an application-defined or object-defined error.

I've been trying to fix this piece of code for too long - and need your expertise!

The ultimate goal is: I only need 1 person selected at each iteration. And I need the same person selected on both slicers.

And GO!

Upvotes: 1

Views: 1308

Answers (1)

Damian
Damian

Reputation: 5174

Ok, let's try this:

Option Explicit
Sub Main()

    Dim wb As Workbook
    Dim slItem As SlicerItem
    Dim sc3 As SlicerCache
    Dim sc3L As SlicerCacheLevel

    Set wb = ActiveWorkbook 'ThisWorkbook is better if this code is run on the same workbook
    Set sc3 = wb.SlicerCaches("Slicer_Primary_Account_List_Combo__BI")

    sc3L.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData

    ' Select the first person within the Sales Cube slicer
    ' This selects each slicer item in the Sales Cube and iterates through them
    For Each slItem In sc3L.SlicerItems
        If slItem.HasData Then   ''' This ensures the iteration is only on items with data
            sc3.ClearManualFilter
            sc3.VisibleSlicerItemsList = Array(slItem.Name)
            Testing CStr(slItem.Name)
        End If
    Next
End Sub
Sub Testing(SalesName As String)

    Dim slItem2 As SlicerItem
    Dim sc4 As SlicerCache
    Dim sc4L As SlicerCacheLevel
    Dim wb As Workbook
    Dim SalesSplit As Variant

    'with this you are getting an array with the number and name being SalesSplit(1) the name
    'So taking that SalesName =  "123C - Smith, Bob" then SalesSplit(0) will be "123C" and SalesSplit(1) will be "Smith, Bob"
    SalesSplit = Split(SalesName, " - ")


    Set wb = ThisWorkbook 'if the workbook is the same containing the code
    Set sc4 = wb.SlicerCaches("Slicer_TM_Hierarchy")
    Set sc3L = sc3.SlicerCacheLevels(1)
    Set sc4L = sc4.SlicerCacheLevels(3)

    For Each slItem2 In sc4L.SlicerItems
        sc4.ClearManualFilter ''' CODE WORKS UP TO HERE
        If slItem2.Name = SalesSplit(1) Then   ''' I am trying to force the selection on the second slicer by looking for the name match. But this is NOT working. BREAKPOINT.
            slItem2.Selected = True
        End If
    Next

    'Continue your code, when this sub ends it will loop through the next item on your sales cube

End Sub

2 subs, the first one "main" will call the second one "Testing".

Step 1 Select a item with data on sales cube.

Step 2 Call the testing procedure passing the selected name from sales cube. Then you select the same item on the second cube and perform your tasks. Once done, back to step 1

Upvotes: 1

Related Questions