Dynamic subset hierarchy sort ascendic hierarchy

[maps]

Hi All,

I have to create a subset which shows my product dimension hierarchy in a alphabetic order.

The product dimenions looks basically like this:

total
-c
--ad
--cf
--bd
-a
--ed
--af


My subset has to reflect the hierarchy but in a alphabetical order in each level. Like this....

total
-a
--af
--ed
-c
--ad
--bd
--cf


Any ideas?

[tomok]

Any ideas?

Can't do it. If you want the hierarchy to be sorted like that in a subset then the dimension itself will already need to be sorted like that to begin with. Just change the sort order properties in the }DimensionProperties cube and rebuild the dimension (or rebuild with an XDI sheet).

[me2]

What seems to work for me is attaching the following MDX expression to the subset:

{ORDER({TM1SUBSETALL( [Products] )}, MemberToStr([Products].CurrentMember), ASC)}

This may not work in all circumstances but is perhaps worth a try.

[moritz]

Sorry to open this old Thread,

the mdx-expression is working, but somehow it sorts the elemnts from this:

total
-c
--ad
--cf
--bd
-a
--ed
--af

to this

a
-af
-ed
c
-ad
-bd
-cf
total

Any ideas? :/

[jrizk]

There is a way achieve the sort using ELPAR in this example - but it may not work well where the parent index is different for the children that sit under a particular hierarchy.

In this case the parent index is the same (ie 1) for all the children - so the sort can be done in 3 steps.

1. Create attribute for the dimension

- call it sortName for example

2. Create a rule on the }ElementAttributes cube for the dimension

- the rule will be:

['sorName'] = S:
IF (DTYPE('dimName', !dimName) @='N',
ELPAR('dimName', !dimName, 1) | '-' | !dimName ,
!dimName);

this will concatenate the parent name with the element name - thereby giving a alphabetical (or alpha-numeric) list that can be sorted.

3. Create an MDX that sorts using the attribute with a drilldown on the consolidation member (in this case total)

- the expression will be

{ ORDER( {TM1DRILLDOWNMEMBER( {[dimName].[total]}, ALL, RECURSIVE )} , [dimName].[sortName], ASC) }


You can try/tweak this approach on other hierarchies and see how you go.

[moritz]

Hi jrizk,

thank you for your approach.

I somehow solved it myself with the following MDX-Expression.

Regards

Code: Select all

UNION(
  {[<Dimension>].[total]},
  {
    ORDER(
      {
         {TM1DRILLDOWNMEMBER( {TM1SubsetBasis()}, ALL, RECURSIVE )}
      }
      , MemberToStr([<Dimension>].CurrentMember), ASC)}
)