CODEWITHSUNDEEP
excelvbassasssas-tabular
ZelelB
ZelelB

Reputation: 2010

How to exclude two items from an SSAS Pivot table column and select all the rest?

I am trying to filter a column in an SSAS cube pivot table. I want to exclude only two identified values from that column, and select all the rest values.

Somehow my code is excluding many other values and selecting only few other ones.

Can someone help, and tell me what I am doing wrong? Here is the code:

    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[MasterData Publisher Groups].[Publisher Group].[Publisher Group]"). _
        ClearAllFilters
    Range("E8").Select
    
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim includeItems As Collection
    Dim item As Variant
    Dim i As Long
    Dim excludeList As Variant
    Dim visibleItemsArray() As Variant
    Dim itemCount As Long

    ' Set your PivotTable and PivotField
    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("[MasterData Publisher Groups].[Publisher Group].[Publisher Group]")

    ' Refresh the PivotTable to ensure it has the latest data
    pt.RefreshTable

    ' Define the items to exclude
    excludeList = Array("[MasterData Publisher Groups].[Publisher Group].&[30118]", _
                        "[MasterData Publisher Groups].[Publisher Group].&[27123]")

    ' Initialize the collection to store items to include
    Set includeItems = New Collection

    ' Loop through all items in the pivot field
    For Each pi In pf.PivotItems
        ' Check if the item is in the exclude list
        Dim shouldExclude As Boolean
        shouldExclude = False
        
        For i = LBound(excludeList) To UBound(excludeList)
            If pi.Name = excludeList(i) Then
                shouldExclude = True
                Exit For
            End If
        Next i

        ' Add the item to includeItems if it should not be excluded
        If Not shouldExclude Then
            includeItems.Add pi.Name
        End If
    Next pi

    ' Convert the collection to an array
    itemCount = includeItems.count
    ReDim visibleItemsArray(1 To itemCount)
    For i = 1 To itemCount
        visibleItemsArray(i) = includeItems(i)
    Next i

    ' Clear existing filters
    pf.ClearAllFilters

    ' Set the VisibleItemsList property
    pf.visibleItemsList = visibleItemsArray

EDIT: The list of items is VERY long, that's why I can't specify which items to select in a vba statement. In the beginning when I was trying to put on the list all the items visible, I was getting a runtime error that the statement is too long. Thus, I am going this way to build the exclude and includeLists.

EDIT2: when debugging, when I hover over pi.Name in the loop LBound / RBound loop above, the value is in the form of "[MasterData Publisher Groups].[Publisher Group].&[30118]" and not the real output name I see in Excel. It's normal? Maybe that's why the code is not working as it should?

Upvotes: 0

Views: 68

Answers (1)

TheRizza
TheRizza

Reputation: 2062

Your EDIT2 response is MDX format, and also used in Excel CUBE formulas. Keep debugging, but here are links to good resources on MDX and CUBE formulas that may help:

Upvotes: 0

Related Questions