Pivot Table Controls

[pover]

Is there a way to expand all and collapse all in a pivot table by using a macro. I've searched the API Guide, but I haven't found anything.

Has anybody tried to make 3 separate pivot or straight tables behave as if it were one table using macros?

Thanks,

Karl

[#2]

hey Karl!

give this a try:

sub toggle_expand()

 set chart = ActiveDocument.GetSheetObject("CH1324")
 set gp = chart.GetProperties

 gp.TableProperties.PivotAlwaysFullyExpanded = not gp.TableProperties.PivotAlwaysFullyExpanded
 chart.SetProperties gp

end sub

[Anonymous · Accepted solution]

This is what I used to collapse or expand all:

sub CollapsePivot
 on error resume next
 set chart = ActiveDocument.GetSheetObject("CH01")
 chart.CollapseLeft 0,0,true
end sub
sub ExpandPivot
 on error resume next
 for n = 0 to 5 ' number of dimensions was 5
 set chart = ActiveDocument.GetSheetObject("CH01")
 chart.ExpandLeft 0, n, true
 next
end sub

Didn't work in QV8.50. Should be OK in 9

[anitamelbye]

Hi,

I can not get this to work. Can someone please be helpfull and explain to me what I am doing wrong?

I would like to make an expand all button, and I have changed the numer of dimension to 7 (the number of dimensions I have), and I have changed the "CH01" to "CH02". Am I suppose to remove the ""? What else am I suppose to change?

I am sorry to say, I don't know any macro-programming, so I am so very thankful for all your help 🙂

*newbee in love* 🙂

[Anonymous]

Anita,
Your certainly should not remove "". The reason ofornot working could be QlikView version. These macros don't work in some versions, I can't tell now which ones, sorry.

[anitamelbye]

Thank you for responding Michael!
I'm on version 9.00.7320.7 SR2.

If Michael does not have an answer maybe no one else does either? 🙂
I really would like to get this "Expand All"-button to work.. Hmm..

*newbee in love*