Dynamically selecting a dimension

[halkosj]

In a cross-tab report using a Cognos Powercube, we need to dynamically select one of the dimensions, based on the user response to a prompt.

We've tried the CASE construct, but not sure what to put in the THEN expressions:

CASE ?prompt_response?
WHEN 'A' THEN Dimension_A
WHEN 'B' THEN Dimension_B
END

What do we put for Dimension_A and Dimension_B ??

Everything we've tried results in either a parsing error, or incorrect results.

[CognosPaul]

Try using a prompt macro:

#prompt('prompt_response','token')#

You can put dimensions, hierarchies, sets, levels, individual MUNs, and even tuples into the use for the prompt.

[rockytopmark]

What is your error?  Are trying to select the entire dimension? ...why?

I would suggest you need to select lower than the dimension, at least a hierarchy, for your query item to work.

Case ?pSelection?
When 'A' then [Namespace].[Dimension A QS].[A Hierarchy]
When 'B' then [Namespace].[Dimension B QS].[B Hierarchy]
End

Furthermore, when referencing a Hiearchy, you need to be careful to get only the desired members... by default you will get all.  Specify rootmembers() to get only those members at the root (the All member)

[halkosj]

First of all, we discovered that MDX does not support the CASE statement.

If we re-formulate the CASE into IF...THEN...ELSE, we get the following error:

==========
OP-ERR-0205

Unsupported 'if' expression for dataItem="DataItem1". The 'then' clause (expression="members([Namespace].[Dimension].[Hierarchy].[Level])") must return a single value.
==========

No we're not necessarily trying to select the entire dimension - just trying to find something that works.  We've tried the dimension, the hierarchy, the level - all result in the same error.

[jpadilla]

As mentioned by PaulM, prompt macros is the way to go:

#prompt('Prompt','token')#

Steps:
Create a value prompt
Define statics values (on the use value, use your dimension - something like [Cube].[All Time] ) <- where Cube is your cube
(you probably will use a level instead of a dimension, but that really depend on your need).
Create a query data item in your crosstab
Use #prompt('Prompt','token')#  <- where Prompt is your prompt name

Works nicely.

[halkosj]

I'm not sure what the prompt macro is going to do for us, since we already have a prompt on the prompt page.

Just tried it and it prompts the user twice - once from the value prompt on the prompt page and once from the prompt macro.

The static choices we created for the value prompt are:
Display         Use
Army          [cube].[namespace].[dimension(Army)].[hierarchy].[level]
Navy          [cube].[namespace].[dimension(Navy)].[hierarchy].[level]

Running the report brings up the prompt page - we select Army from the value prompt - click Finish.
Another prompt comes up - this is a free form type-in the value prompt.  Type in "Army" . Click OK.
And then it gives us an error:

QE-DEF-0260
Parsing error before or near position: 5 of: "Army"

Okay - tried it again, and this time on the second prompt, we typed the actual name of the level we want: [cube].[namespace].[dimension(Army)].[hierarchy].[level].

This worked - okay, so how do we avoid the double prompting?  We need to use only one prompt page with all the prompts on it. Also, the users running this report have no knowledge of cubes, or hierarchies, or levels - they have no way of even knowing what we named them. So they cannot type in the Level Unique Name. They must be presented with a pull down list of simple selections (Army, Navy, etc.)

[godawgs85]

As mentioned by PaulM, prompt macros is the way to go:

#prompt('Prompt','token')#

Steps:
Create a value prompt
Define statics values (on the use value, use your dimension - something like [Cube].[All Time] ) <- where Cube is your cube
(you probably will use a level instead of a dimension, but that really depend on your need).
Create a query data item in your crosstab
Use #prompt('Prompt','token')#  <- where Prompt is your prompt name

Works nicely.

Any idea on how to refer to a filtered set in the prompt for a dimensional database, using the filter() function ?  Fore example - Static Choice 1 would be [Cube].[Hierarchy].[Dimension].[Level] , Static Choice 2 would be filter ( [Cube].[Hierarchy].[Dimension].[Level] , [Measure]>0 ).  I don't think this can be done in static choices of a value prompt, so is there another way to achieve this?

Thanks!

[CognosPaul]

Static choices should absolutely support this. Remember, the token simply takes an expression fragment. Static choice 1 would return the entire level and static choice 2 would return a level wrapped in the filter function. Replacing that 0 in the filter with another parameter would be difficult, so if you need to make that a reference to another field and put the parameter in there.