cognos macro prompt prefix / suffix problems

Question

I am trying to change multiple columns based on one value prompt in report studio in a cube (running cognos 8.4). A prompt with a prefix would be a perfect solution but I cant seem to make it work. Typically I get a "Parsing error before or near position 4" even tho I can see its the exact name of a data item in the query.

I have one value prompt with YTD and QTD as static options. The parameter name is p_date. In my query I have a data item named YTD, QTD, PriorQTD, and PriorYTD. Additionally I have a data item named D_Prior which holds my prompt script - #prompt('p_date','token','[YTD]','[Prior','',']')#.

When I run the report, I get the default YTD as noted in the prompt. But when I select anything from my value prompt, it errors. As I understand it, the report should display [PriorYTD] if YTD is selected from the value prompt. Am I missing something here? Thanks in advance for any help.

Answer 1

If I understand correctly, you want to concatenate the value of your prompt selection within two strings: [Prior and ]. I think the problem may be that as written, your prompt macro doesn't escape the quotes or use syntax to make it clear that everything after your 3rd comma is one argument. So the parser is treating the [Prior, '', and ] elements as 3 more arguments, and is choking when it hits the 4th comma, which shouldn't be there.

Try wrapping the last argument in a concatenate function or maybe a parentheses with + between each element -- I'm not sure exactly what the syntax will be, but I'm pretty sure that's the source of your error.