Creating tree tables with OData V4 on SAP BTP ABAP Environment

[ReinertM]

Since end of last year, it is possible to implement hierarchies in SAP BTP ABAP Environment. Since this feature is fairly easy to implement, here is an end-to-end guide on how to create tree tables on ABAP Environment. 

Step 1: Create a ABAP Package with a name of your choice

Step 2: Create a database table

@EndUserText.label : 'Employee Data'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zmriemployee {

  key employee_id : abap.char(20) not null;
  first_name      : abap.sstring(255);
  last_name       : abap.sstring(255);
  salary          : abap.dec(8,2);
  salary_currency : abap.cuky;
  manager         : abap.char(20);

}

Step 3: Create ABAP Class and fill table with demo data
Note: make sure to add the if_oo_adt_classrun interface to you class to be able to execute the class.

CLASS z_fill_employee_data DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.

    INTERFACES if_oo_adt_classrun .

    DATA:
        lt_employees TYPE TABLE OF zmriemployee.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS z_fill_employee_data IMPLEMENTATION.


  METHOD if_oo_adt_classrun~main.

    APPEND VALUE #(
      employee_id = '00000001'
      first_name = 'John'
      last_name = 'Travolta'
      salary = '100000.99'
      salary_currency = 'CHF'
 ) TO lt_employees.


    APPEND VALUE #(
      employee_id = '00000002'
      first_name = 'Will'
      last_name = 'Smith'
      salary = '80000.99'
      salary_currency = 'USD'
      manager = '00000001' ) TO lt_employees.


    APPEND VALUE #(
      employee_id = '00000003'
      first_name = 'Jessica'
      last_name = 'Alba'
      salary = '85000.99'
      salary_currency = 'CHF'
      manager = '00000002' ) TO lt_employees.


    APPEND VALUE #(
      employee_id = '00000004'
      first_name = 'Monica'
      last_name = 'Belucci'
      salary = '45987.99'
      salary_currency = 'EUR'
      manager = '00000002' ) TO lt_employees.


    APPEND VALUE #(
      employee_id = '00000005'
      first_name = 'Jack'
      last_name = 'Nicolson'
      salary = '80000.99'
      salary_currency = 'CHF'
      manager = '00000002' ) TO lt_employees.

    APPEND VALUE #(
      employee_id = '00000006'
      first_name = 'Cameron'
      last_name = 'Diaz'
      salary = '47856.99'
      salary_currency = 'USD'
      manager = '00000005' ) TO lt_employees.


    out->write( name = `Employee data to be inserted:` data = lt_employees ).

    DELETE FROM zmriemployee.

    INSERT zmriemployee FROM TABLE @lt_employees.

    COMMIT WORK.

    CLEAR lt_employees.

    out->write( `Insert successful ` ).

  ENDMETHOD.
ENDCLASS.

The table data should look like this: 

Note: there is a relationship between the employee_id and the manager field. Where the field manager is empty (i.e. inital), we have no allocation to another employee, hence this person is the highest ranked employee. 

Step 4: Create view entity as interface view for employee data
When creating the data definition, choose the view entity template and import all fileds into the view (CTRL + SPACE) and implement the many to one association to the database table

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Interface view for employee demo data'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
@Metadata.allowExtensions: true
define view entity Z_I_EMPLOYEE_DATA as select from zmriemployee
  association of many to one Z_I_EMPLOYEE_DATA as _Manager on $projection.Manager = _Manager.EmployeeId
{
   key employee_id as EmployeeId,
    first_name as FirstName,
    last_name as LastName,
    salary as Salary,
    salary_currency as SalaryCurrency,
    manager as Manager,
    
    _Manager
}

Step 5: Define Hierarchy mapping
When creating the data definition, choose the Define Parent Child Hierarchy Template and define the hierarchy relationship.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Hierarchy: Read Only: Employee Hierarchy'

define hierarchy Z_I_EMPLOYEE_DATA_HN
  as parent child hierarchy(
    source Z_I_EMPLOYEE_DATA
    child to parent association _Manager
    start where
      Manager is initial
    siblings order by
      LastName ascending
  )
{
  key EmployeeId,
      Manager
}

Step 6: Create consumption view (data definition) with reference to the hierarchy entity.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Hierarchy: Read Only: Employee'

@Metadata.allowExtensions: true
@Search.searchable: true

@OData.hierarchy.recursiveHierarchy:[{ entity.name: 'Z_I_EMPLOYEE_DATA_HN' }]

define view entity Z_C_EMPLOYEE_DATA
  as select from Z_I_EMPLOYEE_DATA
  association of many to one Z_C_EMPLOYEE_DATA as _Manager on $projection.Manager = _Manager.EmployeeId
{

  key EmployeeId,

      @Search: {
      defaultSearchElement: true}
      FirstName,

      LastName,

      Salary,

      SalaryCurrency,

      Manager,

      /* Associations */
      _Manager
}

Step 7: Create Metadata Extension with UI5 annotations.

@Metadata.layer: #CORE
@UI: { headerInfo: {
                     typeName: 'Employee',
                     typeNamePlural: 'Employees',
                     title: { type: #STANDARD, value: 'EmployeeId' }
                   },
       presentationVariant: [{
           sortOrder: [{ by: 'EmployeeId', direction: #ASC }],
           visualizations: [{type: #AS_LINEITEM}]
         }]
     }
annotate entity Z_C_EMPLOYEE_DATA
    with 
{
@UI: {
      facet:          [
                       { id:            'EmployeeId',
                         purpose:       #STANDARD,
                         type:          #IDENTIFICATION_REFERENCE,
                         label:         'EmployeeId',
                         position:      10
                       }
                      ]
    }

  @UI: {
      lineItem:       [{ position: 20 , label: 'The emoloyee id'}],
      identification: [{ position: 20 }]
    }
  EmployeeId;

  @UI: {
      selectionField: [{ position: 30 }],
      lineItem:       [{ position: 30 }],
      identification: [{ position: 30 }]
    }
  FirstName;

  @UI: {
      selectionField: [{ position: 30 }],
      lineItem:       [{ position: 40 }],
      identification: [{ position: 40 }]
    }
  LastName;

  @UI: {
      lineItem:       [{ position: 50 }],
      identification: [{ position: 50 }]
    }
  Salary;

  @UI: {
      lineItem:       [{ position: 70 }],
      identification: [{ position: 70 }]
    }
  Manager;
    
}

Step 8: Create Service Definition to expose the consumption view.

@EndUserText.label: 'Service Defintion'
@ObjectModel.leadingEntity.name: 'Z_C_EMPLOYEE_DATA'
define service Z_UI_Employee_DATA {
  expose Z_C_EMPLOYEE_DATA as Employee;
}

Step 9: Create Service Binding of Binding Type "OData V4 UI" and publish service binding.

Step 10: Click on the preview button and voilà: the table is rendered with a tree structure.

Note: It is important to comply with the data type and strucure of the node_id which is the central component for rendering the tree structure. If you are running into an error when calling your Fiori Fontend, use the Ecplise Debugger to trace down the error. Most likely it is because the field which is used for the hierarchy association does not match the expected data type and you will run into an ABAP Runtime error (e.g. RAISE_SHORTDUMP) or a HTTP error ($batch failed).