This is a guest post from Revelwood’s Jay Apwah.
Did you know that in Planning Analytics for Microsoft Excel (PAx) and Perspectives you can customize action button messages? Well you can go one step further and make those messages dynamic.
In this blog, we will walk through the following steps:
- Setup cells in a PAx workbook for the custom messages.
- Edit an action button to use the custom messages.
In this example, we have a PAx worksheet that allows users to click an action button to run a TI process using two parameters: the name of a file to load, the period to which the data will be loaded in a cube called bpmFinance. Whenever this process is executed, it logs statistics into a control cube: Number of records in the source file and Number of skipped records.
Below are the custom messages that we want to be shown to the user upon clicking the action button:
- Confirmation message: “Are you sure you want to load the <file name> into the <period> period?”
- Success message: “Process completed successfully – <X of X> records loaded”
- Failure message: “Process failed. Records in file: <X>. Skipped Records: <Y>
Step 1: Setup custom messages
In any row that can be hidden, use a combination of Excel and TM1 worksheet functions to construct the custom messages. In this example, we will end up hiding rows 13-15. This is where our custom messages are constructed.
Figure 1: Example of formula to construct the confirmation message.
Formula:
=”Are you sure you want to load the “&C4&” file into the “&C5&” period?”
Figure 2: Example of formula to construct the success message.
Formula:
=”Process completed successfully – “&DBR(“<server>:bpmControls”, “Value”, “GL Actual File – Total Count”)&” of “&DBR(“<server>:bpmControls”, “Value”, “GL Actual File – Total Count”)&” records loaded.”
Note: The formula above retrieves the process statistic: “GL Actual File – Total Count” from a cube called bpmControls.
Figure 3: Example of formula to construct the failure message.
Formula:
=”Process failed. Records in file: “&DBR(“HQCorp:bpmControls”, “Value”, “GL Actual File – Total Count”)&”. Skipped records: “&DBR(“HQCorp:bpmControls”, “Value”, “GL Actual File – Skipped Count”)
Note: The formula above retrieves the process statistics: “GL Actual File – Total Count” and “GL Actual File – Skipped Count” from a cube called bpmControls.
Step 2: Edit action button to use the custom messages
- In the properties of the action button that runs the TI process, click “Options”
- The default success, failure and confirmation messages are displayed.
- Since the custom confirmation, failure and success messages are in cells C13, C15 and C14, respectively. Change the default messages from hard-coded words to the appropriate Excel references as shown below.
Result
Dynamic confirmation message
Now when the “Run” action button is clicked, a confirmation message appears. The confirmation message is dynamic and picks up the parameter values entered/selected by the user.
Dynamic success message
When the confirmation is accepted, if the process completes successfully, the dynamic success message would appear. Notice how the success message displays the statistics that the process logged in the control cube.
Dynamic failure message
When the confirmation is accepted, if the process failed, the dynamic failed message would appear. Notice how the success message displays the statistics that the process logged in the control cube.
IBM Planning Analytics is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
