In this blog we have a quick guide on how to conditionally format input and consolidated cells in Planning Analytics Workspace (PAW). Conditional formatting is a powerful tool for visually highlighting specific data points within a spreadsheet, Planning Analytics for Excel (PAX/PAfE) reports or Planning Analytics Workspace books. By applying conditions to cells, you can quickly identify values that meet certain criteria and easily distinguish them from the rest of your data.
We’ll demonstrate how to use conditional formatting to differentiate leaf cells from non-leaf cells in an IBM Planning Analytics Workspace book. This post will assist PAW Modellers in designing PAW books that effectively combine consolidated and input cells within the same asset.
Using conditional formatting in this way is a wonderful method to show users where they can enter data in a PAW book, especially where data spreading, or breakneck, is disabled. It will show when the user has N level elements in all dimensions and thus where data can be entered.
By visually highlighting leaf cells, users can effortlessly navigate to the appropriate cells for data entry and observe the immediate impact on the consolidation. This approach helps to mitigate the risk of accidental data entry at the consolidated level, ensuring data accuracy and integrity.
Method to Create Conditional Formatting in PAW to Highlight N Level Cells
While this task might seem straightforward, directly applying conditional formatting within IBM PAW is not feasible as at version 2.0.98. However, we can achieve the desired outcome through a two-step process:
- Firstly, create a Planning Analytics measure to determine if a cell is a leaf.
- Then leverage this measure in PAW to implement targeted conditional formatting rules.
Identify if the Cell is Leaf
To accomplish this task within Planning Analytics, we’ll follow a two-step approach. First, create a measure named ‘IsLeaf Flag’ in the measure dimension. Next, utilise a rule to evaluate each cell. If the cell represents a leaf, assign a value of 1 to ‘IsLeaf Flag’. Conversely, if the cell is a consolidated element, set ‘IsLeaf Flag’ to 0.
The screenshot below shows the existing structure of the cube Gross Profit from our Nebula Fit model.
Lets now complete two tasks, create new measure, apply rule to the new element.
Create New Measure
To distinguish between leaf and consolidated cells, we’ll introduce a new measure. This measure will serve as a flag to identify their respective types. The image below shows the existing measures within the ‘Measures – Gross Profit’ dimension.
Let’s complete the first task by adding new element called “IsLeaf Flag” into Measures – Gross Profit.
The screenshot below shows the data within the Gross Profit Cube. Please note IsLeaf Flag displays zero.
Using ISLEAF to Identify Input Cells for Conditional Formatting
To do the second task, we’ll leverage the Planning Analytics function ‘ISLEAF’. This function returns a value of 1 when a specified cell is a leaf. Conversely, if the cell is identified by any consolidated elements, ‘ISLEAF’ returns 0.
[‘Actual’,’IsLeaf Flag’] = IF( ISLEAF = 1, 1, 0) ;
The Rule and the Feeder are updated with code highlighted in the screenshot below.
As shown in the screenshot below, the IsLeaf Flag works as expected. Note that all dimensions in the context are also at leaf (N or Input level).
This activity concludes our first step.
Conditional Formatting in PAW
With our flag successfully implemented in the first step, let’s proceed to apply conditional formatting within PAW.
Create Exploration view
To accomplish this step, we’ll configure our exploration view within PAW to display products in the rows and measures in the columns, as illustrated in the screenshot below..
Apply Conditional formatting
Next, locate the ‘Unit Price’ column and right-click on it. From the context menu, select ‘Conditional Rules,’ as shown in the screenshot below. In PAW conditional formatting is performed using “Conditional Rules”.
In the newly opened window, select the recently created ‘IsLeaf Flag’ measure. Make the necessary selections as depicted in the screenshot below, and then click the ‘Edit’ option (the little pen out to the right) to apply the desired cell formatting.
In the new pop-up window, you have the flexibility to customise your styling using Cell, Font, and Icon Set options. For this example, we’ve focused solely on Cell Styling.
After clicking the ‘Apply’ button and refreshing the exploration view, you’ll observe the desired changes. Consolidated cells will display the newly defined style, while leaf cells will retain their default settings. You could have just as easily formatted the input cells to be, say yellow, and left the consolidated cells as white.
By following the same steps for ‘Units Sold’ and ‘Unit Cost’, you’ll achieve the following result:
As expected, we’ve successfully differentiated between consolidated cells and leaf cells.
Of course, the limitation here is that you need to evaluate the IsLeaf Flag against another measure and if you had consolidated measures, or rule driven measures at an N level, this method might not work directly.
The final step is to hide the ‘IsLeaf Flag’ column.
Hide Column
Last task is to hide the measure ISLeaf Flag, this task concludes our second and last step.
The desired outcome has been achieved. To customize your PAW models, you can apply all or a subset of the aforementioned steps.
Reports knowledge
We trust above content would have helped you gain some knowledge. To continue learning more about Reports and different scenarios and gain knowledge check out our series of posts all on Reports Series.
Do You Need Help Making Your PAW or PAfE Smarter?
If you need any help with making your Planning Analytics Workspace or Planning Analytics for Excel environment smarter, for example automating reports so they always display the current period automatically as the first element, or formatting PAW input cells, give us a yell. We’d love to help you.
