5 Replies Latest reply: Jun 13, 2013 12:07 AM by vishal.gaikwad RSS

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

    Martin Vierling

      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

        • Re: Expand a Pivot Table to a certain Level with a Macro?
          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