IBM Planning Analytics Workspace – Hierarchy and Attribute Formatting!
By Scott Baker, Education Services, ActionKPI
The release cadence of IBM’s Planning Analytics tools, namely Planning Analytics Workspace (PAW) and Planning Analytics for Excel (PAfE) is really impressive; we typically get a new version every two months or so. Each release comes with new features for administrators, modelers, analysts, and consumers.
These features are often announced in the quarterly Product Updates – What’s New with Planning Analytics webinars, which you should totally check out if you haven’t already. These webinars will often have the product team hinting at features on the roadmap, and one of the features I’ve personally been most excited for since it was first teased showed up in the 103 release earlier this Spring: Hierarchy Formatting.
Let me take a step back to explain why I was so happy to see this feature. I’ve been working with Planning Analytics in one way or another (developer, technical pre-sales, education) since 2007. One of the features I’ve seen users go ga-ga over since the early TM1Web days is Consolidated type-in.
This feature allows a user to type a value into a consolidated cell. The value will then be automatically spread across the descendants of that consolidation at the lowest (leaf) level:
If these lowest cells are completely empty, the value will be spread equally across them. If the cells are already populated the value will be spread proportionally. This is a fantastic ability and can save tons of time.
If it’s done on purpose.
Now, one of the reasons that many people loved the legacy cube viewer was that it was very easy to see the difference between consolidated and leaf cells:
In the legacy tools (Architect, Perspectives, and TM1Web) consolidated, rule-derived, and read-only cells were formatted with a grey background, while leaf (and string) cells were white. This clearly indicated the difference to a user and they would be making a conscious choice to enter a value into a consolidated cell.
In the modern cube viewer rule-derived cells are shaded green by default, and read-only cells will default to grey. Consolidated cells are indicated only by having a bold font:
If a user isn’t paying close attention, it would be quite easy to type into a consolidated cell and perform an unintentional spread. If even one of the context dimensions (the dimensions not on the rows or columns) is at a consolidated level every cell in the view will be consolidated and not only will a spread be performed, but the user could be blissfully unaware that this has occurred since the spread will be occurring in non-visible cells.
When I worked in supporting a model for a client many of my requests were to track down and undo these entries!
Until recently we had several techniques or strategies to prevent this from occurring:
- We could disable consolidated type-in entirely
- We could use security to make consolidated cells read-only
- We could use Excel formatting in PAfE reports to indicate consolidated cells and train the users to avoid typing data in them unless they wanted a spread to occur
These approaches would work, but would either result in additional development and maintenance, or involve removing a potentially useful feature from users.
As of PAW 106 we have a new feature that can help us here: Hierarchy (or Attribute) Formatting.
This feature currently allows us to format the data and/or header cells in a cube view based on either a level or attribute of the members on the rows and columns:
In the example above we’d like to have the Full Year, Q1, and Q2 column data cells shaded grey to indicate that they are consolidated. We’ll leave the individual month cells with the white default background to let any user know they can enter data directly into those cells without spreading.
We can do this in the Format manager, which can be launched via the button on the toolbar or by right-clicking any column’s header and selecting Format manager > hierarchy:
After navigating to the Hierarchy tab, we’d select the Month dimension and hierarchy using the dropdowns:
We’d click the Add format + button and choose Level in the Type dropdown:
Note that the Level dropdown displays the levels of the selected dimension/hierarchy. This is yet another reason that naming your hierarchy levels is a great idea!
We can now select each level one at a time and apply specific formatting to each. Here we can select the All level and click Next. The first screen will allow us to determine the format of the data cells in the view. We can currently (as of September 2025, when I’m writing this) change the Fill color, numeric format, Font color, and both vertical and horizontal alignment. I’ve heard there are plans to include more options here, including Border color:
We’ll click on the Fill color selector and choose Grey 10:
I’ll note that I’d actually prefer to use Grey 20, as this is closer to the legacy cube view color, but it blends in too well with the default border color and doesn’t look great. This is why I was excited to hear that we’ll probably be able to change the border colors in the future!
When we click Next we have the option of applying formatting to the All level member Header cells as well, but for now we’ll click Apply:
At this point we could click Done and see the impact on the cube view, but we’ll click Add format + again to apply the same format to the Quarter level:
Once we’ve done that, we can see all the formats we’ve apply to the view so far. If we’d like to make a change to an existing format rule we can click the Edit button:
We can also move the rules up and down, which may matter as the rules will be applied from top to bottom. If there is a conflict between multiple rules the “highest” rule will win.
This is probably a good time to mention that if we have used a mix of Member, View, and/or Hierarchy formats then they will be applied in this order (with the earlier formats winning):
1. Member formatting
2. Hierarchy formatting for column dimensions
3. Hierarchy formatting for row dimensions
4. View formatting
I’ll also mention that as far as I can tell this will only matter if there are conflicts between the formatting rules. This is a good thing since when we return to our cube view, we can see that we’ll also need to apply some rules to the Product dimension on the rows if we want a similar look to the legacy cube viewer:
We’ll return to the Format manager’s Hierarchy tab and select the Product dimension and hierarchy before clicking Add format +:
Now the Product hierarchy has more levels than our Month hierarchy, and it wouldn’t be usual to have a hierarchy with many more levels that this (believe it or not I’ve seen a Business Unit hierarchy with 20 levels!):
We could use the exact same technique we applied earlier, and set the grey Fill color for the All, Product Line, and Product Type levels, leaving the Product level white. Another option would be to apply the grey Fill color as the Base format. This would apply to all levels, and then we could create a specific rule for the leaf level with a white background:
Here are the two rules that will provide the formatting we desire:
This would be especially good for the afore-mentioned 20-level BU hierarchy. Another option would be to leverage an Attribute formatting rule. The catch is that there is currently no built-in attribute to indicate if a level is consolidated or not, but we can certainly create one. This attribute could be populated manually, or with a cube rule to make it dynamic.
Here we’ve create a string attribute on the Product hierarchy called IsConsolidated. We’ve populated it with a “C” for every non-leaf level:
Back in the Format manager we’ll select Member attribute as the Type, then IsConsolidated as the Attribute name. We’ll then change the Operator to Equals, and type “C” in the Value field:
After clicking Next we’ll set the Fill color to the same Grey 10 as our Month rules:
Once we apply this rule we can see a cube view that should be very intuitive to users:
This should vastly reduce accidental spreading! Note that currently these formatting rules are specific to this one cube view. This gives us the flexibility to apply them only to cube views that will be used for data entry, but does mean we will have to duplicate these efforts multiple times.
I’ve heard that there are plans to allow Book authors to save formats as a named “template”, which in the future would allow them to simply select it from a list to quickly apply it to any desired views.
Even as it stands, I’m of the opinion that this is a great feature, and really useful to anyone who’d like to ensure that the data entered into their cubes ends up in the correct place.
If you like techniques like this, and if you or someone you know would like training on an up-to-date version of PAW/PAfE please check out our training offerings on our site or in IBM’s catalog:
ActionKPI’s Planning Analytics and Cognos Analytics Courses
We aim to keep our material current and will review our courses frequently to make sure we’re including new features like hierarchy and attribute formatting so our students can take advantage of them.