Cognos 8 - convert a member unique name from one hierarchy to another
Publié le mercredi 06 avril 2011, 15:45 - Décisionnel - Lien permanent Tweet
- Article
- |
- Commentaires(0)
- |
- Fichiers attachés(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')))))) #
Article modifié le lundi 09 mai 2011, 10:25
Auteur: Jean-Baptiste HEREN
Restez au courant de l'actualité et abonnez-vous au Flux RSS de cette catégorie
