Hi all,
I'm currently developping a report from a Power Cube as the data source.
In the cube, 3 dimensions :
- Axe 1
- Axe 2
- Axe 3
and a measure folder.
In my report, I created an Item with this expression : #promptmany('P_AXE','memberuniquename','[MYCUBE].[Axe 3].[Axe 3].[Axe 3]','set(','',')')#
It works fine.
But because of the 3 separated dimensions, I have to create 3 reports : one by Axe.
Do you know if it's possible to use an other prompt macro into the expression, in order to develop only one report and let the user select the number af the Axe ?
I tried this (replace the number by an other prompt) , but it's NOK :
#promptmany('P_AXE','memberuniquename','[MYCUBE].[Axe '#prompt('P_NB_AXE')#'].[Axe '#prompt('P_NB_AXE')#'].[Axe '#prompt('P_NB_AXE')#']','set(','',')')#
Any other ideas ?
Thank in advance !
Sébastien
Nesting prompts isn't a problem.
A few things.
Hashes delineate the start and end of macros, so you only need the two:
#promptmany('P_AXE','memberuniquename','[MYCUBE].[Axe ' prompt('P_NB_AXE') '].[Axe 'prompt('P_NB_AXE') '].[Axe 'prompt('P_NB_AXE')']','set(','',')')#
Macros build strings which Cognos attempt to execute as code. So knowing this, you should concat the '[MYCUBE].[AXE ' with the next value.
#promptmany('P_AXE','memberuniquename','[MYCUBE].[Axe '+prompt('P_NB_AXE')+'].[Axe '+prompt('P_NB_AXE')+'].[Axe '+prompt('P_NB_AXE')+']','set(','',')')#
Finally, by default the prompt function defaults to strings. You need to set it to token (or possibly integer):
#promptmany('P_AXE','memberuniquename','[MYCUBE].[Axe '+prompt('P_NB_AXE','token')+'].[Axe '+prompt('P_NB_AXE','token')+'].[Axe '+prompt('P_NB_AXE','token')+']','set(','',')')#
If you leave the datatype param blank, you'd end up with something like:
[MYCUBE].[Axe '1'].[Axe '1'].[Axe '1'] (notice the apostrophes).
Now when it runs it checks for the P_AXE parameter. If that is null, it will attempt to run the default parameter which contains the P_NB_AXE param. I recommend putting a default value in there so it won't be a required parameter.
PaulM, you're my #macro# MASTER !
Your solution works perfectly.
NB : It is necessary to add a default value into the second prompt macro :
#promptmany('P_AXE','memberuniquename','[MYCUBE].[Axe '+ prompt('P_NB_AXE','token','1') +'].[Axe '+ prompt('P_NB_AXE','token','1') +'].[Axe '+ prompt('P_NB_AXE','token','1') +']','set(','',')')#
Thanks again & again !!