Add different member of a dimension in a crosstab depending on a prompt

[philelmousse]

Hello,

My goal is to add different member of a dimension in a crosstab depending on a prompt. In my query I add a data item with something like that :

CASE ?Report?
WHEN 'ReportName1' then set ([Member1],[Member2])
WHEN 'ReportName2' then set ([Member3],[Member4])
end

I tried to add that data item in my crosstab but i get that error : The 'ces_when' function is not supported in the context in which it is used.

Is it possible to make something like that ?

[CognosPaul]

MDX doesn't have an CASE WHEN statement, and IIF statements are used more for measure values. What you can use instead is macro functions.

In your prompt, set the use values to your set. Assuming parameter name Param1
USE:                                          Display
set([Member1],[Member2])       Set 1
set([Member3][Member4])        Set 2

Create a data item with the expression:
#prompt('Param1','token')#

The token will take whatever statement is passed to it and attempt to execute it. Another example is:
#prompt('TopOrBottom','token')#Count([level],[Measure],#prompt('Number','token')#)

This will allow you to create prompts that will let the user select the top or bottom count of Number values.  (Not at a Cognos installation, so the order of the params may be different.)

[philelmousse]

Thanks for that.

You mean create two set expression in the query used by the prompt ? Because i can only set one use and one display.

[CognosPaul]

No, don't set the prompt to a query. Use the static choices, and paste in the set expression into there. Remember to disable the "alias member unique names" option before creating the set. The idea is your passing the set expression to the crosstab, not individual members.