Hi all
I need to create an expression which is fed by 2 prompts. So far I have the following which doesn't work
#Prompt('pDataSource','token','Retail','[','','+ prompt('pMeasure','token','Cash') +MeasureType]')#
So assuming the defaults are selected I want to end up with this [RetailCashMeasureType]
Can anybody help?
thanks
Lloyd
In this case, you don't need to nest the prompts. Since you already have the answer, I'll explain your macro actually does, and the logic behind fixing it.
First glance:
#prompt('pDataSource','token','Retail')#
This tells Cognos there is a parameter called pDataSource that is a code fragment. If the user does not enter a value, it will default to Retail. Note, it literally defaults to Retail. Since it's not wrapped in brackets or quotes, Cognos will reject the statement as Retail is neither a reserved word, like sysdate or current_timestamp, or a function. Instead, you can have that default to [RetailCashMeasureType] fairly easily:
#prompt('pDataSource','token','[RetailCashMeasureType]')#
Next you have the before and after text set:
#prompt('pDataSource','token','[RetailCashMeasureType]','[','','+ prompt('pMeasure','token','Cash') +MeasureType]')#
If the user selects something, it will add the first bracket before, and the second prompt and bracket after. Personally, I think this looks a bit crowded with all the brackets in the statement. I'm also assuming that the +MeasureType]' bit is a typo.
Now we have:
#sb(prompt('pDataSource','token','RetailCashMeasureType','','','+ prompt('pMeasure','token','Cash') +'MeasureType'))#
The sb() function will wrap whatever is inside it with square brackets. In the previous code, however, if the user doesn't select any DataSource, then it will always default to RetailCashMeasureType, even if they select a different Measure.
Let's simplify things even more:
#sb(prompt('pDataSource','token','Retail')+ prompt('pMeasure','token','Cash') +'MeasureType')#
Now both parameters are processed at the same time, and both have default values that will be taken into account. There are no extra brackets anywhere in the macro to confuse people who are looking at the statement for the first time.
Paul, you are terrific. I read your posts just because I know I'll appreciate the elegance of solutions you offer!
Paul thanks for your very detailed explanation :)
Quote from: Lynn on 29 Mar 2013 08:42:23 AM
Paul, you are terrific. I read your posts just because I know I'll appreciate the elegance of solutions you offer!
If I see PaulM posted to a thread, I will nearly always read it because I know I will probably learn something. 8)
And there is his blog, which has tasty morsels about once a month.
https://cognospaul.wordpress.com/ (https://cognospaul.wordpress.com/)