Child Elements are not visible under consolidations

Post Reply
vishalpoddar90
Posts: 16
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Child Elements are not visible under consolidations

Post by vishalpoddar90 » Mon Oct 30, 2017 1:48 pm

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: 1963
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Child Elements are not visible under consolidations

Post by Steve Rowe » 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)

vishalpoddar90
Posts: 16
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 » Mon Oct 30, 2017 2:00 pm

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: 1628
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 » Mon Oct 30, 2017 2:02 pm

What tab did you run the dimensionsortorder on?
Declan Rodger

vishalpoddar90
Posts: 16
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 » Mon Oct 30, 2017 2:03 pm

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: 113
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 » 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.

vishalpoddar90
Posts: 16
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 » Mon Oct 30, 2017 4:20 pm

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: 113
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 » Mon Oct 30, 2017 4:30 pm

did you try to use { HIERARCHIZE( YourMDX ) } ?

vishalpoddar90
Posts: 16
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 » Mon Oct 30, 2017 5:29 pm

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: 113
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 » Mon Oct 30, 2017 6:26 pm

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: 2631
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 » 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 )}
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

vishalpoddar90
Posts: 16
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 » Tue Oct 31, 2017 6:27 am

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: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Child Elements are not visible under consolidations

Post by Wim Gielis » Tue Oct 31, 2017 7:46 am

Can you supply screenshots please ?
Also, the .dim file for the relevant dimension.
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: Set up a TM1 model with AutoHotKey as a service or application

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 » Tue Oct 31, 2017 8:50 am

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: 16
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.2
Excel Version: 2013

Re: Child Elements are not visible under consolidations

Post by vishalpoddar90 » Fri Nov 03, 2017 7:47 am

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