Hierarchy Label of filter in ABAP CDS analytical query

This post describes how to change the label of hierarchy parameter in the filter of analytical report in S/4 HANA.

Background

In S/4 HANA embedded analytics, you can use analytical CDS view to create a multi-dimensional report. One of the feature is allowing hierarchy of a dimension and filtering in the prompt screen. For example, to setup a Profit Center hierarchy, you need to define the hierarchy name, which Controlling Area it belongs to, and finally the hierarchy structure. This can be done through the old way KCH1 or with Fiori app ‘Manage Global Hierarchy’.

After the hierarchy setup is done, analytical CDS view can display the profit center data as an hierarchy.

The frequently requested requirement is that user wants to filter the data using profit center hierarchy, requiring only the data under certain profit center node.

Since S/4 allows you to create multiple profit center hierarchies under multiple controlling areas, the report must determine which hierarchy to show in the filter. To do this, the user must enter the controlling area and hierarchy name this hierarchy structure belongs so the system can determine the profit hierarchy structure. That is why these two extra filters are always needed if you want to enable hierarchy in the filter.

To add these extra filters, you must add hierarchy biding in the CDS query.

    @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.display: #KEY_TEXT
  @AnalyticsDetails.query.displayHierarchy: #FILTER
  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [{type : #USER_INPUT, value : 'ControllingArea', variableSequence: 10},
                                                {type : #USER_INPUT, value : 'ProfitCenterHierarchyName', variableSequence: 11 }
                                               ] }  
  ProfitCenter

Problem statement

Having understood how the hierarchy works in analytical query filter, the problem statement is that how can we change the label of these two fields shown as filters?

These two fields are automatically generated just with ‘hierarchybinding’ annotations and set type = #USER_INPUT. The value parameter in ‘hierarchybinding’ is just a technical name and cannot be used as label. There is no more annotation field available in ‘hierarchybinding’ other than these three, where do the labels come from?

After a painstaking effort, I finally found that they simply come from data element inside CDS hierarchy view associated in CDS cube. Let me show you what I mean and let’s try to change the labels with below exercise.

Excersie to see where the labels come from

To reproduce the report as shown above, let’s create a custom CDS cube and query.

Y_GLITEM_HIER_CUBE(Cube)

@Analytics: { dataCategory: #CUBE }
@EndUserText.label: 'G/L Account Line Item - Cube'
@ObjectModel: { representativeKey: 'LedgerGLLineItem',
                usageType: { sizeCategory: #XXL,
                             dataClass:  #MIXED,
                             serviceQuality: #D },
                supportedCapabilities: [#ANALYTICAL_PROVIDER, #SQL_DATA_SOURCE, #CDS_MODELING_DATA_SOURCE],
                modelingPattern: #ANALYTICAL_CUBE }
@VDM.viewType: #COMPOSITE

define view entity Y_GLITEM_HIER_CUBE

  as select from I_GLAccountLineItem as I_GLAccountLineItem

  association [0..*] to I_ProfitCenter as _ProfitCenterHier on  $projection.ControllingArea = _ProfitCenterHier.ControllingArea
                                                            and $projection.ProfitCenter    = _ProfitCenterHier.ProfitCenter

{

      @ObjectModel.foreignKey.association: '_Ledger'
  key Ledger,
      @ObjectModel.foreignKey.association: '_SourceLedger'
  key SourceLedger,
      @ObjectModel.foreignKey.association: '_CompanyCode'
  key CompanyCode,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key cast( FiscalYear as fis_gjahr_no_conv preserving type ) as FiscalYear,
      @ObjectModel.foreignKey.association: '_JournalEntry'
  key AccountingDocument,
  key LedgerGLLineItem,

      @ObjectModel.foreignKey.association: '_ProfitCenterHier'
      ProfitCenter,

      @ObjectModel.foreignKey.association: '_ControllingArea'
      ControllingArea,
      _Ledger,
      _SourceLedger,
      _CompanyCode,
      _FiscalYear,
      _JournalEntry,
      _ControllingArea,
      _ProfitCenterHier


}

YTESTHIER_QUERY(Query)

@EndUserText.label: 'Test Query for hierarchy'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
define view entity YTESTHIER_QUERY
  as select from Y_GLITEM_HIER_CUBE
{

  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.display: #KEY_TEXT
  Ledger,

  @AnalyticsDetails.query.axis: #ROWS
  @AnalyticsDetails.query.totals: #SHOW
  @AnalyticsDetails.query.display: #KEY_TEXT
  CompanyCode,

  @AnalyticsDetails.query.axis: #FREE
  AccountingDocument,

  @AnalyticsDetails.query.variableSequence : 12
  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.display: #KEY_TEXT
//  @EndUserText.label: 'Custom Profit Center'
  @AnalyticsDetails.query.displayHierarchy: #FILTER
  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [{type : #USER_INPUT, value : 'ControllingArea', variableSequence: 10},
                                                {type : #USER_INPUT, value : 'ProfitCenterHierarchyName', variableSequence: 11 }
                                               ] }  
  ProfitCenter

};

Go to transaction code RSRT and enter ‘2CYTESTHIER_QUERY’, choose WD Grid and execute. You will be able to see the three filters as shown in the beginning of the blog.

Now let’s change the label of all three fields.

First we will change the easiest one, which is ‘Profit Center(Node)’. The (Node) keyword cannot be changed, but Profit Center can be changed by explicitly specifying the label in CDS query.

  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.display: #KEY_TEXT
  @EndUserText.label: 'Custom Profit Center'
  @AnalyticsDetails.query.displayHierarchy: #FILTER
  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [{type : #USER_INPUT, value : 'ControllingArea', variableSequence: 10},
                                                {type : #USER_INPUT, value : 'ProfitCenterHierarchyName', variableSequence: 11 }
                                               ] }  
  ProfitCenter

Now let’s change the labels for controlling area and profit center hierarchy. The texts are actually coming from I_ProfitCenter view associated in CDS cube. To be more specific, there is I_ProfitCenterHierarchyNode that contains hierarchy structure data and inside that view is I_ProfitCenterHierarchy which stores the hierarchy name in hierarchy dictionary. The texts are coming from data element from I_ProfitCenterHierarchy used for ControllingArea and ProfitCenterHierarchy fields.

Now this is clear, we need to create a custom version of I_ProfitCenter and data elements with custom texts. Go to SE11 or in ADT and create a copy of fis_kokrs and fis_hryid_prctr and change the texts to your own.

Next create a copy of I_PROFITCENTERHIERARCHY with name YI_PROFITCENTERHIERARCHY and use the custom data element for ControllingArea and ProfitCenterHierarchy.

      @ObjectModel.foreignKey.association: '_ControllingArea'
  key cast( hrrp_directory.hrycls as yfis_kokrs )     as ControllingArea,
      @ObjectModel.text.association: '_Text'
  key cast(hrrp_directory.hryid as yfis_hryid_prctr ) as ProfitCenterHierarchy,

Then create a copy of I_PROFITCENTERHIERARCHYNODE with name YI_PROFITCENTERHIERARCHYNODE and use YI_PROFITCENTERHIERARCHY instead of I_PROFITCENTERHIERARCHY. 

  association [1..1] to YI_PROFITCENTERHIERARCHY as _Hierarchy           on  $projection.ProfitCenterHierarchy = _Hierarchy.ProfitCenterHierarchy
                                                                         and $projection.ControllingArea       = _Hierarchy.ControllingArea
                                                                         and $projection.ValidityEndDate       = _Hierarchy.ValidityEndDate

Finally create a copy of I_PROFITCENTER with name YI_PROFITCENTER and use YI_PROFITCENTERHIERARCHYNODE as association instead of I_PROFITCENTERHIERARCHYNODE. 

  association [0..*] to YI_PROFITCENTERHIERARCHYNODE    as _ProfitCenterHierarchyNode    on  $projection.ControllingArea = _ProfitCenterHierarchyNode.ControllingArea
                                                                                        and $projection.ProfitCenter    = _ProfitCenterHierarchyNode.ProfitCenter

After this make sure to go back to I_PROFITCENTERHIERARCHYNODE and change the association of I_PROFITCENTER to YI_PROFITCENTER.

  association [0..*] to YI_PROFITCENTER          as _ProfitCenter        on  $projection.ProfitCenter    = _ProfitCenter.ProfitCenter
                                                                         and $projection.ControllingArea = _ProfitCenter.ControllingArea

Now our custom profit center association view is complete and what’s left is to use YI_PROFITCENTER in the CDS cube view, instead of I_PROFITCENTER.

  association [0..*] to YI_PROFITCENTER as _ProfitCenterHier on  $projection.ControllingArea = _ProfitCenterHier.ControllingArea
//  association [0..*] to I_ProfitCenter as _ProfitCenterHier on  $projection.ControllingArea = _ProfitCenterHier.ControllingArea
                                                            and $projection.ProfitCenter    = _ProfitCenterHier.ProfitCenter

If you go to RSRT and preview the CDS query 2CYTESTHIER_QUERY, you should be able to see the labels change to your custom texts defined in the data elements.

By ABAP, CDS Query app, CDS query(Embedded Analytics) based standard report