CDS Analytical Query: How to use profit center hierarchy?

[MioYasutake]

Hi community,

I am developing a CDS View (analytical query) on S/4HANA 2020 FPS01.

We have a requirement to show profit center hierarchy ID as a filter criteria on a multidimensional report.

For G/L Account Hierarchy, it is achieved by the following annotations. (reference: C_Trialbalanceq0001)

  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [{type : #USER_INPUT, value : 'P_GLAccountHierarchyName', variableSequence: 31 } ] }
  @EndUserText.label: 'G/L Account Hierarchy'
  GLAccountHierarchy,<br>

To achieve the same for profit center, I have added the following annotation, but ADT shows an error.

  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [{type : #USER_INPUT, value : 'P_ProfitCenterHierarchyName', variableSequence: 41 } ] }
  @EndUserText.label: 'G/L Account Hierarchy'
  ProfitCenter,
  ControllingArea,<br>

Error: Inconsistent hierarchy binding: 2 entries required, 1 entries found [Analytics]

It looks like profit center requires controlling area, but how can I bind it to the hierarchy?

The entire view source is below.

@AbapCatalog.sqlViewName: 'ZCTRIALBALANCEQ'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Trial Balance Query'
@Analytics.query: true
define view ZC_TrialBalenceQuery
  with parameters
    @Consumption.hidden: true
    @Environment.systemField: #SYSTEM_LANGUAGE
    P_Language        : sylangu,
    @Consumption.hidden: true
    @Environment.systemField: #SYSTEM_DATE
    P_KeyDate         : sydate,
    @Consumption.derivation: { lookupEntity: 'I_CalendarDate',
        resultElement: 'FirstDayofMonthDate',
        binding: [
        { targetElement : 'CalendarDate' , type : #PARAMETER, value : 'P_ToPostingDate' } ]
       }
    P_FromPostingDate : fis_budat_from,
    @Consumption.derivation: { lookupEntity: 'I_MySessionContext',
        resultElement: 'UserLocalDate', binding: [
        { targetElement : 'UserID' , type : #SYSTEM_FIELD, value : '#USER' } ]
       }
    P_ToPostingDate   : fis_budat_to


  as select from I_GLAcctBalanceCube
                 ( P_FromPostingDate: $parameters.P_FromPostingDate, P_ToPostingDate: $parameters.P_ToPostingDate ) as I_GLAcctBalanceCube

{
  @Consumption.filter: {selectionType: #SINGLE, multipleSelections: false, mandatory: true}
  @Consumption.derivation: { lookupEntity: 'I_LedgerStdVH',
        resultElement: 'Ledger', binding: [
        { targetElement : 'IsLeadingLedger' , type : #CONSTANT, value : 'X' } ]
       }
  @AnalyticsDetails.query.variableSequence : 10
  @AnalyticsDetails.query.axis: #FREE
  Ledger,
  @Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: true}
  @AnalyticsDetails.query.variableSequence : 20
  @AnalyticsDetails.query.axis: #ROWS
  @AnalyticsDetails.query.totals: #SHOW
  CompanyCode,
  @Consumption.filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false}
  @AnalyticsDetails.query.variableSequence : 30
  @AnalyticsDetails.query.axis: #ROWS
  @AnalyticsDetails.query.totals: #SHOW
  @AnalyticsDetails.query.display: #KEY_TEXT
  GLAccount,

  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [{type : #USER_INPUT, value : 'P_GLAccountHierarchyName', variableSequence: 31 } ] }
  @EndUserText.label: 'G/L Account Hierarchy'
  GLAccountHierarchy,

  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [{type : #USER_INPUT, value : 'P_ProfitCenterHierarchyName', variableSequence: 41 } ] }
  @EndUserText.label: 'G/L Account Hierarchy'
  ProfitCenter,
  ControllingArea,


  _ProfitCenter[1:ValidityEndDate   >= $parameters.P_KeyDate and
                  ValidityStartDate <= $parameters.P_KeyDate]._Text[1:Language = $parameters.P_Language].ProfitCenterName,
  @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
  AmountInCompanyCodeCurrency,
  CompanyCodeCurrency
}

Best regards,Mio

[MioYasutake · Accepted solution]

With the following definition I was able to activate the view.

  @Consumption.filter: {  selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
                          hierarchyBinding : [ {type : #USER_INPUT, value : 'P_ControllingArea',variableSequence: 59},
 {type : #USER_INPUT, value : 'P_ProfitCenterHierarchyName', variableSequence: 60 } ] }
  @EndUserText.label: 'Profit Center Hierarchy' 
  ProfitCenterHierarchy,

As I_ProfitcenterHierarchy requires two keys (ControllingArea and ProfitCenterHierarchy), we must provide those keys in hierarchy binding. This seems to have been enforced since 2020 FPS01.

[igor_sangulin]

Hi Mio,

you need to expose Profit Center hierarchy inside cube view. I would create Z CDS cube (you can copy it from SAP cube), expose PC hierarchy there and then use it in your Z CDS query.

I created 2 sample views, you can see them here:

CDS Cube

CDS query

On my S/4 Demo21 2020 prompt looks like this:

Regards, Igor

[MioYasutake]

Hi Igor,

Thank you very much for your response and also providing the code!

I will try with this and update here.

[MioYasutake]

Hi Igor,

I have implemented exactly the same CDS views as you shared.

But unfortunatelly, the issue still persists. I'm going to raise an incident.

Could you let me know your SP level? (ours is S/4HANA 2020 FPS01)

Regards,

Mio

[MioYasutake]

Thank you for your quick response!