Expand a Pivot Table to a certain Level with a Macro?

[#1]

Hi All,

unfortunately i wasn't successful with finding an answer to this by searching in here and the documentation, so I need to ask you... Is there a known way to expand a pivot table to a certain level using a macro? For example a table with maybe 5 dimensions is fully expanded, but when the macro runs, it should be collapsed/changed so only the first 2 dimensions are expanded. The same of course when the table is fully collapsed... after the macro also the first 2 dimensions should be expanded.

Thanks a lot in advance,

Martin

[nirav_bhimani]

Hi,

I dont know about macro but following code will help you for dynamic pivot table based on level of selection in list box.

I have created 5 levels in pivot. This figure we can vary based on levels selection.

1. Write inline table in the load script.

Levels:
LOAD * INLINE [
Levels
Level1
Level2
Level3
Level4
Level5
];

// here you have to give name to each filed , database name of field or script.

Linkl:   
load * Inline [
GroupFieldname,GroupField
Brandcode,BCode
Item Category Code,ICode
Product Group Code,PCode
Outlet Code,OCode
Item No,INo
];

2. In the Setting->Document Setting->General->Alternate State->Add

Grp1 …………………  Grp5

3. In the Variable define:

              vlevel1 = only ( {Grp1} GroupField ) ……………… vlevel5 = only ( {Grp5} GroupField )

4. In the sheet Add Container

4.1     In the List box Set property for each level as Always one selected and

Title : Level1

Field : GroupField

Alternate State: Grp1…………………………….. Similarly 4 others as well.

4.2 Add listbox : Levels

5. Add Pivot Table  -> Go to Dimension  :

5.1 Add Calculated Dimension:

                 =$(vlevel1) …………………… =$(vlevel5)

5.2 Checked Enable Condition Checkbox

Match('Level1',$(vlevelvarchar))>0 …………………… Similarly 4 others as well.            

5.3 Checked Label

=only({Grp1}GroupFieldname) ……………….………… Similarly 4 others as well.

Hope this will help you.

Regads,

Nirav Bhimani

[#3]

Dear Nirav,

I am afraid this won't solve my problem but nevertheless it is an amazing piece of code, thank you, I am sure I will need it soon!!

Regards,

Martin

[#4]

Hi again,

no one ever had that problem?

Cheers,

Martin

[flipside]

You can use bookmarks with the Include Layout State option selected, so create a bookmark for the levels of expansion you need (without any selections) and apply the bookmark through actions via a button or similar.

flipside

[#6]

how i create bookmarks for the levels of expansion