Page 1 of 1
Dynamic subset hierarchy sort ascendic hierarchy
Posted: Thu Apr 11, 2013 9:34 am
by 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?
Re: Dynamic subset hierarchy sort ascendic hierarchy
Posted: Thu Apr 11, 2013 12:18 pm
by tomok
maps wrote: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).
Re: Dynamic subset hierarchy sort ascendic hierarchy
Posted: Fri Apr 12, 2013 1:40 pm
by 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.
Re: Dynamic subset hierarchy sort ascendic hierarchy
Posted: Wed Jul 09, 2014 10:49 am
by 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? :/
Re: Dynamic subset hierarchy sort ascendic hierarchy
Posted: Thu Jul 10, 2014 2:22 am
by 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.
Re: Dynamic subset hierarchy sort ascendic hierarchy
Posted: Thu Jul 10, 2014 9:07 am
by 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)}
)