I am creating a report in Report Studio 8.4.1 from a PowerPlay cube. On that report, I need to be able to do the following:
- Dynamically slice the data on a time dimension (could be any dimension with a hierarchy, though). This is done via a slicer.
- Have a default value for the dynamic slicer. This is done via a #prompt()# macro in the slicer.
- Control the display of prompts (via a prompt page?) including the prompt for the slicer.
- Display the selected or default value for the slicer prompt on the report.
The first two I achieved, as I said. The third I'm struggling with; when I build a prompt page that displays a tree for the slicer parameter, it displays all levels in the hierarchy at once. I think I'm missing something on this; the #prompt()# macro on it's own generates a tree that works as expected: show the top level, let users drill down and select a specific element. If nothing is selected, the default I've provided is used. An interesting note on that is that the default I baked in to the #prompt()# macro is used even with the separate prompt page, which wasn't entirely expected (by me).
The fourth I've tried by just using paramDisplayValue() in a layout calculation. No joy. I've tried a few other things too, such as a query calculation that returns currentMember on the time dimension. Also no luck.
Any thoughts? Need more detail anywhere?
Thanks!
Ken
Are you using a designed prompt page or just the generated prompt page?
It sounds like a generated prompt page is coming up for and giving you a text box prompt because the prompt macro isn't specifying the data type of the parameter.
Try adding 'mun' to the prompt macro:
#prompt('paramater_name','mun','[xxx].[yyy]->[PC].[@MEMBER].[XX]')#
I'm using a designed prompt page. In the slicer, I have a macro which looks like so:
#prompt ( 'Month', 'memberuniquename', '[CUBE].[Time].[Time].[Month]->:[PC].[@MEMBER].['
+ timestampMask ( _first_of_month ( _add_months ( $current_timestamp, -1 ) ) , 'yyyymmdd' ) + '-'
+ timestampMask ( _last_of_month ( _add_months ( $current_timestamp, -1 ) ) , 'yyyymmdd' ) + ']'
, '', '[CUBE].[Time].[Time]' )#
This defaults the parameter to current month - 1 (July 2012 at present), and starts the tree prompt at the top of the time hierarchy. Then I've attached a prompt on the prompt page to the same Month parameter. If I remove the prompt from the prompt page, the auto-generated prompt works as expected: it displays only Time (the top of the hierarchy), and a user can drill down to the month they want to view. If I have the prompt in the prompt page, it displays as a tree prompt with Time as the first category, but then all other categories, in the entire hierarchy, displayed below it. It's possible to drill down in any ancestor category to get to the months that are descendants. Obviously I'm missing something, I just don't know what, and my colleague who's also working on this project doesn't see any errors in what I've done. :)
Interesting. I'm not sure if this is what happened to you, but I can duplicate that behavior following these steps:
1. Creating a new query item in the query that the prompt uses.
2. In the query, drag over the Time Hierarchy (when prompted, select all members).
3. Set the prompt to use this new query item.
If that is the problem for you, create a new query item that has the top member [Time] in it, and use that to drive the prompt.
That worked. (I could swear I tried that, too. Guess not.)
Thanks very much!