Sorting on an alphanumeric metric column

How does microstrategy sort data ?

This looks like a very easy question, however it is important to understand how sorting of data happens in the report.  With Version 9.x MSTR has changed the way they sort data. Earlier the sorting was platform dependent. Intelligence Server will do the sorting for a 4-tier environment (web) and the desktop analytical engine will do the sorting for 3-tier architecture. The sorting is dependent on the platform (OS) specific localization setting to determine the sorting rules to follow. However rules for the same localization on different platforms are different and this often affected the sort order for MicroStrategy Reports. To mitigate this problem, starting from 9.x MSTR uses International Components for Unicode libraries for sorting algorithm. This library is standard across all platforms. 

Alphanumeric sorting 

This is based on "natural sorting" algorithm. You need not be surprised when you see A100 coming before A20.  Micorstrategy identifies the type of data returned from the warehouse, and and applies similar sorting logic. There might be lot of instanced when your metric consists of both Alphabets and numbers. How do you sort it ? And then you sort on such metric, the results will look like below.

Why is 10 coming ahead of 2. This is not users want to  see. Users dont care what damn algorithm you use for sorting. All they want  to see is data sorted in a humanly sensible way, and that is numer 10 coming after number 5. If it is a programming language you could write your own code. Will you be able to do a code change for BI internals ? Or do you have the time to request for a new algorithm and wait for it? Usually you get few hours to respond for a client request like this?

There are multiple ways you can resolve this problem. You could alter the table structure and insert a new numeric column, and sort on that. It is nearly impossible to apply this solution in a productin environment to solve a problem like this.

The easy way is to create a new fact, with your database specific function to convert the column into integer. I am using microsoft access DB and below is the new fact expression that I created.

ApplySimple("cint (#0)", [TEST_SORT])

This database specific function would take care of converting the column values into numeric type and return only numberic data.  Sorting on the numeric data type is what the user expected to see. You can create a new metric based on this fact and sort using that. You will now see the report results sorted in a way the user wanted. 

You can apply formatting and adjust the column width if you do not want to display the new metric in the report. Will look like something below.

Do you know of any solution where you can add the new metric in the objects list and sort based on that. I am using 9.2.1m and when I applied advanced sort based on this new metric and removed it from the grid, the sorting was reset. It there is a way you can sort on objects not in the grid, that could help .