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!
Ancestor function Versus More than 1 parent
- 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
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
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
-
- 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
Thank you for your reply, Michel.
Best regards,
Fausto
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).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.
Best regards,
Fausto
-
- 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
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:
On the other hand, if I issue the following expression, it will bring only the 2012-01-01_YTD element:
Is there a way of doing something like TM1ROLLUP in Report Studio?
Best regards,
Fausto
[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] }) }
Code: Select all
ANCESTORS([Period Days].[2012-10-22], 1)
Best regards,
Fausto