excel - 如果 CubeField.Orientation = xlPageField，如何设置 PivotField.HiddenItemsList 属性的值

Question

任务是自动化 OLAP 数据透视表数据过滤。我需要排除名为 sPivotFieldName 的数据透视字段中的一些项目。下面的代码工作得很好。

With Worksheets(sWorksheetName).PivotTables(sPivotTableName)
    With .CubeFields(sCubeFieldName)
        .Orientation = xlRowField
        .IncludeNewItemsInFilter = True
    End With
    .PivotFields(sPivotFieldName).HiddenItemsList = vSomeItemsToExclude
End With

但是当我尝试将多维数据集字段“.Orientation”属性的值更改为 xlPageField 时出现问题。每次都会触发运行时错误 1004。这是一个例子:

With Worksheets(sWorksheetName).PivotTables(sPivotTableName)
    With .CubeFields(sCubeFieldName)
        .Orientation = xlPageField
        .IncludeNewItemsInFilter = True
    End With
    .PivotFields(sPivotFieldName).HiddenItemsList = vSomeItemsToExclude
End With

原因似乎是放置在 pagefield 中的字段项目是可见的，例如当它们放置在 rowfield 中时(可以将它们视为行标题)。或者也许还有别的东西。我错过了什么？

Answer 1

此功能显然不适用于 PageFields。在我看来，一种解决方法是改用 .VisibleITemsList 方法，但要确保它不包含您要排除的项目。

为此，您需要将所有未过滤的项目转储到变体，循环变体以查找要隐藏的术语，如果找到，只需将该元素替换为您不想隐藏的其他元素. (这使您不必创建一个没有该项目的新数组)。

棘手的事情是获取所有未过滤项目的列表:如果数据透视表没有应用某种过滤器，.VisibleItemsList 不会给你它。因此，我们需要通过制作 PivotTable 的副本，将感兴趣的 PageField 设置为 RowField，删除所有其他字段，然后将完整的项目列表吸起来，以便我们知道在删除那些应该可见的应该隐藏。

这是一个处理过滤的函数，无论您是处理 RowField 还是 PageField，也无论您是想使用 .VisibleItemsList 还是 .HiddenItemsList 来设置过滤器

在您的特定情况下，您可以这样称呼它:
FilterOLAP SomePivotField、vSomeItemsToExclude、False

Function FilterOLAP(pf As PivotField, vList As Variant, Optional bVisible As Boolean = True)

    Dim vAll        As Variant
    Dim dic          As Object
    Dim sItem       As String
    Dim i           As Long
    Dim wsTemp      As Worksheet
    Dim ptTemp      As PivotTable
    Dim pfTemp      As PivotField
    Dim sPrefix     As String

    Set dic = CreateObject("Scripting.Dictionary")

    With pf
        If .Orientation = xlPageField Then
        pf.CubeField.EnableMultiplePageItems = True

            If Not pf.CubeField.EnableMultiplePageItems Then pf.CubeField.EnableMultiplePageItems = True
        End If

        If bVisible Then
            If .CubeField.IncludeNewItemsInFilter Then .CubeField.IncludeNewItemsInFilter = False
            .VisibleItemsList = vList
        Else

            If .Orientation = xlPageField Then
                ' Can't use pf.HiddenItemsList on PageFields
                ' We'll need to manipulate a copy of the PT to get a complete list of visible fields
                Set wsTemp = ActiveWorkbook.Worksheets.Add
                pf.Parent.TableRange2.Copy wsTemp.Range("A1")
                Set ptTemp = wsTemp.Range("A1").PivotTable

                With ptTemp
                    .ColumnGrand = False
                    .RowGrand = False
                    .ManualUpdate = True
                    For Each pfTemp In .VisibleFields
                        With pfTemp
                            If .Name <> pf.Name And .Name <> "Values" And .CubeField.Orientation <> xlDataField Then .CubeField.Orientation = xlHidden
                        End With
                    Next pfTemp
                    .ManualUpdate = False
                End With
                sPrefix = Left(pf.Name, InStrRev(pf.Name, ".")) & "&["
                Set pfTemp = ptTemp.PivotFields(pf.Name)
                pfTemp.CubeField.Orientation = xlRowField
                pfTemp.ClearAllFilters

                vAll = Application.Transpose(pfTemp.DataRange)
                For i = 1 To UBound(vAll)
                    vAll(i) = sPrefix & vAll(i) & "]"
                    dic.Add vAll(i), i
                Next i

                'Find an item that we know is visible
                For i = 1 To UBound(vList)
                    If Not dic.exists(vList(i)) Then
                        sItem = vList(i)
                        Exit For
                    End If
                Next i

                'Change any items that should be hidden to sItem
                For i = 1 To UBound(vList)
                    If dic.exists(vList(i)) Then
                        vAll(dic.Item(vList(i))) = sItem
                    End If
                Next i

                .VisibleItemsList = vAll

                Application.DisplayAlerts = False
                wsTemp.Delete
                Application.DisplayAlerts = True

            Else
                If Not .CubeField.IncludeNewItemsInFilter Then .CubeField.IncludeNewItemsInFilter = True
                .HiddenItemsList = vList
            End If
        End If

    End With


End Function