Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi again,
no one ever had that problem?
Cheers,
Martin
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
how i create bookmarks for the levels of expansion