Page 1 of 1

Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Wed Jan 11, 2012 2:26 pm
by Ajay
Hi All

I have a dimension of legal entities, which has many levels within one hierarchy...i.e. is unbalanced.

This dimension is attached to a cube, which is published to Cognos 10.

I have a report which requires a prompt page, which is to only allow the end user to select the n-level elements from the dimension.

However, the prompt is displaying both the n-levels and the consolidations. I've temporarily added an attribute which is only populated for the n-levels in an attempt to filter on them, but this is not working in my prompt.

Has anyone experienced this challenge and how was it overcome.

Your help, as always, greatly appreciated.

Thanks
Ajay

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Wed Jan 11, 2012 2:36 pm
by jim wood
Have you tried creating a dynamic level 0 subset? Then may be set this as the default subset for the dimension? I'm no Cognos 10 expert and I'm not sure if this will work but it's something to try.

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Wed Jan 11, 2012 10:10 pm
by PlanningDev
Have you tried creating a query item, use the set expression setting. Then drag in your entire hierarchy and click ok. After this there is a button to create a set filter. Here you will add a filter based on your n level attribute. Then use this query item as the data value item for your prompt. I have had to do this and it works.

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Fri Jan 13, 2012 3:38 am
by zameelarif
Try creating named levels in TM1 and use them in your prompt query.

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Fri Jan 13, 2012 6:35 am
by Michel Zijlema
zameelarif wrote:Try creating named levels in TM1 and use them in your prompt query.
But when using named levels the dimension hierarchy needs to be balanced - the legal entity dimension of OP isn't.

Michel

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Wed Jan 25, 2012 4:28 am
by ItsPat
Create an attribute called 'Level' and just fill it in with what you want to filter. So in this case fill it in with Level 0 (you can call it whatever you want) and then use the filter() command and filter against that in BI.

Example:
Filter([MyCube].[MyDimension].[MyDimension], [MyCube].[MyDimension].[MyDimension].[Level]='Level 0')

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Wed Jan 25, 2012 5:01 pm
by Martin Ingram
We overcame something similar in report studio by creating a parallel hierarchy in the dimension - a new parent that only has the N'level elements underneath it.

The prompt then just has a query that is set to show all descendants of.....

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Wed Jan 25, 2012 7:51 pm
by Ajay
@itspat - I've already got an attribute setup, but I would imagine my filter is not quite right so I shall try it again.

@Martin - in your approach would I need to position the new hiearchy first in the dimension ? and would i also need to update the }HierarchyProperties cube with the new rollup ?

Thanks for your suggestions, I shall try both and report back to the forum

Cheers
Ajay

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Thu Jan 26, 2012 2:31 pm
by Martin Ingram
Ajay - no the new hierarchy doesn't need to be the first, and we didn't need to set anything up in the }HierarchyProperties cube

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Posted: Thu Jan 26, 2012 11:45 pm
by paulsimon
Ajay

We use a similar approach to the one that Martin is suggesting. We have about 15 different hierarchies in our Date dimension. Default Rep Dates is a consolidation of all n level dates, then we have All Report Dates which has levels of Year, Month, Day. We make all the different consolidations join up to an All Reporting Dates consolidation, using 0 weights. This just makes the hierarchy easier to navigate than having lots of separate hierarchies. Since the }HierarchyProperties cube only allows the levels of one hierarchy to be named, we just name the levels of the All Report Dates hierarchy so our levels are
All Reporting Dates
All Report Dates
Year
Month
Date.

That does mean that for the other hierarchies the level names are meaningless. However, that is something we can work around in Cognos BI.

For our prompt which just needs a list of N level dates, we just take Children of Default Rep Dates.

Regards

Paul Simon