Hierarchy sorting issue

Post Reply
ravi
Posts: 138
Joined: Mon Apr 26, 2010 12:39 pm
OLAP Product: cognos
Version: tm1 9.5
Excel Version: 2007

Hierarchy sorting issue

Post by ravi »

Hi,

I need to create a Subset for the hierarchy sorted out names alphabetically-
When I use the sorting icon A-Z- it sorts only few elemements-Example

Parent A- have a elements B C D-this is fine.
but within the B hierarchy- elements does not get sorted alphabetically-

Please advise.
Regards,
Ravi
MSidat
Community Contributor
Posts: 110
Joined: Thu Aug 26, 2010 7:41 am
OLAP Product: TM1, PA
Version: PAL 2.0.8
Excel Version: 2016
Location: North West England

Re: Hierarchy sorting issue

Post by MSidat »

Hmm Interesting, i thought this would be possibly but having tried it, it appears not especially if you want the Hierarchy sort first as it will always structure it according to the original consolidation set up. ( I also tried clicking on the sorting icons a number of items, which sometimes works!! ;))

unless someone comes up with a MDX statement to achieve it a work around would be to either:

Write a quick TI to either:
1, Restructure the dimension so all children within each consolidation are firstly removed and then reinserted in alphetical order or
2, Create a shadow dimension which mimics the original dimension but is structured alphetically. If you do a search on the forums or google you should find some script that the TM1 Guru's on here have created to copy a dimension, this can then be tweaked to ensure you add any children in alphatical order.

Both options should relatively be a quick TI and can be set up as a Chore to ensure it is always correct.
Always Open to Opportunities
jstrygner
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Re: Hierarchy sorting issue

Post by jstrygner »

If I understand you correctly, running this via TI should do the trick:

Code: Select all

DimensionSortOrder(DimensionName, 'ByName', 'ASCENDING', 'ByHierarchy', 'ASCENDING');
It will apply automatic sorting of elements among this dimension (by hierarchy first and by name among hierarchy second).
csjean
Posts: 40
Joined: Mon Mar 01, 2010 2:53 pm
OLAP Product: TM1
Version: 9.5 9.5.1 9.5.2
Excel Version: 2007

Re: Hierarchy sorting issue

Post by csjean »

Hi Ravi,

This should work (although I haven't tested it).

Create an MDX subset with this:

Code: Select all

{
ORDER(
  {TM1SUBSETALL([<your dim here>])},
  [<your dim here>].Name, BDESC)
}
Hope this helps.
Cheers!

--
Claude-Sebastien Jean
Senior Consultant in Information Technology
Keyrus Canada
www.keyrus.ca
Post Reply