Cognos 10 Report Studio Prompt from TM1 dimension
- 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
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
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
- jim wood
- Site Admin
- Posts: 3953
- 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
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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
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.
-
- 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
Try creating named levels in TM1 and use them in your prompt query.
- 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
But when using named levels the dimension hierarchy needs to be balanced - the legal entity dimension of OP isn't.zameelarif wrote:Try creating named levels in TM1 and use them in your prompt query.
Michel
Re: Cognos 10 Report Studio Prompt from TM1 dimension
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')
Example:
Filter([MyCube].[MyDimension].[MyDimension], [MyCube].[MyDimension].[MyDimension].[Level]='Level 0')
-
- Posts: 55
- 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
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.....
The prompt then just has a query that is set to show all descendants of.....
- 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
@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 - 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
-
- Posts: 55
- 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
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
- 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
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
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