Maintain Hierarchies from Standard CDS views in S4HANA Cloud

[DanielleBeeken]

Background:

GLAccount Hierarchies are probably the most common requirements in our Analytics space as this gives insight on high-level overview with the option to drill into the lower hierarchy branches and nodes.

There are many platforms across SAP forums detailing the significance and the need to represent Hierarchy in CDS views. There are also some interesting Blogs that emphasize why we need them to further analyze data.

A hierarchy is usually not defined on the facts themselves but on some master data column that categorizes the facts. This master data entity, or any business entity that should be arranged in a hierarchy, is given by a CDS view of analytical data category DIMENSION.

In this blog, I would like to give some insight on how we could maintain Hierarchies in S4HANA Cloud while building custom CDS views from scratch using standard CDS views and later building a Custom Analytical Query (for sake of simplicity, I will mention it as Query going forward) to display the hierarchies in your Query.

In my demonstration, I have used the data source: I_ACTUALPLANJOURNALENTRYITEM as my example to create GLAccount Hierarchies.

The steps are as follows:

1. Create a new Custom CDS View based on I_ACTAULPLNJOURNALENTRYITEM and I named my custom view: YY1_ACDOCAP_Test_Amith

2. Select the appropriate fields of your choice and as per functional requirement.

Note: Please select Analytical as your scenario since our intention is to build a Query on top of custom CDS view as depicted in fig 1

Fig 1:

Please make sure to model your custom CDS view in accordance with the guidelines shared by our Developers as per the KBA 2657021

3. Add the ChartOfAccounts field, and the 2 Associations, _GLACCOUNTINCHARTOFACCOUNTS, and _PROFITCENTER

Fig 2:

Note:

Since, our intention is to create GL Account Hierarchy, it is imperative to add the Association _GLACCOUNTINCHARTOFACCOUNTS and with this Association we need the following fields- ChartOfAccounts, Controlling Area, ProfitCenter and GLAccount that are intrinsically connected.

If one of the fields mentioned below are missing, you will encounter an error that says: JOURNALENTRY._JOURNALENTRYITEM.GLACCOUNT" must be included together with "_GLAccountInChartOfAccounts" in the selection list

(In this case GLACCOUNT was not added under JournalEntryItem)

Fig 3

Hence, here are the consolidated list of fields to be incorporated for GLAccount Hierarchy

Fig 4

4. Now, you will need to go to the Field Properties section of the CDS View and add _GLACCOUNTINCHARTOFACCOUNTS as the Master Data View for GLAccount, and _PROFITCENTER as the Master Data View for ProfitCenter as depicted in Fig 5 and Fig 6

Fig 5

Fig 6

5. Save and Publish your custom CDS view.

Now, we will go ahead with the creation of Query

It might look repetitive to you if I walk through on how to create custom Analytical Queries as this can be found in many platform and one such platform is in YouTube itself presented by a SAP source .

1. In our use case, I will use the data source YY1_ACDOCAP_Test_Amith and name the query as YY1_ACDOCAP_TEST_Query

2. Select the appropriate fields as needed to build your query(from your custom CDS view that was developed under the Field selection tab.

3. Go to the Display section and pick the field GLAccount, now, here you will find two GL Accounts as you observed in your custom CDS view, the one from the Association and one from the main entity. To distinguish between the two, I labeled the one from association as GL Account Hierarchy and then check marked Hierarchy and filter Hierarchy ID from the value help as depicted in fig.7

Fig 7

Repeat the same for your Profit center as well to have Profit Center Hierarchy reflect in your final Query report.

As a side note, if we don’t select the Hierarchy ID from the value help, we run into errors and result will not get previewed and our wise SAP Development Support Team have even documented a KBA article on the same. KBA note 2592697

The type of Errors encountered are:

Could not instantiate data source "DS_1" for query "2CYY9_ACDOCAP__1".Could not instantiate data source "DS_1" for query "2CYY9_ACDOCAP__1". BRAIN(635): Query 2CYY9_ACDOCAP__1 could not be opened.

4. Save the draft and you could preview to see the result

Hope this document helps!

Thank you!