With the introduction of the new Value Driver Tree widget, SAP has introduced an Excel template for the quick creation of value driver trees in SAP Analytics Cloud (SAC). However, the same approach can also be used to easily build structured hierarchies. At the same time, unique IDs are also created.
Since SAC aims to enable the business department to maintain master data and hierarchies independently, these are important criteria. This allows users to create graphical representations of ordered groupings on their own. An example of a hierarchical representation compared to a list representation can be found in the image below.
The presented template is therefore a very useful aid. In this article we will explain the structure of the template step by step, so that you can make your own adjustments if necessary.
The first columns of the Excel spreadsheet are used to represent the levels of the hierarchy. You can enter a two-digit number (e.g. 10, 20 or 11, 12 etc.) per row and column. If a hierarchy level is not required, you do not need to enter anything.
The values entered per level are concatenated using an Excel formula to generate a combined level.
In the next step, the combined level is filled with zeros until the length of 30 characters is reached. This ensures a unique ID.
After the automatically generated ID, you just have to provide a description.
Next, the level of the characteristic value is determined. This is required to determine the parent node later on. Since the levels of the individual value were always defined using a two-digit number, the hierarchy level can be calculated by dividing the length of the combined level by two. For example, it is clear that the combined level "10" represents the first hierarchy level, while the combined level "1010" represents the second hierarchy level.
Finally, we determine the parent node. This is entered in the hierarchy definition in SAP Analytics Cloud. In the case of the first level, it is clear that this is the top node. In all other cases, we find out the top node by taking the number of characters corresponding to the level from the generated ID in the Q column.
Since we always use two characters to define the level, we can use the following formula: (level - 1 ) * 2
For example:
Level 2 - 1 = 1 * 2 = 2 characters → 10 corresponds to the parent level
Level 3 - 1 = 2 * 2 = 4 characters → 1010 corresponds to the parent level
Level 4 - 1 = 3 * 2 = 6 characters → 101010 corresponds to the parent level
Therefore, we take from the second level the first two characters (10) and the rest is filled with zeros (1000000000000000000000). This way we identify the parent node.
The next column checks whether the value is a hierarchy node or a leaf. Using the COUNTIF formula, we check whether the ID appears in the "Parent" column. If an ID is not found, it is not a node for other values, but a leaf.
Last but not least, other attributes of the characteristic, such as currency and person responsible, can be entered.
After the input has been completed, the second sheet of the workbook can be used. This worksheet puts all attributes in the order expected by SAP Analytics Cloud.
This way, all the values can be easily copied and pasted into SAP Analytics Cloud.
Hierarchies are an essential topic in reporting. To make reports more flexible, it is a good idea to use hierarchy nodes instead of individual values. If, for example, new values are added within the node, the changes are automatically reflected in the reports.
The prerequisite for this is that the hierarchy is consistent and easy to maintain. With the template presented, you have a tool at hand that greatly simplifies the creation of hierarchies. This means that users from the business department can also create hierarchies that cover their reporting requirements independently and without support from the IT department.
Do you have questions about this or other topics? Are you trying to build up the necessary know-how in your department or do you need support with a specific question? We will be happy to help you. Request a non-binding consulting offer today.