Hierarchy Level Column in CDS view

[FabioPagoti]

I created a few CDS views in order to define Cost Center Group Hierarchy.

So a tree like this...

- Root Group
--- Group A
--- --- Group A.1
--- --- Group A.2
--- --- --- Group A.2.10
--- --- --- Group A.2.20
--- Group B
--- --- Group B.1
--- --- --- Group B.1.10
--- --- Group B.2
--- Group C
--- --- Group C.1

... becomes a table like this

Child   |   Name    	| Parent
A	| Group A	| Root
A.1	| Group A.1	| A
A.2	| Group A.2	| A
A.2.10  | Group A.2.10	| A.2
A.2.20	| Group A.2.20	| A.2
B	| Group B	| Root
B.1	| Group B.1	| B
B.1.10  | Group B.1.10	| B.1
B.2	| Group B.2	| B
C	| Group C	| Root
C.1	| Group C.1	| C

Note that:

• Root group does not appear as a child node (but I can do a UNION to add it)
• The hierarchy has only one root node and an undefined number of levels.

My problem:

I would like to add a hierarchy level column, like the expected value below:

Child   |   Name    	| Parent| Level
A	| Group A	| Root	| 1
A.1	| Group A.1	| A	| 2
A.2	| Group A.2	| A	| 2
A.2.10  | Group A.2.10	| A.2	| 3
A.2.20	| Group A.2.20	| A.2	| 3
B	| Group B	| Root	| 1
B.1	| Group B.1	| B	| 2
B.1.10  | Group B.1.10	| B.1	| 3
B.2	| Group B.2	| B	| 2
C	| Group C	| Root	| 1
C.1	| Group C.1	| C	| 2

-----

CDS Views

Dimension table

