1 Reply Latest reply: Mar 31, 2017 5:19 PM by Fer Fer RSS

    Macro to set column order

    Trey Smithq

      Hey Guys,

       

      I have a customizable straight table that the users create by choosing dimensions and measures.They then drag and drop the columns to the order they prefer and then bookmark specific reports.

       

      I am using dynamic expressions to hide show the columns. There are over 700 measures so this has to be done. When they drag the order for the columns it then shifts the order of the column into a random order. Is there a way to set columns back into the correct order with a macro?

       

      For example:

       

      Dimension:

      D01: $(=SubField(Concat('['&%Dimension&']',',',FieldIndex('%Dimension',%Dimension)),',',1))

       

      Expressions:

      E01: =$(=SubField(Concat(%Expression,'|',FieldIndex('%Expression',%Expression)),'|',1))

      E02: =$(=SubField(Concat(%Expression,'|',FieldIndex('%Expression',%Expression)),'|',2))

       

      It would be awesome if a macro could pick up the SubField() third parameter and move that column to that position. First for the dimensions then for the expression columns.

       

      So user creates report with order of:

           E02 | D01  E01

       

      Clicks button for macro and the resulting table is:

           D01 | E01 | E02

       

      Would be cool if it worked for both pivot table and straight, but if it only works for straight that is perfectly okay too.

       

      I believe it is possible but I am not familiar with VBscript and have had no luck writing a script for this.