Page 1 of 1

HierarchySortOrder problem

Posted: Wed Jan 13, 2021 10:02 pm
by mnasra
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

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.


Re: HierarchySortOrder problem

Posted: Thu Jan 14, 2021 9:23 am
by lotsaram
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.

Re: HierarchySortOrder problem

Posted: Thu Jan 14, 2021 8:13 pm
by paulsimon
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
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.


Paul Simon

Re: HierarchySortOrder problem

Posted: Tue Jan 19, 2021 5:47 pm
by mnasra
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