Child Elements are not visible under consolidations

Post Reply
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Child Elements are not visible under consolidations

Post by vishalpoddar90 »

Dear All,

I am creating a dimension with C level elements as last 5 week, last 10 week and last 13 week through TI.
Now when I open the Dimension I am not getting all the elements under consolidations. but as soon as i click collapse and reexpand the same consolidated element then I get all the elements under it.

Kindly help me to resolve this issue.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Child Elements are not visible under consolidations

Post by Steve Rowe »

Hi this is not an issue but expected behaviour.

The no-named subset view of a dimension you get when you open the subset editor is the natural underlying order of the dimension, which by default is I think the order in which the elements were added.

You can fix the natural order of the dimension in the dimension properties cube or using a TI command (DimensionSortOrder)
Technical Director
www.infocat.co.uk
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 »

Steve Rowe wrote: Mon Oct 30, 2017 1:53 pm Hi this is not an issue but expected behaviour.

The no-named subset view of a dimension you get when you open the subset editor is the natural underlying order of the dimension, which by default is I think the order in which the elements were added.

You can fix the natural order of the dimension in the dimension properties cube or using a TI command (DimensionSortOrder)
I used DimensionSortOrder(DimensionName, 'ByInput', 'Ascending', 'ByHierarchy','Acending'); but it didnt nade any difference.
i have created a subset with mdx expression hierarchize but still getting the same results.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Child Elements are not visible under consolidations

Post by declanr »

What tab did you run the dimensionsortorder on?
Declan Rodger
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 »

declanr wrote: Mon Oct 30, 2017 2:02 pm What tab did you run the dimensionsortorder on?
I have tried with Prolog, metadata and epilog. Same result with all three tabs.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Child Elements are not visible under consolidations

Post by tm123 »

Subset editor shows elements only once, so if you have multiple hierarchies in the dimension, it will not expand them all.
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 »

tm123 wrote: Mon Oct 30, 2017 2:34 pm Subset editor shows elements only once, so if you have multiple hierarchies in the dimension, it will not expand them all.
Then how to show all elements. I want to automate it. I have created a dyanamic subset using mdx expression but still getting same results.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Child Elements are not visible under consolidations

Post by tm123 »

did you try to use { HIERARCHIZE( YourMDX ) } ?
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 »

tm123 wrote: Mon Oct 30, 2017 4:30 pm did you try to use { HIERARCHIZE( YourMDX ) } ?
Yes, i tried that also bt still no success.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Child Elements are not visible under consolidations

Post by tm123 »

What version of TM1 are you using and also can you please post a screenshot from your Subset Editor, with MDX Expression Windows being displayed
tomok
MVP
Posts: 2831
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: Child Elements are not visible under consolidations

Post by tomok »

What exactly are you expecting to see when you open the dimension? Are you expecting to see last 5 week, last 10 week and last 13 week, in order, all drilled down to their lowest level? If it is then just use this MDX in a subset:

Code: Select all

{TM1DRILLDOWNMEMBER( {[YourDimName].[last 5 week]}, ALL, RECURSIVE )}+
{TM1DRILLDOWNMEMBER( {[YourDimName].[last 10 week]}, ALL, RECURSIVE )}+
{TM1DRILLDOWNMEMBER( {[YourDimName].[last 13 week]}, ALL, RECURSIVE )}
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 »

tomok wrote: Mon Oct 30, 2017 6:52 pm What exactly are you expecting to see when you open the dimension? Are you expecting to see last 5 week, last 10 week and last 13 week, in order, all drilled down to their lowest level? If it is then just use this MDX in a subset:

Code: Select all

{TM1DRILLDOWNMEMBER( {[YourDimName].[last 5 week]}, ALL, RECURSIVE )}+
{TM1DRILLDOWNMEMBER( {[YourDimName].[last 10 week]}, ALL, RECURSIVE )}+
{TM1DRILLDOWNMEMBER( {[YourDimName].[last 13 week]}, ALL, RECURSIVE )}
Thanks a lot for expression but still I am getting same result.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Child Elements are not visible under consolidations

Post by Wim Gielis »

Can you supply screenshots please ?
Also, the .dim file for the relevant dimension.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
AmbPin
Regular Participant
Posts: 173
Joined: Sat Mar 20, 2010 3:03 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007-10

Re: Child Elements are not visible under consolidations

Post by AmbPin »

This MDX may return the elements you require:-
{
{TM1DRILLDOWNMEMBER( {[YourDimName].[last 5 week]}, ALL, RECURSIVE )}
, {TM1DRILLDOWNMEMBER( {[YourDimName].[last 10 week]}, ALL, RECURSIVE )}
, {TM1DRILLDOWNMEMBER( {[YourDimName].[last 13 week]}, ALL, RECURSIVE )}
}
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 »

Wim Gielis wrote: Tue Oct 31, 2017 7:46 am Can you supply screenshots please ?
Also, the .dim file for the relevant dimension.
As per my organization's restriction I am not allowed to post any files or screenshots.
Post Reply