Creating Prompt Page in IBM PAX Report
In IBM Planning Analytics for Excel (PAX) you can create a prompt page that links to all the other worksheets in your report. This will give your reports a nice professional feel and can make them easier to maintain as your system matures and you move from one fiscal year to the next.
Before we start walking through creating a prompt we need to note that a prompt page cannot be used with an “Exploration View” report type. Prompt pages will only work with Quick Reports, Dynamic Reports, or Custom Reports. A majority of the time you or your users will be generating an exploration view but it is easy to convert it to a quick report.
1. Take an existing exploration view report, and convert it to a quick report on a new sheet.
2. Insert a new blank worksheet as the beginning of your workbook, rename it to “Prompt Page”.
3. The prompts on your prompt page are based on a TM1 Excel function that queries a dimension. Insert the following function into a cell:
=SUBNM(“Your Server Name:Version”, “Lowest Level”, “October Forecast”, “Name”)
“Your Server Name:Version” – Insert your server name and the dimension you want to prompt on. I chose a dimension named Version.
“Lowest Level” – The name of a subset you want to use for prompting.
“October Forecast” – The default element to be selected. You can leave this blank as two double quotes (“”) and your prompt will always initially show as blank.
“Name” – The Alias to use in the prompt. You can leave this blank as two double quotes (“”) and the technical ID of the element will display.
4. Link the filtering in your quick report to the cell in the prompt page holding the value. In a quick report you will notice that the first few rows are always hidden. This is where the filters are set for your quick report. Since version is on my column I just linked the version name shown below to the prompt page.
5. Refresh your sheet and you now have a fully functionality dynamic PAX report.