错误"1004":无法设置PivotItem类的Visible属性 [英] Error '1004': Unable to set the Visible property of the PivotItem class

问题描述

我从这里获得了以下代码:遍历报告更改可见性的过滤器不起作用，该解决方案被标记为有效.根据我的需要修改后，就像这样:

i got the below code from here: Looping through report filters to change visibility doesn't work where the solution is marked as working. After modifying according to my need, it is like this:

With pt.PivotFields(6)
    .ClearAllFilters
     If .PivotItems.Count > 0 Then

        'goofy but necessary
        Set firstPi = .PivotItems(1)

        For Each pi In .PivotItems

            If firstPi.Visible = False Then
                firstPi.Visible = True
            End If

             'Don't loop through firstPi
            If pi.Value <> firstPi.Value Then
                itemValue = pt.GetPivotData("[Measures].[Nr of Cancelled]", "[Characteristics].[Reason]", pi.Name).Value

                rw = rw + 1
                nwSheet.Cells(rw, 1).Value = pi.Name
                nwSheet.Cells(rw, 2).Value = pi.Visible
                If itemValue < 2000 Then
                    If pi.Visible = True Then
                        pi.Visible = False 'Error here
                    End If
                Else
                    MsgBox pi.Value
                    If pi.Visible = False Then
                        pi.Visible = True 'Error here
                    End If
                End If
            End If
        Next

              'Finally perform the check on the first pivot item
              If firstPi > 2000 Then
                  firstPi.Visible = True
              Else
                  firstPi.Visible = False
              End If
           End If
    End With

我看到整个代码都能正常工作，而且我只遇到pi.Visible = True或pi.Visible = False

I see that the whole code is working fine and I'm facing error only the lines pi.Visible = True or pi.Visible = False

我不确定代码在哪里做错了.

I'm not sure where I've done wrong for the code not to work.

当我在互联网上搜索时，我遇到了以下链接: https: //support.microsoft.com/zh-cn/kb/114822 ，其中MS提到仅 数据透视表字段中的连续项目可以隐藏. 这是否意味着我表中的项目不连续?谁能帮我?我在这里迷路了.

When I was searching internet for the soltuion, I came across this link: https://support.microsoft.com/en-us/kb/114822 where MS mentioned that Only contiguous items in a PivotTable Field can be hidden. Does that mean that items in my table are not contiguous? Can anyone help me? I'm lost here.

推荐答案

我没有找到有关该错误的任何解决方案.但是我找到了另一种方法来完成任务.我使用数组存储了所有要隐藏的项目和可见的项目，以便可以调用HiddenItemsList或VisibleItemsList:

I didn't find any solution for the Error. But I found another way to achieve the task. I used array to store all the Items to hide and Items to be visible so that I can call either HiddenItemsList or VisibleItemsList:

    For Each pvtitem In pt.PivotFields(6).PivotItems
        On Error GoTo skipreason
        itemValue = pt.GetPivotData("[Measures].[Cancelled]", "[Characteristics].[Reason]", pvtitem.Name).Value
        If itemValue < 2000 Then
            hiddenReasons(hiddenCount) = pvtitem.Name
            hiddenCount = hiddenCount + 1
        Else
            visibleReasons(visibleCount) = pvtitem.Name
            visibleCount = visibleCount + 1
        End If

Sheets("Cancels").PivotTables("Cancels").PivotFields( _
    "[Characteristics].[Reason].[Reason]" _
    ).VisibleItemsList = Array(visibleReasons())

这篇关于错误"1004":无法设置PivotItem类的Visible属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！