Cognos 10 Report Studio Prompt from TM1 dimension

Cognos 10 Report Studio Prompt from TM1 dimension

Postby Ajay » Wed Jan 11, 2012 2:26 pm

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
x64bit 10.1.1, w2008R2 OS Enterprise Edition, 1TB HD, 72GB RAM, MS Excel 2007/10, Cognos 10 BI
User avatar
Ajay
 
Posts: 110
Joined: Wed May 14, 2008 8:27 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 and 2010

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Postby jim wood » Wed Jan 11, 2012 2:36 pm

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.
Legal Advisory: These are my personal opions... My opinion is I'm right most of the time.
ACG TM1 / Essbase Consulting
Server(s): Windows Server 2008 SP1 64-bit. TM1 Version: 9.5.2 Client(s): Windows XP.
User avatar
jim wood
Site Admin
 
Posts: 2607
Joined: Wed May 14, 2008 1:51 pm
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
OLAP Product: TM1
Version: TM1 10.1.1 EV 9.5.0
Excel Version: 2007

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Postby PlanningDev » Wed Jan 11, 2012 10:10 pm

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.
PlanningDev
Regular Participant
 
Posts: 309
Joined: Tue Aug 17, 2010 6:31 am
OLAP Product: TM1
Version: 10.1.1 FP1
Excel Version: 2010

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Postby zameelarif » Fri Jan 13, 2012 3:38 am

Try creating named levels in TM1 and use them in your prompt query.
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

Postby Michel Zijlema » Fri Jan 13, 2012 6:35 am

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
User avatar
Michel Zijlema
Site Admin
 
Posts: 632
Joined: Wed May 14, 2008 5:22 am
Location: Netherlands
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Postby ItsPat » Wed Jan 25, 2012 4:28 am

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')
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

Postby Martin Ingram » Wed Jan 25, 2012 5:01 pm

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.....
Martin Ingram
 
Posts: 49
Joined: Thu May 15, 2008 9:11 am
Location: Reading / London
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Postby Ajay » Wed Jan 25, 2012 7:51 pm

@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
x64bit 10.1.1, w2008R2 OS Enterprise Edition, 1TB HD, 72GB RAM, MS Excel 2007/10, Cognos 10 BI
User avatar
Ajay
 
Posts: 110
Joined: Wed May 14, 2008 8:27 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 and 2010

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Postby Martin Ingram » Thu Jan 26, 2012 2:31 pm

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
Martin Ingram
 
Posts: 49
Joined: Thu May 15, 2008 9:11 am
Location: Reading / London
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: Cognos 10 Report Studio Prompt from TM1 dimension

Postby paulsimon » Thu Jan 26, 2012 11:45 pm

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
User avatar
paulsimon
MVP
 
Posts: 378
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 9.5.1 HF24
Excel Version: 2003


Return to Cognos TM1

Who is online

Users browsing this forum: Google [Bot] and 6 guests

Loading