Dynamic sort on dynamic dimensions

[adamdavi3s]

Dear All,

Creating a 'diy' reporting section which allows users to select dimensions and expressions in variables, these variables are then used in a bar chart (for example), nothing new or fancy here.

I want to give the user the ability to sort the x-axis by expression value or dimension A-Z or Dimension 1-9 WITHOUT the use of macros.

Is this possible within the expression sort option? I can't seem to find a definitive answer on this.

I stress again and clearly: WITHOUT THE USE OF MACROS

[sunny_talwar · Accepted solution]

See if this takes you any closer to what you are looking for.

Sort expression:

=Pick(Match(vSort, 'By exp', 'By A-Z', 'By 1-9'), -sum($(v_exp)), Ord($(v_dim)), Ord($(v_dim)))

[adamdavi3s]

Excellent thank you, wasn't aware of the ORD function

[sunny_talwar]

Ord is the opposite of Chr(). But the issue is that it only give the value for first letter. So abc = 97 and acb = 97 (anything starting with a is 97.

So you can further improve on this expression by summing the Ord value for all the letters. The above was just to give you a start.

[adamdavi3s]

Its great thank you, just what I needed to get started... I figure I can also add some tests to automatically determine if the field is numeric etc thanks!