如何在SSAS数据透视表列中仅排除两项并选中其余所有项？

尝试过滤SSAS多维数据集数据透视表中的某一列，仅排除该列中的两个指定值、选中其余所有值，但代码执行后却排除了大量其他值，仅选中少数项。相关VBA代码如下：

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

编辑说明：项列表极长，无法直接在VBA中指定要选中的项，最初尝试列出所有可见项时出现运行时错误（语句过长），因此采用排除列表构建包含列表。
编辑补充：调试发现pi.Name的格式为"[MasterData Publisher Groups].[Publisher Group].&[30118]"，而非Excel中显示的实际名称，这是否正常？是否是代码失效的原因？

问题排查与解决 #

关于pi.Name格式的疑问 #

这个格式是正常的。SSAS多维数据集透视表的PivotItem.Name属性，存储的是包含维度层级、成员键值的唯一标识字符串，而非Excel界面上显示的友好名称，这不是代码失效的原因。

代码失效的核心原因 #

问题出在VisibleItemsList属性的限制上：对于SSAS透视表，VisibleItemsList对传入的数组有项数或字符数上限（通常约1000项或总字符数超阈值）。当你要设置的可见项数量过多时，Excel无法处理完整的数组，导致只有部分项被设置为可见，最终出现“仅选中少数项”的问题。

修复方法：反向操作（隐藏排除项，而非设置大量可见项） #

对于仅需排除少量项的场景，更可靠的方式是：先清空筛选，设置所有项可见，再手动隐藏需要排除的项。这种方式不会触发VisibleItemsList的限制，且效率更高。

修改后的代码如下：

Sub FilterSSASPivotExcludeItems()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim i As Long
    Dim excludeList As Variant
    
    ' 指定目标透视表和字段
    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("[MasterData Publisher Groups].[Publisher Group].[Publisher Group]")
    
    ' 刷新透视表确保数据最新
    pt.RefreshTable
    ' 清除现有筛选
    pf.ClearAllFilters
    
    ' 定义需要排除的项（确保和pi.Name格式完全一致）
    excludeList = Array("[MasterData Publisher Groups].[Publisher Group].&[30118]", _
                        "[MasterData Publisher Groups].[Publisher Group].&[27123]")
    
    ' 遍历所有透视项：先默认显示所有项，再隐藏排除列表中的项
    For Each pi In pf.PivotItems
        pi.Visible = True
        ' 检查当前项是否在排除列表中
        For i = LBound(excludeList) To UBound(excludeList)
            If pi.Name = excludeList(i) Then
                pi.Visible = False
                Exit For ' 找到匹配项后跳出内层循环
            End If
        Next i
    Next pi
End Sub

注意事项 #

• 确保excludeList中的字符串和pi.Name完全一致，包括括号、符号等，建议直接从调试时的pi.Name值复制，避免拼写错误。
• 如果透视表项数量极大，遍历过程可能需要几秒时间，但这是目前处理SSAS透视表大量项筛选的最可靠方式。

内容的提问来源于stack exchange，提问作者ZelelB