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
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"
Quote from: damzI on 14 Nov 2016 03:00:09 PM
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.
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
Quote from: damzI on 15 Nov 2016 08:51:11 AM
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.
MF - That was indeed real easy. Thanks again for you prompt and complete responses. This has fixed the issue I had ! - D