HierarchySortOrder problem

Post Reply
mnasra
Posts: 110
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

HierarchySortOrder problem

Post by mnasra » Wed Jan 13, 2021 10:02 pm

Hello Experts,

I am trying to use HierarchySortOrder for the first time (I have 11 years experience with Tm1, and so far did not even know it existed!!!)

Anyway, I put the following in the PROLOG

DIMNAME = 'COA';
MyHierarchy = 'Trial Balance';
HierarchySortOrder (DIMNAME, MYHierarchy , 'ByName', 'Descending','ByLevel', 'Ascending');

I get the message:
Execution aborted: DIMENSION "COA:Trial Balance" not Found

Have anyone made this Hierarchysortorder work?
does it do what I am hoping?

In my Chart of account, I have a TB hierarchy which I would like to be sorted by name (account)
While the other hierarchy - like Income Statement, I would not want to change the order.

Thanks
Thanks
Micheline

lotsaram
MVP
Posts: 3453
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: HierarchySortOrder problem

Post by lotsaram » Thu Jan 14, 2021 9:23 am

You are misunderstanding what the HierarchySortOrder function is for! This is a new function introduced with v11 and alternate hierarchies. It performs the identical function for an alternate hierarchy as DimensionSortOrder does for a dimension (well actually for the same named hierarchy) and you could actually just use the DimensionsortOrder function passing in DimName:HierName for the DimName argument.

What you are thinking of as a "hierarchy" is not a hierarchy but a rollup (C element). I suggest you read this article.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
paulsimon
MVP
Posts: 776
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: HierarchySortOrder problem

Post by paulsimon » Thu Jan 14, 2021 8:13 pm

Hi Micheline

I am not sure if Scott has some back ground info on your model.

However, if you do have just two different consolidations within a classic dimension then you are not going to be able to achieve what you want as the sort order you set with DimensionSortOrder is going to apply to all elements in the classic dimension which as Scott points out is really now called COA:COA where :COA is the default or classic hierarchy of the dimension COA.

In order to achieve what you want you are going to have to create a real Named Hierarchy called Trial Balance. I would strongly suggest that you avoid having a Dimension or Hierarchy named with spaces, as this then causes problems when used as a source in TI since TI cannot automatically generate variables. It is best to call it eg Trial_Balance_Hier.

You can then use HierarchySortOrder on this and have a different sort order on COA:Trial_Balance_Hier to that in the COA:COA.

Incidentally, I am not sure if the same bug exists whereby DimensionSortOrder did not work unless the dimension was empty. The standard work around for this was to put the entries directly into the }DimensionProperties cube. If you create a Named Hierarchy then, in this cube you will see the 'Dimensions' (really now Hierarchies):
  • COA
    COA:Trial_Balance_Hier
ie the Named Hierarchy appears as a dimension with the :Trial_Balance_Hier as the second part of its name. You will then be able to see the SORTELEMENTSENSE etc properties and can just type into them. They will take effect as soon as the dimension is next updated and saved.

Regards

Paul Simon

mnasra
Posts: 110
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: HierarchySortOrder problem

Post by mnasra » Tue Jan 19, 2021 5:47 pm

Thank you both Lotsaram and Paul.
YES, I am an old timer. Yes, I did not change my vocabulary. Thank you for the CUBEwise document. It depicts me to the T.

and yes, I 'overestimated' what this new functionnality was.
Thanks again for clearing this up
Micheline
Thanks
Micheline

Post Reply