How to display ALL the hierarchy nodes including no data associated accounts using CDS view and Frontend tools

[former_member348895]

Building reports based on SAP “Financial Statement Versions” using CDS view is not hard. However, if we don’t have all the data corresponds to each hierarchy node, the line of that corresponding hierarchy node won’t be easily shown in the frontend. Neither using SAP Analysis for Office nor Web Intelligence as the frontend tool.

Here the author presents you one way to display all the hierarchy nodes using CDS view together with frontend tool SAP Analysis for Office or Web Intelligence.

Let’s take GAAP Financial Statement as an example, where the Balance Sheet “Financial Statement Versions” (t-code 0b58) is shown as below:



In the front end tool screenshot shown below, we use Analysis for Office version 2.4 SP3 and Web Intelligence version 4.2 for the demonstration purpose.

Assume we have some data but not full data corresponds to each hierarchy node, which likely to be a very real life business case, if we simply use traditional way to generate this hierarchy using CDS view, the hierarchy in Analysis for Office and Web Intelligence will be shown as below:

(AO)

(WEBI)

By comparing to the previous “Financial Statement Version” hierarchy, we can tell that we have two missing hierarchy node, which are “Total Long-Term Debt” and “Total Deferred Credits and Other Liabilities”, the reason behind this is because in our system, we have not stored any data under these two nodes, in other words, the G/L Accounts associated with these two nodes have no data.

A simple workaround for this is to trace back our CDS view to the bottom level, where in this case, we create a Master view to select the G/L accounts directly from the I_GLAccountInChartOfAccounts SAP predefined composite view with only the selection of GLAccount:



Other annotations follow the same as you did in a traditional way. After you run this view in Analysis for Office and Web Intelligence, you can get the full hierarchy without any data:

(AO)

(WEBI)

Now our last task is to do the combination for both cases.

In Analysis for Office, insert two queries into one tab, then click the one with the Master hierarchy display, which in this case is the second full hierarchy you see without data. In the end, do the following to combine the two queries:

(AO)

After this, you will see the full “Financial Statement Versions” hierarchy node either with or without corresponding data from Analysis for Office like below:

(AO)

In Web Intelligence, you don’t have to insert two queries into one report. For example, after adding the second query, say Master data query, you should first click Query 2 (full hierarchy query) and then click Query 1 (hierarchy query with data), and merge the two queries like below:

(WEBI)

Then drag the merged Query 2 to the first column of incomplete hierarchy like this:

(WEBI)

After the replacement, you will see the full “Financial Statement Versions” hierarchy node either with or without corresponding data from Web Intelligence as below:

(WEBI)

By implementing this, it would allow each company to see the full “Financial Statement Versions” defined in the backend from frontend tools, e.g. Analysis for Office or Web Intelligence, we don’t need to worry about any loss of the nodes either with or without the corresponding data in the system. Meanwhile, this methodology is also an alternative to SAP BEx Query Designer, which has the functionality of selecting master data from the user interface panel.