For a report based over a Transformer Cube, I currently have an optional slicer set up as follows - which functions as required:
- #promptmany('parm_Store', 'memberuniquename', '[Store Hierarchy]','set(', '[Store] ',')')#
This is based on value prompt containing All Stores and allows selection of multiple Stores.
My requirement now is to allow selection of Region(s) (and then likely District(s) too); Store Hierarchy is Region/District/Store.
I believe I will have to set up additional value prompts for Region (and District), but how do I modify the slicer to:
- Use any Store Selections
- If none, go up the Dimension to any District Selections
- If none, go up the Dimension to any Regions Selections
- If none, make no Selections
These are all in the same dimension, so I cannot make additional slicers.
I am hoping there is logical syntax to do this!
Any help would be really appreciated.
Thanks in advance,
Adam.
I have implemented a similar requirement. It uses just a single prompt query that gets dynamically populated based on the level a user chooses. All you need is a single slicer. Maybe this will work for you:
- Set up a prompt with static choices for the user to select which level they want. Assume the parameter name for this is PromptType. For the "use" value of each static choice, specify the appropriate level such as [YourCube].[By Location].[By Location].[Region]
- Use a second prompt page for a prompt control to satisfy your parm_Store parameter.
- The query to populate this second prompt control should have a query item with this expression: members ( #prompt('PromptType','token')# )
- Make that query item the "use" value for the prompt control.
- The same slicer you already have should now behave according to whatever the user selects
Another way:
#promptmany('parm_Store', 'memberuniquename', promptmany('parm_District', 'memberuniquename', promptmany('parm_Region', 'memberuniquename', '[All Member]','set(', '[Reion] ',')'),'set(', '[District] ',')'),'set(', '[Store] ',')')#
Lynn/Paul...
Thank you for both your solutions!
I ended up using Paul's suggestion as that worked more closely with what I have now.
Thanks again,
Adam.