Help to Reset Pivot Table

[MattAllington]

I have a pivot table connected to a power pivot data model. I have products on rows and the products are set up in a hierarchy in the data model. The net result is that I have expand/collapse buttons on rows (see image)

hierarchy.png (10.18 KiB) Viewed 9512 times

The problem I have got is how to reset the expand collapse buttons back to "All Collapsed". It is easy to get the top level to collapse, but when I expand it again, all the previous expanded levels return to the expanded state. This is not what I want. I want a way to reset the pivot table so that all levels are collapsed back to the fully collapsed level so the user can start drilling into the data again. I assume there is a way to reset this but I can't find it in Excel and I can't find it on the web.

Any ideas?

[PhilC]

Hi Matt,

The Rows have what is called the Active Field. Whichever item in the Rows you have selected will be the Active field. Collapse Field works on the active field. If you collapse each field from the bottom one up in turn, I believe you will get the behaviour you require. That is the manual way.

I suspect you would be able to have a macro that will collapse each one in turn up to the parent level. The code looks something like this - which looks like you can add a command for the different levels in your hierarchy. Perhaps someone will have a more elegant way of looping through the levels.

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[SMTLines].[SMT Layout].[SMTLevel1]").DrilledDown = False
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[SMTLines].[SMT Layout].[SMTLevel2]").DrilledDown = False
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[SMTLines].[SMT Layout].[SMTLevel3]").DrilledDown = False
   

Cheers
Phil

[ella.uther]

newbie here.

[RuskinF]

I know this is a redundant step, but try it:
Click the PivotChart.
On the Analyze tab, in the Data group, click Clear, and then click Clear All. The Clear All command resets your PivotTable but does not delete it. The data connection, placement of the PivotTable, and PivotTable cache remain the same.

I hope this helps!

[RadekZenek]

[quote="MattAllington"]I have a pivot table connected to a power pivot data model. I have products on rows and the products are set up in a hierarchy in the data model. The net result is that I have expand/collapse buttons on rows (see image)
hierarchy.png

The problem I have got is how to reset the expand collapse buttons back to "All Collapsed". It is easy to get the top level to collapse, but when I expand it again, all the previous expanded levels return to the expanded state. This is not what I want. I want a way to reset the pivot table so that all levels are collapsed back to the fully collapsed level so the user can start drilling into the data again. I assume there is a way to reset this but I can't find it in Excel and I can't find it on the web.

Any ideas?[/quote]

You have in the settings marked "open all"

[Jordakson]

I know this is a redundant step, but try it:
Click the PivotChart.
On the Analyze tab, in the Data group, click Clear, and then click Clear All. The Clear All command resets your PivotTable but does not delete it. The data connection, placement of the PivotTable, and PivotTable cache remain the same.

I hope this helps!

It really helped, I didn't expect it.