cascading prompt in report studio using a cube

[damzI]

hello All,
This one is turning out to be a bear...

I am trying a cascading prompt on my Region dimension. The first prompt (A) has the 3 levels of my region set up as static choices - Country, State, City - it is a single-select prompt.
My Prompt B cascades on Prompt A, so I can have values (members) in Prompt B to select for my report.

Then I am using a Prompt macro to populate my cross-tab report, from the Prompt B selection - but it errors out (it seems that I need a default value in the Macro). But I cant have a default selection as the vlaues could be from any of the 3 levels.

Have tried a host of solutions and now asking for any one to help...

Thansk, D

[damzI]

here is the error I get when using the prompt macro in my crosstab

QE-DEF-0260 Parsing error before or near position: 10 of: "testValue"

[MFGF]

hello All,
This one is turning out to be a bear...

I am trying a cascading prompt on my Region dimension. The first prompt (A) has the 3 levels of my region set up as static choices - Country, State, City - it is a single-select prompt.
My Prompt B cascades on Prompt A, so I can have values (members) in Prompt B to select for my report.

Then I am using a Prompt macro to populate my cross-tab report, from the Prompt B selection - but it errors out (it seems that I need a default value in the Macro). But I cant have a default selection as the vlaues could be from any of the 3 levels.

Have tried a host of solutions and now asking for any one to help...

Thansk, D

Hi,

This seems entirely feasible to achieve. This is the approach I'd take:

1. Create a prompt page for the first prompt (A). Add a Value Prompt that sets a parameter (Level) but doesn't use a query. Add Static Choices to this prompt - one for each level. Set the Use Value for each to be the full path of the level (eg [great_outdoors_sales_en].[Sales regions].[Sales regions].[Sales region]) and the display value to be whatever text you want to appear in the prompt (eg Sales Region).
2. Create a second prompt page for the second prompt (B). Add a value prompt that sets a parameter (Members) and base the prompt on a query. In the query of the prompt, add a calculated item that uses a prompt macro to fire up the Level prompt and determine the level, and you can also add a default for this if you wish eg #prompt('Level','token','[great_outdoors_sales_en].[Sales regions].[Sales regions].[Sales region]')# Set the calculated item as the Use Value in the properties of Prompt B.
3. In your crosstab, add a query calculation to prompt for the Members parameter via a dimensional expression based on your hierarchy:  set([your Hierarchy] -> ?Member?)
eg set([great_outdoors_sales_en].[Sales regions].[Sales regions] -> ?Members?)

I have attached an example report spec based on the Great Outdoors Sales En sample Powercube.

Cheers!

MF.

[damzI]

Hi MF,
Thank you for your prompt and detailed response. That was good and it worked - except that it does not work when I have both the prompts on the Report page. Am I missing something ?
- D

[MFGF]

Hi MF,
Thank you for your prompt and detailed response. That was good and it worked - except that it does not work when I have both the prompts on the Report page. Am I missing something ?
- D

Hi,

Yes - to have the prompts on the report page, you need to define a default selection for each one, or else the page won't render (since the crosstab edges won't have been fulfilled).

Here's a modified example (attached).

Cheers!

MF.

[damzI]

MF - That was indeed real easy. Thanks again for you prompt and complete responses. This has fixed the issue I had ! - D