TM1 Forum

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.

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

A simple:

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

should make it or I miss something?

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

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.

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.

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?

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

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.

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.