Subset: Sort by Element Name, Display Alias

[winshent]

I am trying to get my head around this (I'm still relatively new to TM1)..

My dimension is named GLT_Account, the subset is named All_Accounts. This subset contains the consolidation 'All_Accounts' and all of its N level elements.

For example
Premium Written Direct(10000)
Gross Premiums Written by 3rd Parties (10005)
Bad Debt Expense Premiums(10025)

I've built and MDX as below, which sorts the accounts by element name (10025 etc). However, I need to display the alias for users by default.

Code: Select all

{Union ({TM1FILTERBYPATTERN( {TM1SUBSETALL( [GLT_Account] )}, "All_Accounts")},
{TM1SORT( { EXCEPT( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [GLT_Account] )}, "All_Accounts")}, ALL, RECURSIVE )}, { [GLT_Account].[All_Accounts] }) }, ASC)})}

I've tried executing this after saving the MDX. However, the result is the accounts sorted by alias.

Code: Select all

SubsetAliasSet ( 'GLT_Account' , 'All_Accounts', 'Description' ) ;

Have i approached this completely the wrong way ?

[tomok]

Pretty sure you can't sort in a subset by element name and then display alias because the sort is going to be based on whatever is displayed in the subset, not the underlying element names. If they are the same then great, if not, no can do. AFAIK, the only place you can do what you want is in an active form by hiding the element column and using a DBRA in another column and displaying that instead.

[gtonkin]

Try this:

Code: Select all

UNION({[GLT_Account].[All_Accounts]},
ORDER({TM1DRILLDOWNMEMBER( {[GLT_Account].[All_Accounts]}, ALL, RECURSIVE )},[}ElementAttributes_GLT_Account].([GLT_Account].Currentmember),BASC))

It appeared from your previous MDX that you only have level 0 and 1 so did not include a filter on level.

p.s. Based on your SubsetAliasSet, your subset looks to be named the same as your C level/root element - probably not a good idea either.

[winshent]

Try this:

Code: Select all

UNION({[GLT_Account].[All_Accounts]},
ORDER({TM1DRILLDOWNMEMBER( {[GLT_Account].[All_Accounts]}, ALL, RECURSIVE )},[}ElementAttributes_GLT_Account].([GLT_Account].Currentmember),BASC))

It appeared from your previous MDX that you only have level 0 and 1 so did not include a filter on level.

p.s. Based on your SubsetAliasSet, your subset looks to be named the same as your C level/root element - probably not a good idea either.

Thanks gtonkin..

I've been on holiday, hence the late response..

I tried your MDX but it didn't appear to do any sorting of the elements... Did you record this or write the MDX ?

[art83]

If I'm understanding you correctly, you should be able to sort by an attribute. In the example below, "Default" is an alias which is just equal to the element's name. You can then sort by this attribute while still displaying the "Description" alias as you desire.

Code: Select all

{UNION({[GLT_Account].[All_Accounts]},
ORDER( {TM1FILTERBYLEVEL( {Descendants([All_Accounts])}, 0)}, [GLT_Account].[Default], BASC)
)}

If this doesn't work, I think a picture of your dimension structure would help augment your description in your original post.