How to add Hierarchies into Analytical Queries

[ondrakfi]

Update October 2024:

Using Cost Center Hierarchy was a highly requested feature, so it was made as a correction to the standard CDS Views C_CostCenterQ2001 and C_CostCenterPlanActQ2001. You can find the correction under SAP Note 3362694. There are multiple prerequisties, so please go through them.

Additionally, our CDS Views did not support Metadata extensions. This limitation was enabled by SAP Note 3509451.

I mention this here in the article about CDS Views because Metadata extensions can be utilized to transform fields into hierarchies by annotations.

In the example provided, Profit Center was used as an example field because it does not have a hierarchy and may be necessary in certain scenarios.

@Metadata.layer: #CUSTOMER
annotate entity ZF_PROFITCENTERPLANACTQ2701
    with 
{
  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [    {type : #PARAMETER, value : 'P_ControllingArea'}, 
                                              {type: #USER_INPUT, value: 'ProfitCenterHierachy', variableSequence: 48 }
                                          ] }
    ProfitCenter;
    
}

As this is bound to the Profit Center, you do not need to do any change to the WebDynpro Configuration.

Before the change of standard code:

In SAP Delivered CDS View for Cost Centers - Actuals/ Cost Centers - Plan/Actual, or even your own custom-created CDS View, you might need to have Cost Center Hierarchy and Cost Center Node on the selection screen within WebDynpro version of the app. You can select Cost Center Hierarchy as an additional filter, but it will not be visible among other variables.

In order to be able to see Cost Center Hierarchy on the selection screen, you will need to create your own CDS View, as you cannot modify the SAP Delivered one. You can create a copy of the CDS View C_COSTCENTERQ2001, which has the Cost Center Hierarchy already in the code, but it is commented out by default.

You will find this code under the Rows characteristic:

@AnalyticsDetails.query.axis: #ROWS  

IsStatisticalCostCenter,  

 

@AnalyticsDetails.query.variableSequence: 50

@Consumption.filter: { selectionType: #INTERVAL, multipleSelections: true, mandatory: false }

@AnalyticsDetails.query.totals: #SHOW

@AnalyticsDetails.query.axis: #ROWS

@AnalyticsDetails.query.display: #KEY_TEXT

CostCenter,

 

//@AnalyticsDetails.query.variableSequence: 81

//@Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,

//                        hierarchyBinding : [    {type: #USER_INPUT, value: 'CostCenterHierarchy', variableSequence: 80 } ] }

//@AnalyticsDetails.query.displayHierarchy: #FILTER

//@AnalyticsDetails.query.totals: #SHOW

//@AnalyticsDetails.query.axis: #ROWS

//@AnalyticsDetails.query.display: #KEY_TEXT

//CostCenter as CostCenterChildNode,

//_CostCenter[1:ValidityEndDate   >= $parameters.P_KeyDate and 

//                    ValidityStartDate <= $parameters.P_KeyDate]._Text[1:Language = $parameters.P_Language].CostCenterName as CostCenterChildNodeName,

 

 

@AnalyticsDetails.query.variableSequence: 86

@Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,

                                      hierarchyBinding : [  { type: #USER_INPUT, value: 'GLAccountHierarchy', variableSequence: 85 } ] }

@AnalyticsDetails.query.displayHierarchy: #FILTER

@AnalyticsDetails.query.axis: #ROWS

@AnalyticsDetails.query.totals: #SHOW

GLAccount,

_GLAccountInChartOfAccounts._Text[1:Language = $parameters.P_Language].GLAccountName,

When you create a copy of this view, or you will use your own, you will need to replace this part of the code for this one:

@AnalyticsDetails.query.axis: #ROWS  

IsStatisticalCostCenter,  

 

 

@AnalyticsDetails.query.variableSequence: 86

@Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,

                        hierarchyBinding : [    {type : #PARAMETER, value : 'P_ControllingArea'}, {type: #USER_INPUT, value: 'CostCenterHierarchy', variableSequence: 85 }

                                            ] }

@AnalyticsDetails.query.displayHierarchy: #FILTER

//@Consumption.filter: { selectionType: #INTERVAL, multipleSelections: true, mandatory: false }

@AnalyticsDetails.query.totals: #SHOW

@AnalyticsDetails.query.axis: #ROWS

CostCenter,

_CostCenter[1:ValidityEndDate   >= $parameters.P_KeyDate and 

                    ValidityStartDate <= $parameters.P_KeyDate]._Text[1:Language = $parameters.P_Language].CostCenterName,

 

@AnalyticsDetails.query.variableSequence: 96

@Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,

                                      hierarchyBinding : [  { type: #USER_INPUT, value: 'GLAccountHierarchy', variableSequence: 95 } ] }

@AnalyticsDetails.query.displayHierarchy: #FILTER

//@Consumption.filter: { selectionType: #INTERVAL, multipleSelections: true, mandatory: false }

@AnalyticsDetails.query.axis: #ROWS

@AnalyticsDetails.query.totals: #SHOW

GLAccount,

_GLAccountInChartOfAccounts._Text[1:Language = $parameters.P_Language].GLAccountName,

As you can see in the changed code, the part with a single Cost Center was removed completely, and the  Cost Center Hierarchy with Cost Center Node took its place. In the Cost Center Hierarchy and GL Account Hierarchy, there is a line:

//@Consumption.filter: { selectionType: #INTERVAL, multipleSelections: true, mandatory: false }

This line is there in case, you would like to select it not as a Hierarchy and Node, but just a single selection of Cost Center or GL Account. If you would like to have a single GL Account for selection, you need to uncomment this line and comment these two lines in the code for GL Account:

@Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,

                                      hierarchyBinding : [  { type: #USER_INPUT, value: 'GLAccountHierarchy', variableSequence: 95 } ] }

@AnalyticsDetails.query.displayHierarchy: #FILTER

These two lines for Cost Center:

@Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,

                        hierarchyBinding : [    {type : #PARAMETER, value : 'P_ControllingArea'}, {type: #USER_INPUT, value: 'CostCenterHierarchy', variableSequence: 85 }

                                            ] }

@AnalyticsDetails.query.displayHierarchy: #FILTER

At the end, you will need to create your own Fiori Tile with this CDS View in it, to be able to run it in Fiori Launchpad.

The result will look like this in Fiori.

Cost Centers - Actuals with Hierarchy

Please share your feedback or thoughts in the comment section.

Follow me for more tips about BW or BPC in general, so I can create more post about these topics.

You can also follow BW Planning, post and answer questions (https://answers.sap.com/tags/369280904654660339247670897548678), and read other posts on the topic (https://blogs.sap.com/tags/369280904654660339247670897548678/) for more content.

You might suggest some topics you would like to know more about in my DMs as well, so I know on which topics I should focus.