如果CubeField.Orientation = xlPageField，如何设置PivotField.HiddenItemsList属性的值 [英] how to set PivotField.HiddenItemsList property's value if CubeField.Orientation = xlPageField

问题描述

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

The task is to automate OLAP pivot table data filtering. There are some items in pivot field named sPivotFieldName I need to exclude. The code below works pretty fine.

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

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

But the problem appears when I'm trying to change cube field ".Orientation" property's value to xlPageField. Run-time error 1004 fires each time. Here's an example:

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

原因似乎是，放置在pagefield中的字段项在例如放置在rowfield中时是可见的(人们可以将它们视为行标题).也许还有别的东西.我想念什么?

The reason seems to be that items of the fields placed in pagefield aren's visible as they are when placed for example in the rowfield (one can see them as row captions). Or maybe there's something else. What am I missing?

推荐答案

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

This functionality obviously isn't available for PageFields. Seems to me a workaround is to use the .VisibleITemsList approach instead, but make sure it doesn't include the items you want to exclude.

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

To do this, you need to dump all the unfiltered items to a variant, loop the variant looking for the term you want to hide, and if you find it, just replace that element for some other element that you don't want to hide. (This saves you having to create a new array without that item in it).

棘手的事情是获取所有未过滤项目的列表:如果数据透视表未应用某种过滤器，.VisibleItemsList不会将其提供给您.因此，我们需要通过制作数据透视表的副本，将感兴趣的PageField设置为RowField，删除所有其他字段，然后将所有项目都悬停起来来进行偷偷摸摸的工作，因此我们知道在删除那些内容后应该看到什么应该被隐藏.

The tricky thing is to get a list of all unfiltered items: .VisibleItemsList won't give it to you if the PivotTable doesn't have some kind of filter applied. So we need to get sneaky by making a copy of the PivotTable, making the PageField of interest a RowField, removing all other fields, and then hoovering up the complete list of items, so we know what should be visible after we remove the ones that should be hidden.

无论您是要处理RowField还是PageField，无论您是想使用.VisibleItemsList来设置过滤器，还是使用.HiddenItemsList

Here's a function that handles filtering no matter whether you're dealing with a RowField or a PageField and no matter whether you want to use the .VisibleItemsList to set the filter, or the .HiddenItemsList

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

In your particular case, you would call it like so: 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

这篇关于如果CubeField.Orientation = xlPageField，如何设置PivotField.HiddenItemsList属性的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！