TM1DRILLDOWNMEMBER MDX with sort ASC?

Post Reply
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by BigG »

Hi, I have the below MDX but its pretty ugly. I am trying to acheive TM1DRILLDOWNMEMBER but with the Nth level sorted ASC. The hierachy sort returns an order that is not acceptable. Any suggetions...note the below the MDX does work

Code: Select all

{UNION(
{UNION(
{UNION(
{UNION(
{ [Employee].[Total Employee]},{ [Employee].[Existing Employee]})},
 {TM1SORT({FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "Y")}, ASC)})},
{ [Employee].[New Employee]})},
{TM1SORT({FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "N")}, ASC)})}
GG
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by tomok »

BigG wrote:The hierachy sort returns an order that is not acceptable. Any suggetions
Why not just change the sorting order of the source so that when you build the hierarchy it is sorted in the order you want?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
blackhawk
Community Contributor
Posts: 136
Joined: Thu May 29, 2008 2:29 pm

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by blackhawk »

Are you saying that the TM1Sort breaks the hierarchy and you are trying to preserve it or are you saying that what does get ordered is incorrect.

If you want to preserve the hierarchy you can use Hierarchize, such as:

Code: Select all

{UNION(
{UNION(
{UNION(
{UNION(
{ [Employee].[Total Employee]},{ [Employee].[Existing Employee]})},
{ HIERARCHIZE( {TM1SORT({FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "Y")}, ASC)})} )},
{ [Employee].[New Employee]})},
{ HIERARCHIZE( {TM1SORT({FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "N")}, ASC)})} ) }
However, I think that may revert to the natural (index) order which may be what you are referring to. If that is the case, you may have to use the ORDER MDX set operation instead of the TM1Sort.
ajain86
Community Contributor
Posts: 132
Joined: Thu Oct 15, 2009 7:45 pm
OLAP Product: TM1
Version: 9.4.1 9.5 9.5.1
Excel Version: 2003 2007

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by ajain86 »

The leaf level elements will always show in the hierarchy in the order they were added. You cannot have a subset be in hierarchy sort and show the nth level sorted. When you do a sort it does it against all the elements in the subset

This would be the TM1DRILLDOWNMEMBER Statement.

{TM1DRILLDOWNMEMBER( {[dimension name].[member name]},ALL,RECURSIVE ) }
Ankur Jain
blackhawk
Community Contributor
Posts: 136
Joined: Thu May 29, 2008 2:29 pm

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by blackhawk »

Well, I am not sure about sorting the Nth level, but you can certainly sort the leaf levels by something other than the natural index order.

You have to use the ORDER statement instead of the TM1Sort function, such as

Code: Select all

ORDER( FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "Y"), [Employee].[Name], ASC )
And if you really want to fine tune the order, just define an attribute called DisplayOrder or something and then use the MDX:

Code: Select all

ORDER( FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "Y"), [Employee].[DisplayOrder], ASC )
And then you can go to town defining however you want it to look. Existing before NonExisting, Friends before Enemies, whatever you like. :D
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by BigG »

Hi, thanks for replies.
Why not just change the sorting order of the source so that when you build the hierarchy it is sorted in the order you want?
seems like the DIMENSIONSORTORDER would sort byinput but as soon as I hierachizes it returns to the original way I loaded it (not ASC for description ALIAS -which is what the input order is), cant see why it should do this, maybe because the data was in this order prior (I do a DimensionDeleteAllElements evenand it still returns to original order in Heirachize). Might be because the sort I want is on an alias.Gave up on that one.
Are you saying that the TM1Sort breaks the hierarchy and you are trying to preserve it or are you saying that what does get ordered is incorrect.

If you want to preserve the hierarchy you can use Hierarchize
no, trying to get the Nth leaf elements in ASC order based on a Alias name, so TM1DRilldownMEMBER does not help either as it doesnt allow not in Nth leaf level sort

I tried

Code: Select all

{ORDER( {FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "Y")}, [Employee].[Description], ASC )}
but didnt seem to order by Description (the Alias). Where as below did sort

Code: Select all

{TM1SORT({FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "Y")}, ASC)})}
I think I may have to stick with the original UNION method....
GG
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by paulsimon »

Hi

I think you may have missed a part of blackhawk's suggestion

Assuming that you have access to the standard planning_sample cubes you can try the following on the plan_chart_of_accounts dimension. I have tried this and it does definitely sort alphabetically by the given Alias which in that dimension is AccountName, rather than in the original order by numeric account code.

Code: Select all

ORDER( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [plan_chart_of_accounts] )}, "Net Operating Income")}, ALL, RECURSIVE )}, [plan_chart_of_Accounts].[AccountName], ASC )
I suspect that the reason that it may not be working for you is that perhaps your filter on existing employee=Y breaks the hierarchy. You might want to try putting a hierarchize after the filter, or before to see if that helps.

Regards

Paul Simon
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by BigG »

Hi, thanks, have discovered ORDER doesnt work for an ALIAS attribute. Would need to create as 'Description' equivalent as a TEXT Attribute too for below to work. cheers for the responses ...

Code: Select all

{ORDER( {FILTER( {TM1SUBSETALL( [Employee] )},[Employee].[Existing] = "Y")}, [Employee].[Description_TEXT_not_ALIAS], ASC )}

GG
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by Elessar »

Thanks very much, everyone!

This topic helped me today, ORDER function was undeservedly forgotten by me :)
BTW, ORDER works with aliases in 10.2.2
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Bishop
Posts: 4
Joined: Wed May 24, 2017 8:58 pm
OLAP Product: TM1
Version: 10.2.2 looking at PA
Excel Version: 2016

Re: TM1DRILLDOWNMEMBER MDX with sort ASC?

Post by Bishop »

Ran into this same problem. While the Alias *shows* a value, it does not necessarily *contain* the value when the principal value and the alias value are identical.

See: http://www.tm1forum.com/viewtopic.php?t=2150#p48222
Post Reply