MDX Sort by Level

Post Reply
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

MDX Sort by Level

Post by jim wood »

Guys,

Do you know of a way to sort a dimension subset by level? I've only found sort by a-z or index.

Thanks,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: MDX Sort by Level

Post by declanr »

Jim,

If the levels required are in a hierarchy you could use the Hierarchize() function of MDX, if not I think you may have to create via a TI with use of the ellev function but I avoid MDX if it isn't absolutely necessary anyway due to a couple of traumatic experiences with my precious RAM... not so much an issue nowadays but that sort of thing sticks with you :(
Declan Rodger
rozef
Posts: 74
Joined: Thu Jun 17, 2010 10:35 am
OLAP Product: TM1
Version: 9.4 9.5.1
Excel Version: 2003 - 2007

Re: MDX Sort by Level

Post by rozef »

A simple:

{TM1FILTERBYLEVEL( {[MyDimension].[MySubset]} ,0, 1, 2, ... x)}

should make it or I miss something?
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: MDX Sort by Level

Post by declanr »

A simple:

{TM1FILTERBYLEVEL( {[MyDimension].[MySubset]} ,0, 1, 2, ... x)}

should make it or I miss something?

TM1FilterbyLevel doesn't SORT the elements based on their level it only removes those of unselected levels... but then again neither does the hierarchize function I mentioned :oops:

I may be missing a point but I don't think that sortbylevel would be used enough for it to be included in the MDX expressions?



EDIT - I am assuming that by sortbylevel you mean:

All Level 0 Elements
All Level 1 Elements
All Level 2 Elements

etc
Declan Rodger
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: MDX Sort by Level

Post by jim wood »

The only other option I could think of is to us filter by level to create a subset for each level and append. The second part is what I'm a bit hazy on as I'm not sure it's possible,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: MDX Sort by Level

Post by jim wood »

declanr wrote: I am assuming that by sortbylevel you mean:

All Level 0 Elements
All Level 1 Elements
All Level 2 Elements

etc
You are correct. That's exactly what I'm trying to do.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: MDX Sort by Level

Post by declanr »

Not entirely MDX and a bit messy but how about:

An Alias for all elements which you populate by a TI

The TI would cycle all elements and if level 0 would call them A00001, A00002, A00003.... if level 1 B00001, B00002, B00003... etc

Then for the mdx subset - swap to the alias and sort alphabetically and then swap back to the principle name?
Declan Rodger
rozef
Posts: 74
Joined: Thu Jun 17, 2010 10:35 am
OLAP Product: TM1
Version: 9.4 9.5.1
Excel Version: 2003 - 2007

Re: MDX Sort by Level

Post by rozef »

Apologies, my solution works in descending order.

You could also make some union:

{ UNION(
{ UNION(
{ TM1FILTERBYLEVEL( {[MyDimension].[MySubset]}, 0 ) }
,
{ TM1FILTERBYLEVEL( {[MyDimension].[MySubset]}, 1 ) }
) }
,
{ TM1FILTERBYLEVEL( {[MyDimension].[MySubset]}, 2 ) }
...
) }

This will works altough its a not very pretty solution.
I will prefer to make a calculate attribute which give your element levels, then make an ORDER on it (but I didn't test it).

Cheers,

-- EDIT --

Burned by Declanr :)
rozef
Posts: 74
Joined: Thu Jun 17, 2010 10:35 am
OLAP Product: TM1
Version: 9.4 9.5.1
Excel Version: 2003 - 2007

Re: MDX Sort by Level

Post by rozef »

Finally,

{ ORDER ( {[MyDimpension].[MySubset]} ,
[MyDimpension].CurrentMember.Level.Ordinal, BDESC ) }

will do it.
Unless your on a very old TM1 version (before 9.0 according to Philip Bichard and Martin Findon MDX documentation).

-- EDIT --
By the way, if you use this solution, be aware that Level.Ordinal function return the "index" level, starting by 0 at top level, then increasing on childs. So ordinal level and TM1 level are not the same. Besides, where brother elements can have different levels on TM1, they will have always the same ordinal level.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: MDX Sort by Level

Post by jim wood »

Guys,

Thanks for your responses. Defo gives me something to chew on. I'm going to try Declan's solution first to see how that fits,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Post Reply