Ancestor function Versus More than 1 parent

Post Reply
fcapell
Posts: 11
Joined: Wed Jun 13, 2012 6:46 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2007
Location: São Paulo, Brazil

Ancestor function Versus More than 1 parent

Post by fcapell »

Hi everyone,

I'm a newbie working with Report Studio (Cognos 10.1.1) reading from a TM1 data source. I'm dealing with a Time dimension which has hierarchies like this:

- 2012-01-01_YTD
|-- 2012-01-01
- 2012-01-02_YTD
|-- 2012-01-01
|-- 2012-01-02
[...]
- Total 2012 per week
|-- 2012_week_0
|-- 2012-01-01
|-- 2012-01-02
[...]

The report has a Date Prompt. I have to select the 'total week' of that date chosen by the user. So I created a data item with an expression to return this element. I have tried these expressions (I have hardcoded the dates for testing purposes):

parent([Daily Sales].[Period Days].[Period Days]->:[TM].[Period Days].[Period Days].[@MEMBER].[2012-01-01])
ancestors([Daily Sales].[Period Days].[Period Days]->:[TM].[Period Days].[Period Days].[@MEMBER].[2012-01-01], 1)

Both return the element [2012-01-01_YTD].

How can I control which parent will be returned?

Thank you!
User avatar
Michel Zijlema
Site Admin
Posts: 712
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Ancestor function Versus More than 1 parent

Post by Michel Zijlema »

Hi,

In your example the ancestor function will pick up the first parent of the first hierarchy encountered.
If you have multiple hierarchies you could combine the parent, ancestor (or ancestors) function in combination with the descendants function to pick the parent within the required context.

Michel
fcapell
Posts: 11
Joined: Wed Jun 13, 2012 6:46 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2007
Location: São Paulo, Brazil

Re: Ancestor function Versus More than 1 parent

Post by fcapell »

Thank you for your reply, Michel.
fcapell wrote:If you have multiple hierarchies you could combine the parent, ancestor (or ancestors) function in combination with the descendants function to pick the parent within the required context.
I'm not sure if I got you, but I think you're suggesting that I kinda intersect 2 sets: one with the ancestors of 2012-01-01; and another set with the descendants of 'Total 2012 per week'. Assuming that, in this case it won't work because neither ancestor or parent functions will return the '2012_week_0' (which would be the common element in both sets).

Best regards,

Fausto
fcapell
Posts: 11
Joined: Wed Jun 13, 2012 6:46 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2007
Location: São Paulo, Brazil

Re: Ancestor function Versus More than 1 parent

Post by fcapell »

The funny thing is: I can bring the '2012_week_0' using TM1 subset screen. Here are the steps:

[1] Click on View menu > Expression Window;
[2] Click on Tools > Record Expression;
[3] Click on 'All' button;
[4] Click on 'Filter by Wildcard' button and type '2012-01-01';
[5] Click on the '2012-01-01' element and then click on 'Roll Up' button.

This gave me all the ancestors of 2012-01-01. The expression recorded was:

Code: Select all

{ TM1ROLLUP( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period Days] )}, "2012-01-01")}, { [Period Days].[2012-01-01] }) }
On the other hand, if I issue the following expression, it will bring only the 2012-01-01_YTD element:

Code: Select all

ANCESTORS([Period Days].[2012-10-22], 1)
Is there a way of doing something like TM1ROLLUP in Report Studio?

Best regards,

Fausto
Post Reply