All PivotItems Listing as Hidden or Not Visible

[BenW71]

I am trying to go through the current pivot filters that are set up on a pivot table and store them so that they can be reapplied with a single button click. The code for all of this is in an add-in if that matters.

When i loop through the pivot fields and check the visible and not visible items, all are showing up as visible = false.

For example for the Project Type pivot field, below are the current selected values...

Debugging a bit shows that when i go through this "Project Type" field i show:

Public Sub TestGetPivotFieldItems()
Dim pf As PivotField, pi As PivotItem, pt As PivotTable


    Set pt = ActiveSheet.PivotTables(1)
    Set pf = pt.PivotFields("Project Type")
    Set pi = pf.PivotItems("EDF")
  
    Debug.Print (ActiveSheet.Name & " -> " & pf.Name & " -> " & pf.Name & " -> " & pi.Name & ".Visible = " & pi.Visible)
    Debug.Print (ActiveSheet.Name & " -> " & pf.Name & " has " & pf.PivotItems.Count & " total items")
    Debug.Print (ActiveSheet.Name & " -> " & pf.Name & " has " & pf.VisibleItems.Count & " visible item which is " & pf.VisibleItems(1).Name)
    Debug.Print (ActiveSheet.Name & " -> " & pf.Name & " has " & pf.HiddenItems.Count & " not visible items")
  
End Sub

Gives the result:
Full Portfolio -> Project Type -> Project Type -> EDF.Visible = False
Full Portfolio -> Project Type has 10 total items
Full Portfolio -> Project Type has 1 visible item which is (All)
Full Portfolio -> Project Type has 10 not visible items

However, in the screenshot you can see that EDF is checked, as are most of the others... I don't know why it doesn't show 2 as not hidden and the other 8 as visible. i can deal with (All) in the code.

 

[BenW71]

OH WEIRD... i just changed one of the pivot items to not be checked and re-ran it...

