When formatting a report to highlight variances, changing the numerical format to display a negative value with brackets around the number is usually the default method used to highlight differences. Visually, this format isn't the most effective for users to quickly analyse variances. Add emoticons to your report to enhance its visualisation.
Here, I’m going to show how to do this in IBM Planning Analytics, by updating this report
to include business logic to display emoticons.
- How to set up the Cube
- How to access emoticons
- How to apply the logic
Screenshots are based on these resources:
- Excel 2016
- IBM Planning Analytics 2.0.6 / TM1 Server 11.4
- Windows Server 2016
- Arc for TM1 – Developer Utility
1. How to set up the Cube
Emoticons are basically text. To store text in a TM1 cube, a string element must be created in the last dimension defined in the cube.
Here is the structure of the cube I will be working with. The last dimension is ‘Product Scorecard Measure’.
In the ‘Product Scorecard Measure’ dimension, I will add the string element ‘Indicator’, that will be used to store the emoticon.
2. How to accces emoticons
There are two ways to access emoticons in Windows:
- Press on ‘Windows‘ + ‘;‘ on the keyboard at the same time and the emoticon window will appear
- Use the touch keyboard
Right-click on the task bar and select ‘Show touch keyboard button’.
Click on the touch keyboard in the task bar.
3. How to apply the logic
The business logic for the ‘indicator’ will display a ? if Actual is greater than Budget, otherwise display a ❌. In TM1, the logic will be written in the Cube’s rule like the following.
To enable zero suppression for string elements, ‘FEEDSTRINGS;’ must be defined in the first line.
This rule is pretty simple and can be enhanced further if you want to include multiple conditions to return different emoticons.
Refresh the view in IBM Planning Analytics to see the result. This is how it appears in IBM Planning Analytics Workspace.
And here with IBM Planning Analytics for Excel.
Want to learn how to write your own business logic? Attend our Advanced Rules course to take advantage of this tip or enhance its capabilities.