Skip to Content
0
5 days ago

CDS Analytical Query: How to use profit center hierarchy?

69 Views Last edit 5 days ago 2 rev

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.

samplequery.png

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]

inconsistency.png

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

Attachments

samplequery.png (155.7 kB)
inconsistency.png (10.7 kB)