TM1 Forum

Posted: **Wed Mar 02, 2016 4:15 pm**

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 ?

Posted: **Wed Mar 02, 2016 5:51 pm**

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.

Posted: **Wed Mar 02, 2016 5:58 pm**

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.

Posted: **Thu Apr 07, 2016 3:59 pm**

gtonkin wrote: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 ?

Posted: **Fri Apr 08, 2016 12:10 am**

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.

TM1 Forum

Subset: Sort by Element Name, Display Alias

Subset: Sort by Element Name, Display Alias

Re: Subset: Sort by Element Name, Display Alias

Re: Subset: Sort by Element Name, Display Alias

Re: Subset: Sort by Element Name, Display Alias

Re: Subset: Sort by Element Name, Display Alias