Skip navigation.
spilling the beans

TM1Sort, TM1SortByIndex and Order

developers
| |

TM1Sort is the equivalent of pressing one of the two Sort Ascending or Sort Descending buttons in the subset editor – i.e. sort alphabetically.

TM1SortIndex is the equivalent of pressing one of the two Sort by index, ascending or Sort by index, descending buttons in the subset editor – i.e. sort by the dimension index (dimix).

Order is a standard MDX function that uses a data value from a cube to perform the sort. For example, sort the list of customers according to the sales, or a list of employees according to their length of service.

Sort the whole Product dimension in alphabetically ascending order.
{TM1SORT( {TM1SUBSETALL( [Product] )}, ASC)}

Or, more usefully, just the leaf members:
{TM1SORT( TM1FILTERBYLEVEL({TM1SUBSETALL( [Product] )},0), ASC)}

Sort the leaf members according to their dimix:
{TM1SORTBYINDEX( TM1FILTERBYLEVEL({TM1SUBSETALL( [Product] )},0), ASC)}

Sort the leaf members of the dimension according to their Amount values in the Test cube from highest downwards.
{
ORDER(
    { TM1FILTERBYLEVEL(
        {TM1SUBSETALL( [Product] )}
        ,0)}
, [Test].([Posting Measures].[Amount]), BDESC)
}

Note that using BDESC instead of DESC gives radically different results. This is because BDESC treats all the members across the set used (in this case the whole dimension) as being equal siblings and ranks them accordingly, while DESC treats the members as still being in their “family groups” and ranks them only against their own “direct” siblings. If you’re not sure what this means and can’t see the difference when you try it out, then just use BDESC!

Order can also use an attribute instead of a cube value. In this example the AlternateSort attribute of Product is used to sort the children of Demand Loan in descending order. It is a numeric attribute containing integers (i.e. 1, 2, 3, 4, etc) to allow a completely dynamic sort order to be defined:
{ ORDER( {[Demand Loan].Children}, [Product].[AlternateSort], DESC) }


Average rating
(1 vote)