Aggregation of text and images in Hierarchy Charts
When Hierarchy charts were introduced to the Anaplan UX, their popularity grew very quickly. The power of the intuitive visual representation of business hierarchies, and the simplicity of setup, combined to fuel substantial adoption by hundreds of our customers.
At its’ simplest, the structure of a hierarchy can be displayed as shown….
Here we have a Parent Company with subsidiaries in the UK, France, and Germany. The hierarchy is ragged in nature because the UK is further broken down into the countries in which the company operates, namely England, Scotland, and Wales, whereas France and Germany are not split further. Let’s first assume that this is represented in Anaplan by a single list (called Ragged Hierarchy). We will come to composite hierarchies later.
We have the ability to add a numeric line item to the chart, in this case, Revenue.
This line item aggregates as expected with the Summary Option set to Sum.
So far so good, but what if we want to add a unique image to each node, such as the flag of each country in the hierarchy? To do this, we need to store the URLs to the images in a line item like this one:
URLs, being text-formatted items, can naturally be stored at leaf level as shown. Assigning this to the hierarchy chart then produces this:
All very smart, except that we have no images for the UK or the Parent Company, because our summary option is set to None. If we look at our aggregation options, there are none which would allow for a unique image for the parent nodes. For example, Last non Blank is a common summary option for text, but here it would produce this:
Clearly, this is not what we want, because the UK ends up with the Welsh flag, and the Parent Company with the German one. So how can we model a line item to populate the parent nodes of the hierarchy with their own unique image, or indeed any text or list formatted item?
The answer is first to store the unique URLs for the images against a flattened version of the items in the hierarchy, rather than the hierarchy itself as above.
Our challenge now is to apply these URLs to our hierarchy list, and to do that we need to find a way to identify the levels of each node, from leaf to top level. This example involves a shallow hierarchy of just three levels. Follow the logic to add more levels if required.
This module is dimensioned by Ragged Hierarchy.
The parent-child relationships are set out in the first 3 line items, followed by their codes. The formula for both Max Level and #Levels is the same, as follows:
The only difference is that Max Level has a Summary Option of Max, whereas #Levels has a Summary Option of Formula (along with all the other line items shown above), giving different results at parent levels. The Effective Level calculation then compares the difference between them, relative to the overall depth of the hierarchy (3 in this case). The result is a number whereby one means top level, two means intermediate parent, and three means leaf. This line item therefore defines the level of each item in the ragged hierarchy, which we can now exploit.
The Ragged Hierarchy Calc module is extended as follows:
To forge a link between the Flat Hierarchy list and the Ragged Hierarchy list, we use the same codes for the items within each. We can then use FINDITEM formula to populate the three line items (Flat L3, Flat L2, and Flat L1), each formatted on the Flat Hierarchy.
These line items are then used as lookups from the module SYS01 Flat Ragged.
The key line items for us here are Image, Manager, and Role. Manager is text formatted, and Role is list formatted, and so have the same aggregation problem as images when applied to a hierarchy, as can be seen here when added to the chart:
You will see three sets of four line items in the Ragged Hierarchy Calc module, one each for the Manager, Role, and Image. The first 3 line items per set pull the relevant item from the SYS 01 Flat Ragged module, and lookup the associated flat item depending on the level.
For example:
Image L3='SYS01 Flat Ragged'.Image[LOOKUP: 'Flat L3']
Image L2='SYS01 Flat Ragged'.Image[LOOKUP: 'Flat L2']
Setting the summary option to Last Non Blank populates the parents with the same items as the nodes.
The fourth line item per set uses the Effective Level line item detailed above, to pull the correct calculation from the three preceding line items:
For example:
So for Manager, we end up with this view:
Choosing these “overall x” line items in the hierarchy chart settings now yields this result:
And so finally, after a little modeling, we have what we want! Each node has a unique flag and Manager, and the Role is correctly represented too.
I said I’d come back to composite hierarchies, where we have multiple lists, each containing items of a certain level of a normalized hierarchy. Using our example above, we would typically see a representation like this:
The leaf level is designated with an L3 suffix, intermediate parent by L2 and top level by L1. The main difference to the single ragged version is that France and Germany have repeating “dummy” nodes, so that they have items at the lowest level.
To do our hierarchy chart, we can use exactly the same technique as above, except that the flattened version of the composite hierarchy will have the additional dummy nodes for France and Germany.
For example:
The only further issue that you may encounter, is that you may not want the dummy nodes to appear on the chart like they do here:
We can deal with this by creating a filter to exclude the dummy nodes. In this example, you can see a line item called Name in the SYS02 Flat Composite module. By comparing the names of children to their parents, and determining which parents only have one child, you can construct a filter called Dummy Node? which can then be applied in the custom view foe your hierarchy chart:
You can see here that France Level 2 and Germany Level 2 are ticked, and so we can eliminate them from our chart to produce this result:
I hope you found this useful. Any comments or questions will be very welcome.
…….
Author: Andrew Martin, Operational Excellence Director at Anaplan.