What Annotations are needed in a CDS Cube to setup up a Hierarchy ?

[Martin_4]

Hi,

I need the hierarchy representation of the GL Accounts in a a query on top of a Cube based on I_JournalEntryItemCube.

Cube ZCACCOUNTHIER joins to I_GLAccountHierarchyNode and I_GLAccountHierarchyNodeT

Query ZCACCOUNTHIER sits on top of ZCACCOUNTHIER

What are the necessary Hierarchie Annotations to get this thing run ?

Are there a Standard CDS available showing this ?

ThanXs a lot .

Best Martin

----------------------------------------------------------------------------------------------------------------

This is the Cube ZCACCOUNTHIER , slimmed out to whats minimum needed:
Parameters are replaced in general by constants.

@Analytics.dataCategory:                #CUBE
@AccessControl.authorizationCheck:      #NOT_REQUIRED
@EndUserText.label:                     'Consumption View GL Account Hierarchy Implementation'
@AbapCatalog.sqlViewName:               'ZVACCOUNTHIER'

define view                             ZCACCOUNTHIER
with parameters
P_Ledger           : abap.char(2), // For documentation purpose only - Constant in the WHERE-clause
P_LedgerFiscalYear : abap.numc(4), // For documentation purpose only - Constant in the WHERE-clause
P_KeyDate          : abap.dats   , // For documentation purpose only - Constant in the WHERE-clause
P_ValidityEndDate  : abap.dats 
as select from I_JournalEntryItemCube             
as _JournalEntryItemCube


association[0..1] to I_GLAccountHierarchyNode   as _GLAccountHierarchyNode
on  _GLAccountHierarchyNode.GLAccount               = _JournalEntryItemCube.GLAccount
and _GLAccountHierarchyNode.GLAccountHierarchy      = 'INT'

association[0..1] to I_GLAccountHierarchyNodeT  as _GLAccountHierarchyNodeText
on  _GLAccountHierarchyNodeText.GLAccountHierarchy  = 'INT'
and _GLAccountHierarchyNodeText.HierarchyNode       = _GLAccountHierarchyNode.HierarchyNode
and _GLAccountHierarchyNodeText.ValidityEndDate     =  '99991312' 
and _GLAccountHierarchyNodeText.Language            = 'D'

{
///////////////////////////////////////////////////////////////////////////////////
//@What Hierarchy Annotation are needed ?
_GLAccountHierarchyNode.GLAccountHierarchy            as GLAccountHierarchy,
_GLAccountHierarchyNode.HierarchyNode                 as HierarchyNode,
_GLAccountHierarchyNode.ValidityEndDate               as ValidityEndDate,

//@What Hierarchy Annotation are needed ?

_JournalEntryItemCube.GLAccount                       as GLAccount,

//@What CDS Notation & Hierarchy Annotation are needed ?
//_GLAccountHierarchyNodeText.HierarchyNodeShortText   as HierarchyNodeShortText,
///////////////////////////////////////////////////////////////////////////////////

 //KPI Section
@Aggregation.default: #SUM
_JournalEntryItemCube.AmountInCompanyCodeCurrency     as AmountInCompanyCodeCurrency,

//Units Sections
@Semantics.currencyCode: true
_JournalEntryItemCube.CompanyCodeCurrency             as CompanyCodeCurrency,

// Associations
_GLAccountHierarchyNode,
_GLAccountHierarchyNodeText
}

This is the Query ZCACCOUNTHIER , slimmed out to whats minimum needed:

@Analytics.query:                  true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.sqlViewName :         'ZQACHIER'
@EndUserText.label:                'Query View GL Account Hierarchy Implementation'
define view                         ZQACCOUNTHIER
with parameters 
// Just for compliance, we use constants in this sample
P_Ledger           : abap.char(2), // For documentation purpose only - Constant in the WHERE-clause
P_LedgerFiscalYear : abap.numc(4), // For documentation purpose only - Constant in the WHERE-clause
P_KeyDate          : abap.dats,    // For documentation purpose only - Constant in the WHERE-clause
P_ValidityEndDate  : abap.dats     //  Use 9999-12-31 = 99991231

as select from ZCACCOUNTHIER( P_Ledger:$parameters.P_Ledger , 
                              P_LedgerFiscalYear:$parameters.P_LedgerFiscalYear , 
                              P_KeyDate:$parameters.P_KeyDate  , 
                              P_ValidityEndDate:$parameters.P_ValidityEndDate   )
as             ZCACCOUNTHIER                        
{
//@What Hierarchy Annotation ?
GLAccountHierarchy   ,
HierarchyNode,
ValidityEndDate,
//HierarchyNodeShortText<br>
//@What Hierarchy Annotation ?<br>//////////////////////////////////////////////////////////////////////////////
// This Annotation results in the error:
// Annotations of GLAccount use hierarchies, but GLAccount does not have any [Analytics]
 @AnalyticsDetails.query: {
            axis: #ROWS,
            displayHierarchy: #ON,
            hierarchyInitialLevel: 2,
            hierarchyBinding: [ {type: #PARAMETER, value: 'GLAccountHierarchy' } ],
            hierarchySettings: {  childNodePosition:  #ABOVE }
          }
GLAccount,<br>////////////////////////////////////////////////////////////////////////////////////

AmountInCompanyCodeCurrency,
CompanyCodeCurrency
}
where GLAccountHierarchy = 'INT'         
and   ValidityEndDate    = $parameters.P_ValidityEndDate

[JoeyLi]

Hi,

This should help you:

https://blogs.sap.com/2023/04/06/analytical-query-design-with-cds-restricted-key-figures-hierarchy-n...

Best Regards,

[Martin_4]

Hi Joey,

with the hierarchy annotations I got a bit lost (also my S4 release does not have transient views.... ).

Addingt in the query ZQACCOUNTHIER the Annotation from Your sample in the, now updated , sample above

results in an error:

Annotations of GLAccount use hierarchies, but GLAccount does not have any [Analytics].

I guess, there are missing Annotation in the CUBE ZCACCOUNTHIER for the field _JournalEntryItemCube.GLAccount , but which ?

ThanXs

Best Martin
---------------------------------------------------------------------------------------------------------------------------

I added, in the query ZQACCOUNTHIER as also shown in the original, updated blog:

....

@AnalyticsDetails.query: {
            axis: #ROWS,
            displayHierarchy: #ON,
            hierarchyInitialLevel: 2,
            hierarchyBinding: [ {type: #PARAMETER, value: '_GLAccountHierarchy' } ],
            hierarchySettings: {  childNodePosition:  #ABOVE }
          }
GLAccount