Skip to Content

Hi!

In this post I’d like to consider hierarchies in CDS views.

Good starting point could be looking at Cost Center hierarchy, it was described here.

I was interested in standard analytics capabilities of HCM in S/4HANA (1511) and Orgunit Hierarchy realization in ABAP CDS views.

I. Find a standard HR hierarchy

I started with Query Browser Fiori App and wanted to find standard analytical queries by HCM components like HR-PA, HR-PD, HR-PT, HR-PY etc. But there was not one unfortunately.

After this I went to ABAP dictionary to find where main HR tables, like HRP1000, HRP1001, PA0001 are used. It is possible to find DDL Sources.

At the picture you could see a result for HRP1001, there is a private CDS view for table of relationships between HR Objects (PIQCDSHRP1001)

After this I found a packages with ABAP CDS definitions of HCM Area:

There are not so many ABAP CDS views. You could see private, interface and consumption prefics of names:

But I didn’t find Orgunit Hierarchy in some of there views. That’s why we have to build a new one.

Important: I am sure that at some future releases of S/4HANA there will be (or already exist in 1610) a standard approach, but as this is my case, I am using this like an opportunity of building custom hierarchy ABAP CDS View.

II. Create custom hierarchy CDS view

  1. Create a hierarchy view Zi_Orgunit_H

Notes:

  • I’m using some standard private CDS views on HRP1000 and HRP1001 tables.
  • CDS view Zi_Orgunit_Dim will be showed later. It’s our dimention CDS view. I will open it in Analysis for Excel to demonstrate a result.
  • This is a simple example on test data and I am not care about time-dependency of hierarchy structure, but in real case it of cause shouldbe done. You could use P_TODAYDATE or create a variable for key date different that today.
  • Take care about correct definition of hirarchy semation. If you don’t use hierarchy directory @Hierarchy.ParentChild.name is mandatory. For more information go Hierarchy Annotations.
