Summary:

Hierarchies are one of the most important elements in SAP BW which provides the Business users flexibility to navigate the hierarchy structure in the Frontend Reports. At times, a need arises where we need to flatten the hierarchy structure because of the business requirement in order to display only certain levels of hierarchy in the report without displaying the whole hierarchy structure, there could be a number of reasons where we need to flatten the hierarchy.

Below is the simple Plant hierarchy structure which consists of 5 levels: Group, Region, Country, City/County, and Plant.

The following graphic gives an example of a hierarchy for the InfoObject ZZPlant displaying all the levels with entities and relation.

Steps to flatten the hierarchy using CDS Views are as follows.

We need to create multiple CDS Views in order to achieve the desired outcome.

CDS View 1:

Define a CDS View on hierarchy table and restrict it to get the Top Level so that we have only the Root in the result.

If there are multiple Hierarchy maintained for the InfoObject, restrict it to the relevant Hierarchy ID in the CDS view for which we require desired results.

CDS View 2:

Define the next CDS view on the same hierarchy table deriving all the levels excluding the Top Level.

CDS View 3:

In order to achieve the outcome, in this CDS View, we will join both CDS View 1 and CDS View 2 using nested joins between Node ID and Parent ID using the Parent Child relationship to extract all levels with flattened structure.

In this example, I have flattened up to level 5, but this can be extended even further based on the particular business requirement.

The following graphic displays the Flattened structure of the hierarchy.

In certain scenarios, the Hierarchy would not have all levels populated, for example in a Cost Centre Hierarchy a Cost Centre can be populated under Level 3 or Level 4 or other Levels. To cater this, a case statement can be written to populate the values in a new field regardless of what level the Cost Centre exist.

Conclusion:

Once we have the flattened hierarchy structure, it can then be either loaded into InfoProvider or could be consumed in Open ODS and then into Composite Provider (Virtual layer) which can be consumed in frontend tools. We can join the the transaction data with this Flattened structure on the basis of Plant and derive the levels accordingly in the report.

Note:

The Annotations defined in the above CDS Views are standard system generated. Based on the business requirement, other annotations can be added.

Please feel free to share your comments and feedback.