Blog Pro de Jean-Baptiste HEREN

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