@VDM.viewType: #BASIC
@ObjectModel: { dataCategory: #HIERARCHY }
@AbapCatalog.sqlViewName: 'ZIORGUNITH'
@Hierarchy.parentChild.name: 'ORGEH_01'
@Hierarchy.parentChild.label: 'Orgunit hierarchy 01'
@Hierarchy.parentChild: 
{ recurse:          {   parent: 'ParentNode',   child:  'HierarchyNode'   },
  siblingsOrder:    {   by: 'HierarchyNode',    direction: 'ASC'   },
  orphanedNode.handling: #ROOT_NODES,
 rootNode.visibility: #DO_NOT_ADD_ROOT_NODE
  }
@AccessControl.authorizationCheck: #NOT_ALLOWED  

define view Zi_Orgunit_H       
     
 as select from P_PDOBJECT as object 
 
 inner join P_PDRELATION as relation  on
   relation.PlanVersion       =  object.PlanVersion  and   
   relation.ObjectType        =  object.ObjectType   and
   relation.ObjectID          =  object.ObjectID     and
   relation.RelationStatus    =  object.ObjectStatus  
 association[0..*] to Zi_Orgunit_Dim as _Zi_Orgunit_Dim on $projection.OrgUnit = _Zi_Orgunit_Dim.Orgunit
 
{       
    key object.ObjectID as HierarchyNode,
    relation.EndDate as RelationEndDate,
    relation.StartDate as RelationStartDate,
    relation.RelatedObjectID  as ParentNode,
    @ObjectModel.foreignKey.association: '_Zi_Orgunit_Dim'
    relation.ObjectID as OrgUnit,
    
    _Zi_Orgunit_Dim
 
} where object.PlanVersion = '01' and
        relation.ObjectType             = 'O' and
        relation.RelationShipDirection  = 'A' and
        relation.ReleationShip          = '002' and
        relation.RelatedObjectType      = 'O'

Data preview in HANA Studio:

 

2. Create a dimension view Zi_Orgunit_Dim

Notes:

  • In this CDS view we create an association to hierarchy CDS, in hierarchy CDS an association to dimention CDS is also needed.
  • For texts to be showen in Analysis we could use a standard CDS view I_OrgUnitText, but in this view dataCategory is missing (#TEXT needed to be defined), that’s why @ObjectModel.text.assosiation is not possible. But we could always use @ObjectModel.text.element annotation instead.
@AbapCatalog.sqlViewName: 'ZIORGUNITD'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'ZI_ORGUNIT_DIM'
@VDM.viewType: #BASIC
@Analytics: { dataCategory: #DIMENSION, dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'Orgunit'
define view Zi_Orgunit_Dim
 as select distinct from P_PDOBJECT 
    association[0..*] to Zi_Orgunit_H  as _OrgUnit_Hier on  $projection.Orgunit    = _OrgUnit_Hier.OrgUnit
                                                       
    association[0..*] to I_OrgUnitText as _Text on
        $projection.Orgunit = _Text.OrganizationalUnit                                                                      
{
@ObjectModel.Hierarchy.association: '_OrgUnit_Hier'
@ObjectModel.text.element:  [ 'OrgunitName' ] 
@EndUserText.label: 'Orgunit'
key P_PDOBJECT.ObjectID as Orgunit, 
@Semantics.businessDate.to: true
P_PDOBJECT.EndDate,
@Semantics.businessDate.from: true
P_PDOBJECT.StartDate,

/*Assosiations*/
_OrgUnit_Hier,
/*Text*/
@Semantics.text: true
_Text.OrganizationalUnitName as OrgunitName
    
} where P_PDOBJECT.ObjectType = 'O';

Data preview in HANA Studio (As you can see no hierarchy are show. This is normal of course):

3. Open dimension CDS view Zi_Orgunit_Dim in Analysis for Excel.

Notes:

  • Technical name is 2CZIORGUNITD.
  • Technical name and description of hierarchy are the same as we defined earlier.

 

 

4. Compare with PPOSE:

Almost the same except a root Company organisational unit.

Problem:

Our hierarchy has ROOT node Company 50000050, but unfortunately this is not showed correctly in Analysis for Excel and BEx Analyzer also.

I supposed that these 2 annotation at the hierarchy CDS view definition should control this:

  • @Hierarchy.parentChild.orphanedNode.handling: #ROOT_NODES

Defines how nodes with a parent that does not occur as a child are processed. It should create a root for Company 50000050, but It is not happen.

  • @Hierarchy.parentChild.rootNode.visibility: #DO_NOT_ADD_ROOT_NODE

The system will not add an additional artificial single root node to the hierarchy.

Compromise Solution:

We could filter in reports Not assigned Nodes of hierarchy handle this case some how.

P.S. If you faced this problem and found some general (more beautiful) solution, please let me know in comments.

 

Thank you for attention!

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Elan Wang

    hello Maksim,

    I solved this problem as you face .  you need add on line for the root .

    for example:

    add the data (‘tony’, ”) to the following table .

    name boss
    elan sunny
    joy eric
    sunny tony
    eric tony

     

    why do that?
    at class CL_RSODP_ABAP_CDS_CONTEXT->GET_PARENT_CHILD_HIERARCHY line 382,  the where condition only search the node that have parent, then build the hierarchy tree, so if you       want the root display, you need do that.

      DO 99 TIMES.
        ASSERT sy-index < 99.
        LOOP AT lt_node ASSIGNING <ls_node>
             WHERE parentid IS NOT INITIAL
               AND tlevel IS INITIAL.
          READ TABLE lt_node ASSIGNING <ls_node2>
               WITH TABLE KEY nodeid = <ls_node>-parentid.
          ASSERT sy-subrc = 0.
          CHECK <ls_node2>-tlevel IS NOT INITIAL.
          <ls_node>-tlevel = <ls_node2>-tlevel + 1.
        ENDLOOP.
        IF sy-subrc <> 0.
          EXIT.
        ENDIF.
      ENDDO.

     

    best,

    Elan

    (0) 

Leave a Reply