Cognos 10 Report Studio Prompt from TM1 dimension

Post Reply
User avatar
Ajay
Regular Participant
Posts: 183
Joined: Wed May 14, 2008 8:27 am
OLAP Product: TM1
Version: 10.2.0, PA 2.0.9
Excel Version: 2016
Location: London

Cognos 10 Report Studio Prompt from TM1 dimension

Post 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
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Post 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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
PlanningDev
Community Contributor
Posts: 349
Joined: Tue Aug 17, 2010 6:31 am
OLAP Product: Planning Analytics
Version: 2.0.5
Excel Version: 2016

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Post 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.
zameelarif
Posts: 13
Joined: Tue Apr 07, 2009 8:46 am
Version: 9.1.3
Excel Version: 2007

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Post by zameelarif »

Try creating named levels in TM1 and use them in your prompt query.
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: Cognos 10 Report Studio Prompt from TM1 dimension

Post 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
ItsPat
Posts: 7
Joined: Sat Sep 05, 2009 9:50 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Post 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')
Martin Ingram
Posts: 52
Joined: Thu May 15, 2008 9:11 am
OLAP Product: Planning Analytics
Version: IBM SaaS - Digital Pack
Excel Version: Office 365
Location: Reading / London
Contact:

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Post 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.....
User avatar
Ajay
Regular Participant
Posts: 183
Joined: Wed May 14, 2008 8:27 am
OLAP Product: TM1
Version: 10.2.0, PA 2.0.9
Excel Version: 2016
Location: London

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Post 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
Martin Ingram
Posts: 52
Joined: Thu May 15, 2008 9:11 am
OLAP Product: Planning Analytics
Version: IBM SaaS - Digital Pack
Excel Version: Office 365
Location: Reading / London
Contact:

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Post 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
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Post 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
Post Reply