Sort Table By Specified Column Selection

[#1]

I'm trying to sort a Straight Table by, depending on the user column selection.

If possible, I would like to do so via a macro, as the same user selection will be applied to several charts (easier to copy to the other charts).

The user can select 1 of 3 criteria, which is stored in a variable 'Order_By'.

Then I tried using the macro below:

     Sub Order_By

     If ActiveDocument.Variables("Order_By").GetContent.String = "SIR No" Then

         ActiveDocument.GetSheetObject("Table_All").SortBy 1

     Else

         If ActiveDocument.Variables("Order_By").GetContent.String = "Application" Then

             ActiveDocument.GetSheetObject("Table_All").SortBy 3

         Else

             If ActiveDocument.Variables("Order_By").GetContent.String = "Status" Then

                 ActiveDocument.GetSheetObject("Table_All").SortBy 33

             End If

         End If

     End If

     End Sub

This doesn't fully work.

Also it seems to mess up the 'Sort By Columns Priority', if I take a look at the chart properties.

How can achieve a simple sort by a specified column ?

Thanks

[#2 · Accepted solution]

I would suggest avoiding macros whenever possible. See if the attached file is kind of what you are looking for.

[b_garside]

Great Solution Rebecca!  Im going to try this as well.

[b_garside]

Rebecca, do you know how I would sort a Project list that is text based vs Numerical?

Does it need a function like MinString or just the Field value its self?

I have  aMonth field that wont sort unless I move it to the top of the Sort list. I used Num to ensure it was numerical when using Sum but that wont work when used my expression completely. Is kind of sorts but not until I promote it to the top.

Any ideas

[#5]

Ok, so I figured out one way that may help you.  I created a new field that uses Ord() to give each SalesRepName a numeric ranking and then used that field in the %Calc field.  Note that it will only be alphabetical through the third character of the text string unless you add to my formula.  Hope it helps!!

[#6]

Gysbert Wassenaar, swuehl, Michael Solomovich... do you guys have any better ideas?

[Gysbert_Wassenaar]

Nope, just a minor change. As far as I can tell it's possible to use rank(Sales Rep) to sort the Sales Rep field. The Ord field won't be necessary then. Or you could first load the sales reps in a temp table, then sort it and store it in a qvd and finally load the sales reps again from the qvd and join it with the rest of the data.

[b_garside]

Thank you both!  Very Clever. Sorting it in the Script really helped.

[#9]

Hello sir,

     i have also same problem of sorting. I am using pivot table and i want to sort 2nd column as per selection but it's working wiyh abve logic can u pls help me in this.

Thank u in advance.

[b_garside]

I would use the sample Gysbert provided. I essentially use the same sorting method by joining a pre-sorted QVD with my existing table. The formula used is also provided in a table. =$(=SortExp)  you specify the sort exp in a separate table and place the field in the Sort field.  Then when you select form lsit box is will select the proper expression to sort on.

Provide a screenshot or sample QVW for more assistance.