Cognos 8 - convert a member unique name from one hierarchy to another
Posted on Wednesday 06 April 2011, 15:45 - Décisionnel - Permalink Tweet
- Article
- |
- Comments(0)
- |
- Attachments(0)
If you need to build any report or Event studio Query using the same data from different packages; and you want to prompt just once.
You can convert the member unique name from one hierarchy to another, using the cognos substitute macro.
Consider the following example :
You prompt on level value using the following hierarchy :
[First package].[First dimension].[First hierarchy].[First Level]
Then you want to filter one Query built on another package using the same prompt value :
[Second package].[Second dimension].[Second hierarchy].[Second Level]
It means yo uwant to convert :
-- Source hierarchy
[First package].[First dimension].[First hierarchy].[First Level]->:[YK].[[First dimension]].[First hierarchy]].[First Level]].&[00004]]]
-- to Destination Hierarchy
[Second package].[Second dimension].[Second hierarchy].[Second Level]->:[YK].[[Second dimension]].[Second hierarchy]].[Second Level]].&[00004]]]
So you can use a Query element containing a substitute macro :
-- Formula
# sq(substitute ('First package','Second package',
substitute ('First hierarchy','Second hierarchy',
substitute ('First dimension','Second dimension',
substitute ('First Level','Second Level',
prompt('First Level Prompt Name', 'token')))))) #Post updated on Wednesday 06 April 2011, 15:45