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 :
[SQL] [First package].[First dimension].[First hierarchy].[First Level]
Then you want to filter one Query built on another package using the same prompt value :
[SQL] [Second package].[Second dimension].[Second hierarchy].[Second Level]
It means yo uwant to convert :
[SQL] -- 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 :
[SQL] -- 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')))))) #