Prompt token macros are used to pass the definition of a data item rather than a value. They are very efficient and useful for both relational and dimensional data sources and prove to be a friendly administrative and end user experience. You can use the relative time token for scheduling purposes as well as a default selection. Here’s how it works in BI 10.2.1 using the sales_and_marketing sample cube as the data source!
Let’s create a simple crosstab with product members on the rows and revenue as the measure.
Next, go into the query and drag a data item from the toolbox into the query. Name the data item (in this example, I named it ‘Selected Month’) and set up the syntax for the prompt macro exactly as it appears – #prompt(‘Month’,’token’)#.
‘Month’ can be named anything, but it makes sense to name it something related to what you are going to prompt for in the query. We will set up a prompt using the Time dimension/Month level in the cube with a static choice for the ‘Current Month’ relative time period. This provides the end user with the ability to run the report for the current month and any of the available months in the cube, as well as dynamically schedule/run the reports.
The ‘token’ piece is used to pass the definition of the item that we will define in the static choice or select in the prompt. It’s a reserved word and must appear in single quotes as seen above.
At this point, we can add the newly created ‘Selected Month’ data item as the column on our crosstab.
Now let’s create a prompt page and add a Value Prompt to the prompt page. In the Prompt Wizard, we’ll use the existing parameter ‘Month’ that we created in the ‘Selected Month’ data item in the query.
In the next step of the Prompt Wizard, I named my query ‘Q_Prompt’ and navigated to the Month level of the sales_and_marketing sample cube in the Values to use area.
The wizard should look like the following illustration after setting the Values to use to the Month level of the Time dimension. The Value Prompt will be added to the prompt page after you click Finish.
If we run the report now, the prompt option will appear with only the months available in the cube. We want the relative time ‘Current Month’ to also be available in the prompt. We need to set up a Static Choice.
With the Value Prompt highlighted on the prompt page, select the ellipsis for Static Choices under the Data section.
The Use value will be linkmember(firstChild([sales_and_marketing].[Time].[Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month]),[sales_and_marketing].[Time].[Time].[Month]).
The Display value will be ‘Current Month’.
The logic we set up as the Use value finds the first child member of the ‘Current Month’ member and links that member to the same member in the Month level. The current month is July 2013 (2013/Jul).
Everything is complete in order for the Prompt Token Macro to work. However, I’d like to set up ‘Current Month’ as the default prompt selection and change the prompt type. These properties can be found under the General section of the Value Prompt. The Default Selections property needs to be defined with the same syntax as the Use value in the Static Choice. You can change the prompt type by choosing an option for the Select UI. I chose Radio button group.
When you run the report now, ‘Current Month’ will appear in the list as a selected item.
If ‘Current Month’ is selected on the prompt page, then the report will render revenue for the products for July 2013.
Here’s the report specification of all the aforementioned steps. Again, the specs can be used with the sales_and_marketing sample cube provided by IBM to further illustrate the technique. Happy prompting!
