Union + Sort in MDX

[Karthik1710]

I have a requirement where each element of my MDX subset is supposed to appear twice to be used on an Active Form(Basically duplicate all elements).

Easiest way I could think of was to do an UNION ALL with the same MDX query. So I wrote the following MDX:

Code: Select all

UNION(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Transaction ID] )}, 0)},
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Transaction ID] )}, 0)},ALL
)

Works well. But the problem is Result isn't sorted.

For ex. IF MDX result is A,B,C then the above query returns: A,B,C,A,B,C whereas I want it to be A,A,B,B,C,C.

Didn't see any SORT feature for UNION in Microsoft website. Tried TM1SORT but got a Syntax error. Any suggestions?

[ardi]

Try this:

Code: Select all

{TM1SORT( UNION(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Transaction ID] )}, 0)},
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Transaction ID] )}, 0)},ALL
) , ASC)}

[Karthik1710]

Try this:

Code: Select all

{TM1SORT( UNION(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Transaction ID] )}, 0)},
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Transaction ID] )}, 0)},ALL
) , ASC)}

Worked. Thanks a lot