Page 1 of 1
TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Tue Mar 08, 2011 6:02 am
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)})}
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Tue Mar 08, 2011 1:30 pm
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?
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Tue Mar 08, 2011 3:55 pm
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.
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Tue Mar 08, 2011 4:17 pm
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 ) }
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Tue Mar 08, 2011 5:03 pm
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.
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Tue Mar 08, 2011 9:29 pm
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....
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Wed Mar 09, 2011 12:26 am
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
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Wed Mar 09, 2011 3:02 am
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 )}
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Tue Jul 25, 2017 2:07 pm
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
Re: TM1DRILLDOWNMEMBER MDX with sort ASC?
Posted: Thu Aug 24, 2017 3:31 pm
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