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
HierarchySortOrder problem
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
HierarchySortOrder problem
Thanks
Micheline
Micheline
-
- MVP
- Posts: 3651
- 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
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.
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.
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: HierarchySortOrder problem
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):
Regards
Paul Simon
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
Regards
Paul Simon
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Re: HierarchySortOrder problem
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
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
Micheline