Sorting consolidations dynamically

Post Reply
kaazimraza
Posts: 86
Joined: Mon Jun 25, 2012 6:58 am
OLAP Product: TM1, SSAS, Power BI
Version: 10.2.2
Excel Version: 2016

Sorting consolidations dynamically

Post by kaazimraza » Mon Jan 21, 2019 6:04 am

Hi everyone,
I am trying to sort the following consolidations ;
Capex-projects-consol-sorting-issue.png
Capex-projects-consol-sorting-issue.png (3.01 KiB) Viewed 199 times
I want 'All Not Used' to be listed before 'All New Work', and I have tried variations of DimensionSortOrder function but I am not having any luck, certainly I am doing something incorrect.

What's the best combination of parameters to sort it in a way that that I get All Not Used between All Current Projects and All New Work?

Thanks
Kaz
Thanks,

Kaz

Wim Gielis
MVP
Posts: 2011
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: Sorting consolidations dynamically

Post by Wim Gielis » Mon Jan 21, 2019 8:22 am

In the Dimension Editorovw the elements in the correct order. Then set the order of the indexes by clicking the last icon of the menu bar.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Active form claustrophobia

tomok
MVP
Posts: 2538
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Sorting consolidations dynamically

Post by tomok » Mon Jan 21, 2019 1:52 pm

Wim Gielis wrote:
Mon Jan 21, 2019 8:22 am
In the Dimension Editorovw the elements in the correct order. Then set the order of the indexes by clicking the last icon of the menu bar.
This only works when you intend to maintain the dimension manually. If it is maintained by TI this will not work. Realistically, it's not practical unless these top two levels never change. Make the sort order on the dimension By Element. In your TI attach All Current Projects, All Not Used and All New York as children of All Plan Projects, in that order, in the Prolog. Build the rest of the hierarchy in the Metadata tab, taking care to skip any steps that would attempt to attach these specific children.

I have done this on several occasions and it will work but the structure has to be pretty static.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

kaazimraza
Posts: 86
Joined: Mon Jun 25, 2012 6:58 am
OLAP Product: TM1, SSAS, Power BI
Version: 10.2.2
Excel Version: 2016

Re: Sorting consolidations dynamically

Post by kaazimraza » Fri Feb 08, 2019 10:35 am

Thanks Wim and Tom.

@Wim, I tried setting order of the elements, but they get blown away as soon as the overnight process loads data and re-assign children to their respective parents.

@Tom, my data loading process has multiple processes. I am adding Current, New Work, and Not Used as children of All New Projects in the Prolog of the master process, but then I have another process to load Current jobs, another one to work New and Not Used where jobs are being assigned between New Work and Not Used.

So I guess, fair to say, it's not a very static hierarchy.

And I appreciate your quick response, and apologies for the delay in getting back.

thanks

Kaz
Thanks,

Kaz

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Sorting consolidations dynamically

Post by PavoGa » Fri Feb 08, 2019 1:17 pm

If you can use subsets to control display, use a numeric attribute to control the display order.

Otherwise, as Tom states, you have to set dimensionsortorder to ByInput and then carefully control how objects are added.
Ty
Cleveland, TN

Post Reply