@AbapCatalog.sqlViewName: 'YA'
@AbapCatalog.compiler.compareFilter: true
@Analytics: { dataCategory: #DIMENSION }
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Cost Centers Groups'
define view YA_CDS
  with parameters
  @Consumption: {
      defaultValue: 'XXX'
  }
  controlling_area:kokrs
  
as select from setheader as cost_center_group


left outer join setheadert as text on 
      cost_center_group.setclass = text.setclass
  and cost_center_group.subclass = text.subclass
  and cost_center_group.setname = text.setname
  and text.langu = $session.system_language
        
association[1..*] to setnode as _Hierarchy
  on
      $projection.Class = _Hierarchy.setclass
      and $projection.ControllingArea = _Hierarchy.subclass
      and $projection.id = _Hierarchy.subsetname
{
  
  key cost_center_group.setname as id,
  text.descript as name,
  cost_center_group.setclass as Class,
  cost_center_group.subclass as ControllingArea,
  
  count( distinct _Hierarchy.setname) as HasParent, // trying to sum this in other view
  _Hierarchy
  
}
where 
        cost_center_group.setclass = '0101' // Cost Center Group
    and cost_center_group.subclass = $parameters.controlling_area
    
group by 
    cost_center_group.setname,
    text.descript,
    cost_center_group.setclass,
    cost_center_group.subclass
  

Hierarchy view

@AbapCatalog.sqlViewName: 'YB'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@ObjectModel: { 
    dataCategory: #HIERARCHY
}
@VDM.viewType: #BASIC
@ObjectModel.representativeKey: 'Child'
@Hierarchy.parentChild: 
{ 
    recurse:
        {
            parent: 'Parent',
            child:  'Child'
        },
    siblingsOrder:
        {
            by: 'Child',
            direction: 'ASC'
        },
    directory: '_CostCenterGroup'
  }
@EndUserText.label: 'Cost center group hierarchy node'
define view YB_CDS


as select from setnode as CostCenterGroup 


association[1..1] to I_ControllingArea as _ControllingArea
  on $projection.ControllingArea = _ControllingArea.ControllingArea


association[1..1] to YA_CDS as _CostCenterGroup
    on $projection.Child = _CostCenterGroup.id
    
association[1..1] to YA_CDS as _CostCenterParentGroup
    on $projection.Parent = _CostCenterParentGroup.id
{
    key CostCenterGroup.subsetname as Child,
    _CostCenterGroup( controlling_area: 'XXX' ).name as Name,
    
    CostCenterGroup.setname  as Parent,
    _CostCenterParentGroup( controlling_area: 'XXX' ).name as ParentName,
    
    CostCenterGroup.subclass as ControllingArea,

    _ControllingArea,
    _CostCenterGroup,
    _CostCenterParentGroup
    
}


where
    CostCenterGroup.setclass = '0101'
and CostCenterGroup.subclass = 'XXX'
and CostCenterGroup.subsetscls = 'XXX'

These two views solves the hierarchy problem. In order to try to solve the Level column, I tried to add a column in the first view called HasParent.

My objective was to do some kind of recursive aggregation on it but it did not work. For that I created a third view.

@AbapCatalog.sqlViewName: 'YC'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Level sum'
define view YC_CDS as select from YB_CDS {
     
    Child, 
    Name, 
    Parent, 
    ParentName, 
    
    // every non-root node is always = 2 - Makes sense but is not what I need
    lvl + parent_lvl as node_level,  
     
    _CostCenterGroup
}

System version:

SAP_ABA 750 SP5

Thanks!

[former_member1335216]

Hi,

Did you ever find an answer to your question?
I would really appreciate how you finished coding it.

[leandro_oneda]

Hi,

I achieved this goal using a "define hierachy" as explained here.

https://int4.com/abap-cds-recursive-association-using-hierarchy

I'm not sure if this is the best way nowadays but this worked for me.

@Hierarchy.parentChild:
[{
    name: 'Parent',
    recurse: {
        parent: ['PrecOrgUnit', 'PrecSetName' ],
        child: ['SubsOrgUnit', 'SubsSetName' ]
    }
}]

define view ZI_CostCentersGroupsBase
  as select from setnode
  association [0..1] to ZI_CostCentersGroupsBase as _Parent    on  $projection.PrecOrgUnit = _Parent.SubsOrgUnit
                                                               and $projection.PrecSetName = _Parent.SubsSetName
  association [1..1] to I_SetHeader              as _SetHeader on  $projection.SubsSetClass = _SetHeader.SetClass
                                                               and $projection.SubsOrgUnit  = _SetHeader.SetSubClass
                                                               and $projection.SubsSetName  = _SetHeader.SetID
{
  key subclass                     as PrecOrgUnit,
  key setname                      as PrecSetName,
      cast( lineid as setlnseqnr ) as PrecSetCounter,
      setclass                     as PrecSetClass,
      subsetscls                   as SubsOrgUnit,
      subsetname                   as SubsSetName,
      seqnr                        as SubsSetCounter,
      subsetcls                    as SubsSetClass,
      _Parent,
      _SetHeader
}
where
      setclass   = '0101' //Cost Center

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Cost Centers Groups Hierarchy'
define hierarchy ZI_CostCentersGroupsHier
  as parent child hierarchy(
    source ZI_CostCentersGroupsBase
    child to parent association _Parent
    siblings order by
      PrecOrgUnit    ascending,
      PrecSetName    ascending,
      PrecSetCounter ascending
    orphans root
  )
{
  key PrecOrgUnit,
  key PrecSetName,
      PrecSetCounter,
      PrecSetClass,
      SubsOrgUnit,
      SubsSetName,
      SubsSetCounter,
      SubsSetClass,
      $node.parent_id             as ParentNode,
      $node.node_id               as ChildNode,
      $node.hierarchy_is_orphan   as HierlsOrphan,
      $node.hierarchy_level       as HierLevel,
      $node.hierarchy_rank        as HierRank,
      $node.hierarchy_parent_rank as HierParentRank,
      $node.hierarchy_tree_size   as HierTreeSize,
      _SetHeader
}

This last one view I created just to expose as an odata service.

define view entity ZC_CostCentersGroupsHier
  as select from ZI_CostCentersGroupsHier
{
  key PrecOrgUnit,
  key PrecSetName,
  key PrecSetCounter,
      PrecSetClass,
      SubsOrgUnit,
      SubsSetName,
      SubsSetCounter,
      SubsSetClass,
      ParentNode,
      ChildNode,
      HierlsOrphan,
      HierLevel,
      HierRank,
      HierParentRank,
      HierTreeSize,
      /* Associations */
      _SetHeader
}