Macro to show/hide selected dimensions subtotals in the pivot table (list box)

Anonymous — Tue, 14 Jul 2015 08:09:06 GMT

Hello,

I want to write a macro that would show/hide subtotals for dimensions, that are selected from list box. I found a macro to show/hide subtotals for the table, that has regular dimensions (where you can't select which dimensions to show):

'***********************************************************************************************************************************************

' Show Subtotals

'***********************************************************************************************************************************************

sub PivotShowSubtotals

set c = ActiveDocument.GetSheetObject("CH01")

set cp = c.GetProperties

set dims = cp.Dimensions

'set gp = chart.GetProperties

'set tp = gp.TableProperties

countexpr = cp.expressions.count 'count expressions

countdimexpanded = c.getcolumncount - countexpr 'count expanded dimensions

'msgbox(c.getcolumncount - countexpr)

for i = 0 to countdimexpanded -1

j = i+1

Answer = MsgBox("Show Subtotals: Column "& j,3) '3 = yes, no, cancel

If Answer=6 Then 'yes

dims(i).ShowPartialSums = true

c.SetProperties cp

'''' show total from dimension limit tab

c.SetProperties cp

elseif Answer=7 Then 'no

dims(i).ShowPartialSums = false

c.SetProperties cp

else

exit for '(Answer=2) 'cancel

End If

end sub

'***********************************************************************************************************************************************

' Hide Subtotals

'***********************************************************************************************************************************************

sub PivotHideSubtotals

set c = ActiveDocument.GetSheetObject("CH01")

set cp = c.GetProperties

set dims = cp.Dimensions

for i = 0 to dims.Count -1

dims(i).ShowPartialSums = false

c.SetProperties cp

end sub

But I need a macro, which would show/hide subtotals for particular dimensions, selected from listbox.

Please, help me

Re: Macro to show/hide selected dimensions subtotals in the pivot table (list box)

marcus_sommer — Tue, 14 Jul 2015 14:56:59 GMT

I have built such show/hide sub-totals with macros and wouldn't recommend to use such approach then it's not necessary - see: Macros are Bad - you could gain the same if you doubled each of your dimensions within the chart - one with sub-totals and one without - and use visibility-conditions on these dimensions maybe per if(substringcount(getfieldselections(DimensionsWithSubTotals), 'Dimension1') >= 1, 1, 0).

There are various examples available here in the community how you could show and hide dimensionss and expressions, maybe this one: Hiding Dimensions in Pivot Table.

- Marcus

topic Macro to show/hide selected dimensions subtotals in the pivot table (list box) in QlikView

Macro to show/hide selected dimensions subtotals in the pivot table (list box)

Re: Macro to show/hide selected dimensions subtotals in the pivot table (list box)