Sorting hierarchy by an attribute

[missspeedy23]

Before I created a hierarchy in my dimension the MDX to sort by attribute worked perfectly. I have since created a structure in the dimension and the sort is now being applied to each area of the hierarchy individually. What I need to be able to do is show only the leaf level elements and then sort those by the attribute (so in effect what it did before the hierarchy was created). Is there a way to sort N level elements irresepctive of their parent?

Please see attached document demonstrating the issue.

[Wim Gielis]

Your screenshots do not match the explanation/code.
If you filter by level 0, how can you have consolidated elements in the subset ?
Otherwise your screenshots are misleading and it will not be easy to help you.

[missspeedy23]

Your screenshots do not match the explanation/code.
If you filter by level 0, how can you have consolidated elements in the subset ?
Otherwise your screenshots are misleading and it will not be easy to help you.

I just showed the hierarchy screenshot just to show how it is sorting within each hierarchy rather than ignoring them.

So you can see in the first example "Advisor" has a sort order of 99 and "Admin Support" has a sort order of 0, so in the first screenshot which is the order the MDX is generating, "Advisor" should be at the bottom as 99 is the highest number and "Admin Support" should be at the top. Within the "Other parent" it is sorting in the sort order attribute correctly but it is doing it within each parent, not on the leafs as a whole.

[gtonkin]

Try BASC at the end of your ORDER function instead of ASC.

[missspeedy23]

Try BASC at the end of your ORDER function instead of ASC.

Perfect! Thanks very much.