MDX Sort by Level
- 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
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.
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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
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
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
-
- 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
A simple:
{TM1FILTERBYLEVEL( {[MyDimension].[MySubset]} ,0, 1, 2, ... x)}
should make it or I miss something?
{TM1FILTERBYLEVEL( {[MyDimension].[MySubset]} ,0, 1, 2, ... x)}
should make it or I miss something?
-
- 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
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
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
- 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
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.
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- 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
You are correct. That's exactly what I'm trying to do.declanr wrote: I am assuming that by sortbylevel you mean:
All Level 0 Elements
All Level 1 Elements
All Level 2 Elements
etc
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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
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?
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
-
- 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
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
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
-
- 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
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.
{ 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.
- 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
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.
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7