Skip to Content

Hierarchy Level Column in CDS view

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers