Show all items except one with pivot table in VBA

[JonathanA]

Hi there,

I hope someone might be able to help me with this, I have a macro that updates a table from a SQL Server view, refreshes a pivot table and moves the axis' along for a gantt chart according to the date. My problem is I need to pivot table to show all items except where Value of Free =0 but I get an error when running this code
"Runtime-Error 1004 Unable to set the visible property of the PivotItem class"

stops at "Pi.Visible = True"

Sub Macro1()
Application.DisplayAlerts = False
Dim mydate As Date
Dim AxisStart As Date
Dim AxisEnd As Date
Dim pt As PivotTable
Dim Pi As PivotItem

mydate = Date + 28
AxisStart = Date - 140
AxisEnd = Date + 112

    Sheets("Data").Select
    Range("A1").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Pivot").Select
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    
    ActiveSheet.PivotTables("PivotTable7").PivotFields("No_").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable7").PivotFields("No_").PivotFilters.Add _
        Type:=xlValueIsLessThan, DataField:=ActiveSheet.PivotTables("PivotTable7"). _
        PivotFields("Max of Order By"), Value1:=mydate
        Set pt = ActiveSheet.PivotTables("PivotTable7")
        For Each Pi In pt.PivotFields("Free").PivotItems
        Pi.Visible = True
        Next Pi
        
        
        With ActiveSheet.PivotTables("PivotTable7").PivotFields("Free")
        .PivotItems("0").Visible = False
    End With
    
        
     Sheets("Chart2").Select
   ' ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = AxisStart
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = AxisEnd
    
    ActiveChart.Axes(xlValue).MinimumScale = AxisStart
    ActiveChart.Axes(xlValue).MaximumScale = AxisEnd
      Application.DisplayAlerts = True
    
End Sub

 

[Fazza]

What about putting that criteria in the query table's SQL?

Then the dataset does not contain the unwanted data.

Something like

SELECT *
FROM table
WHERE Free <> 0

 

[JonathanA]

Unfortunately I need to be able to show or hide based on that field.

 

[Fazza]

Looping through pivot items can be done. I haven't looked at that code yet. Short of time, sorry. Also, I don't use Excel 2007 or 2010. There will be other similar posts. I'm guessing you have a lot of data. Try search for word manualupdate

Another way would be add another field within the SQL and make it a page field. Then it can be toggled using the page field.

Such as

SELECT *, IIF(Free=0,'YES','NO') AS [Zero]
FROM table

Then, select either 'show all' to include the zeros or 'NO' to exclude on the new Zero pagefield.

 

[JonathanA]

The old page filter in 2003 has changed to the report filter in 2007 that I am using, I think I might have to use 2 datasets to solve this, but I'll search as you suggest, thanks.