I made the second row (Business SUpport Project (BSR 's) unchecked and now i can see...

Full Portfolio -> Project Type -> Project Type -> EDF.Visible = True (NOTE: THIS IS NOW CORRECT WHEN I ACCESS THE VALUE DIRECTLY)
Full Portfolio -> Project Type has 10 total items (NOTE: CORRECT )
Full Portfolio -> Project Type has 1 visible item which is (All) (NOTE: - .visibleitems doesn't seem to be correct)
Full Portfolio -> Project Type has 10 not visible items (NOTE: - .hiddenitems doesn't seem to be correct)

I also have a function that goes through each and manually looks at the visible property for all of the pivot items in the pivot field... I ran this before updating the BSR field checkbox it and it showed everything as not visible... But once i updated / changed the setting for that line now it is showing properly...

All items of pivot field Project Type (10): Business, Business Support Project (BSR ‘s), Core Support Project, XYZ agile, XYZ Scrum, XYZ XP, Discovery, EDF, Out of Office, Production Support Project
Visible items of pivot field Project Type (7): Core Support Project, XYZ agile, XYZ Scrum, XYZ XP, Discovery, EDF, Production Support Project
NOT Visible items of pivot field Project Type (3): Business, Business Support Project (BSR ‘s), Out of Office

So my two issues are:
- I don't know why the value had to be changed to get the pivotitems to show true and false properly...
- I don't know why visibleitems and hiddenitems is not correct. This is lower priority as i can just do the loop through all pivotitems if necessary...

 

[BenW71]

Additional debugging findings... Fields that are not filtered (ie are showing ("All")) also show up with all of their pivotitems having the .visible property false...

My basic requirement i am trying to solve is that i want to be able to store all the selections, for example:
- Run through each pivot field in the pivot table that is a page field
- If it has any not visible items, i will store the visible items in a hidden workbook with pivot field name / pivot item name pairs

Later i will be able to reapply it by going through each of those pivot fields and setting the .visible property accordingly.

But the above problem is stopping me from getting the pivot fields for which pivot items have been filtered out...

 

[BenW71]

@Moderators (or anyone who could advise)... Apparently i did something dumb by replying to my own post here so this never really was in the "unanswered questions" section.

Is there any way I can delete this and repost it as a single post ? I don't want to break forum rules?

 

[RoryA]

What kind of pivot table is it? A normal one, or a Power Pivot / OLAP one?

 

[BenW71]

What kind of pivot table is it? A normal one, or a Power Pivot / OLAP one?

I'm sorry Rory, i don't actually know the difference. The data set is an immense database where they create an export (which is no longer in the spreadsheet as it is HUGE) and create these pivots for us...

Btw here's a really simple test that shows what's going on...
1) I open the spreadsheet and the pivot table has the below setting:

2) Simple debugging program:

Public Sub ProjectTypePivotTest()
Dim pf As PivotField, pi As PivotItem

    Set pf = ActiveSheet.PivotTables(1).PivotFields("Project Type")
    For Each pi In pf.PivotItems
        Debug.Print "'" & pi.Name & "' Visible:  " & pi.Visible
    Next pi
   
   
End Sub

Gives output with everything showing as False (when only the Business and Out of Office lines should be false):
'Business' Visible: False
'Business Support Project (BSR ‘s)' Visible: False
'Core Support Project' Visible: False
'XYZ agile' Visible: False
'XYZ Scrum' Visible: False
'XYZ XP' Visible: False
'Discovery' Visible: False
'EDF' Visible: False
'Out of Office' Visible: False
'Production Support Project' Visible: False



I then just change the second pivot item in the list to be UNCHECKED (the BSR line) and rerun that same macro and get the correct results..."
'Business' Visible: False
'Business Support Project (BSR ‘s)' Visible: False
'Core Support Project' Visible: True
'XYZ agile' Visible: True
'XYZ Scrum' Visible: True
'XYZ XP' Visible: True
'Discovery' Visible: True
'EDF' Visible: True
'Out of Office' Visible: False
'Production Support Project' Visible: True


Note:
- The code resides in an XLAM addin file.
- My overall goal with this is to just be able to store the pivot selections in a hidden spreadsheet in the XLAM so it can be reapplied later (for example when a new export workbook is made available)

 

[RoryA]

My best guess would be that the issue is caused by however the pivots were separated from the data. Can you refresh the pivot tables? Are there queries in the workbook?

 

[BenW71]

When i try to refresh the data (right clicking on the pivot filters area and selecting refresh) it gives "Reference is not valid". I believe the workbook is saved without the underlying queries or data.

 

[RoryA]

I wondered if that might be the case. Can you toggle the visible property in your code before any manual changes, or does it error? e.g.

With pt.PivotFields("Project Type").Pivotitems(1)
   .Visible = Not .Visible
   .Visible = Not .Visible
end with

 

[BenW71]

Funny, i had just changed the testing / debugging program that same way....

Public Sub ProjectTypePivotTest()
Dim pf As PivotField, pi As PivotItem

    Set pf = ActiveSheet.PivotTables(1).PivotFields("Project Type")
    For Each pi In pf.PivotItems
        Debug.Print "'" & pi.Name & "' Initial Visible:  " & pi.Visible
        pi.Visible = Not pi.Visible
        Debug.Print "'" & pi.Name & "' Changed Visible:  " & pi.Visible
        pi.Visible = Not pi.Visible
        Debug.Print "'" & pi.Name & "' Final Visible:  " & pi.Visible
    Next pi
 
End Sub

Results are underwhelming...
'Business' Initial Visible: False
'Business' Changed Visible: False
'Business' Final Visible: False
'Business Support Project (BSR ‘s)' Initial Visible: False
'Business Support Project (BSR ‘s)' Changed Visible: False
'Business Support Project (BSR ‘s)' Final Visible: False
... and so on for all of them.


EDIT: After running this actually now the project type filter itself looks like it's been actually changed to being unfiltered entirely... So toggling the value on and off unfiltered all values (it even is asking me now if i want to select multiple items like it's an unfiltered field)


BUT... after this if i MANUALLY change the filter back to the ORIGINAL SETTINGS (only Business and Out of Office are unchecked... then the results again are all correct in the initial, changed, and final visible all work properly, like:
'Business' Initial Visible: False
'Business' Changed Visible: True
'Business' Final Visible: False
'Business Support Project (BSR ‘s)' Initial Visible: True
'Business Support Project (BSR ‘s)' Changed Visible: False
'Business Support Project (BSR ‘s)' Final Visible: True
etc...