How to collapse all dimensions?

[Anonymous]

Hi,

I have pivot chart that looks like this.  QV seems to remember the last "state" I was in. 

My question is how do I collapse all the dimensions back to the "FiscalYear" level (see 2nd screenshot)

every time I click on this sheet?  I don't want the "state: to be remembered.

Thank you.

[krishnacbe]

Hi,

Try with below option

Presentation Tab -> Uncheck Always Fully Expanded check box.

[t_moutault]

Hi,

try sheet properties -> Trigger

[Anonymous]

The "Always Fully Expanded" was already unchecked.

[Anonymous]

Thomas,

Adding a trigger to minimize the object simply minimizes my chart when I click on the sheet.  So this is not what I want.

I just wanted to collapse the dimensions.

[jyothish8807]

Hi Sydney,

Try the below macro. It will expand your pivot permanently. i am working on to make it more flexible.

run this macro on sheet open trigger.

Sub ExpandChart2

On Error Resume Next

  set vUN = ActiveDocument.Variables("vUserName")

  set vFilePath = ActiveDocument.Variables("vFilePath")

  Set vChartToExpand = ActiveDocument.Variables("vChartToExpand")

  Set chart = ActiveDocument.GetSheetObject(vChartToExpand.GetContent.String)

  Set gp = chart.GetProperties

  gp.TableProperties.PivotAlwaysFullyExpanded = TRUE

  chart.SetProperties gp

  set obj = ActiveDocument.GetSheetObject("CH01")

   End Sub

Regards

KC

[Anonymous]

Hi Jyothish,

Thanks for the code.  But I'm looking to collapse the dimensions (again, see 2nd screenshot) and not

to expand them.  Do you have code to do "collapsing"?  Thanks!

[rupamjyotidas]

Try this through AccessPoint every time you login

[Anonymous]

I found this post but it's a user driven action.

Expand all the dimensions - Pivot Table

Right click on dimension -> choose expand / collapse. 

Anyone knows the code/macro behind this event?

[Anonymous]

Does this only affect my account?  What about the users?