Skip to Content
Technical Articles

SAP S/4 HANA Custom Fiori app for Project & WBS using CDS View Table Function, SmartFilter & Tree table

Hi SAP Fiori Development Enthusiasts,

For the past few years, I have been immersed in the land of SAP S/4 HANA Fiori and CDS development.  It’s amazing how quickly you can develop very effective custom apps using CDS views to harness the speed of HANA and OData annotations for building feature rich Fiori UIs.

Recently, I built two custom Fiori applications: one to search for Projects, WBS Elements, and settlement cost centers presented in a tree table and second one to display the allocated and consumed budgeted amounts to help the finance team see how the project spend is coming along.

In this blog, I would like to present the custom Fiori app that I developed for searching for Projects, WBS elements, and settlement cost centers.  This blog will present details on how to read Project and WBS data using CDS views and table functions and then render the data in a tree table with a smart filter.

 

Here is a snapshot of the custom Fiori application showing Project, WBS element and settlement cost center data in a nice tree view table with a smart filter bar:

 

So here is a sample project with nested WBS elements from SAP transaction code CJ20N.

CJ30N

Transaction code – CJ30N

 

In the SAP transaction, you can only show one project at a time, and you cannot search based on settlement cost centers.  The customer needed to show all of the projects and WBS tied to settlement costs centers and display them in their hierarchical views.  And that is why we built this custom Fiori App.  Quite a few nice Fiori concepts are used in this app for rendering tree tables, downloading data to excel, manipulating the smart filters dynamically, changing the OData call to the backend, etc.  My hope is that this blog will come in handy for you if are embarking on similar custom Fiori development projects.

So I had to built a custom app to show multiple projects with the child WBS elements and provide filters for projects, WBS, and settlement cost centers.  Let’s dive in!

Here are the tiles for the custom Project WBS Fiori apps – one for the Hierarchy and one for the Analysis Report:

 

So here is the first Project WBS Hierarchy application main screen:

 

As we type in the Project, we have a nice value help fuzzy search implemented with the annotation and CDS view for the value help (code is below):

Project%20help%20fuzzy%20search

Project help fuzzy search

 

So if you select Project I-000066, here is the display of that Project and the child WBS elements rendered in a tree table rendering the hierarchy:

Project%20I-000066

 

Project I-000066

 

You can also search for multiple projects by selected them in the Project value help:

Project%20value%20help%20screen

Project value help screen

 

Here you can see multiple projects and the underlying WBS Elements.  You can even select all of the Projects in the system and get the entire Projects and WBS Elements shown and later exported to excel (the code to download this data to Excel is also below):

Multiple%20projects

Multiple projects

 

Here is the WBS Element value help:

WBS%20Element%20value%20help

WBS Element value help

 

Here is the value help for the settlement cost center:

Settlement%20Cost%20Center%20value%20help

Settlement Cost Center value help

 

 

So here is the CDS view ZRTR_PROJ_WBS_HIERARCHY called that is exposed as an OData interface that is consumed by the Fiori application: /sap/opu/odata/sap/ZRTR_PROJ_WBS_HIERARCHY_CDS/.

This view is using a table function ZRTR_WBSHIERARCHY_TABLEFUNC and associations to the standard SAP CDS views I_ProjectWithCodingMaskVH and I_WBSElementWithCodingMaskVH and custom CDS views ZRTR_I_SETTLEMENTCOSTCENTER_VH and ZRTR_WBS_STATUS

There are annotations for navigation and value help:

//---------------------------------------------------------------------*
// Author        : Jay Malla                                           *
// Description   : This is the main CDS view for Project WBS Hierarchy *
//                 that shows the different levels and is used by the  *
//                 Fiori WBS Hierarchy application ZRTR_WBS_REPORT     *
//                                                                     *
//---------------------------------------------------------------------*


@AbapCatalog.sqlViewName: 'ZRTR_PROJWBDHIER'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Project WBS Hierarchy'

@OData.publish: true

define view ZRTR_PROJ_WBS_HIERARCHY as select from ZRTR_WBSHIERARCHY_TABLEFUNC 

  association [0..1] to I_ProjectWithCodingMaskVH    as _ProjectValueHelp on  _ProjectValueHelp.Project = $projection.Project

  association [0..1] to I_WBSElementWithCodingMaskVH as _WBSValueHelp     on  _WBSValueHelp.WBSElement = $projection.WBSElement

  association [0..1] to ZRTR_I_SETTLEMENTCOSTCENTER_VH as _SettlementCostCenter on  $projection.SettlementCostCenter = _SettlementCostCenter.CostCenter
                                                                              //and _SettlementCostCenter.ControllingArea = 'A000'
  association [0..1] to ZRTR_WBS_STATUS as _ProjectStatusClosed on $projection.ProjectObjectNumber = _ProjectStatusClosed.objnr
                                                            and _ProjectStatusClosed.spras = $session.system_language
                                                            and _ProjectStatusClosed.txt04 = 'CLSD'

{

    //ZRTR_WBSHIERARCHY_TABLEFUNC 
    @EndUserText.label: 'Counter'
    key counter as Counter, 
    @EndUserText.label: 'NodeID'
    nodeid as NodeID, 
    @EndUserText.label: 'NodeType'
    nodetype as NodeType, 
    @EndUserText.label: 'HierarchyLevel'
    hierarchylevel as HierarchyLevel, 
    @EndUserText.label: 'ParentCounter'
    parentcounter as ParentCounter, 
    @EndUserText.label: 'ParentNodeID'
    parentnodeid as ParentNodeID, 
    @EndUserText.label: 'Description'
    description as Description, 
    @Consumption.valueHelp: '_ProjectValueHelp'
    @EndUserText.label: 'Project'
    @Consumption.semanticObject: 'Project'
    @UI:{lineItem: [{label: 'Project',
    type: #FOR_INTENT_BASED_NAVIGATION,
    semanticObjectAction: 'displayFactSheet' }]}
    project as Project, 
    @EndUserText.label: 'ProjectDescription'
    projectdescription as ProjectDescription, 
    @EndUserText.label: 'ProjectObjectNumber'
    projectobjectnumber as ProjectObjectNumber, 
    @Consumption.valueHelp: '_WBSValueHelp'
    @EndUserText.label: 'WBSElement'
    @Consumption.semanticObject: 'WBSElement'
    @UI:{lineItem: [{label: 'WBSElement',
    type: #FOR_INTENT_BASED_NAVIGATION,
    semanticObjectAction: 'displayFactSheet' }]}
    wbselement as WBSElement, 
    @EndUserText.label: 'WBSDescription'
    wbselementdescription as WBSDescription,
    @EndUserText.label: 'WBSObjectNumber'
    wbselementobjectnumber as WBSObjectNumber,
    @Consumption.valueHelp: '_SettlementCostCenter'
    @EndUserText.label: 'SettlementCostCenter'
    settlementcostcenter as SettlementCostCenter,
    @EndUserText.label: 'SettlementCostCenterName'
    _SettlementCostCenter.CostCenterName as SettlementCostCenterName,
    @EndUserText.label: 'SettlementCostCenterDescription'
    _SettlementCostCenter.CostCenterDescription as SettlementCostCenterDesc,
//    @EndUserText.label: 'SettlementCostCenterNoLeadingZeroes'
//    ltrim(settlementcostcenter,'0') as SettlementCostCenterZeroesRmv,
//    @EndUserText.label: 'SettlementCostCenterString'
//    cast(settlementcostcenter as char10) as SettlementCostCenterString,
    @EndUserText.label: 'ActiveFlag'
    case 
      when (_ProjectStatusClosed.inact= '' and _ProjectStatusClosed.txt30='Closed') then 'INACTIVE'
      else 'ACTIVE'
    end
      as ActiveFlag,
    @EndUserText.label: 'ProjectCreatedByUser'
    _ProjectValueHelp.CreatedByUser as ProjectCreatedByUser,
    @EndUserText.label: 'ProjectCreationDate'
    _ProjectValueHelp.CreationDate as ProjectCreationDate,         
    @EndUserText.label: 'WBSCreatedByUser'
    _WBSValueHelp.CreatedByUser as WBSCreatedByUser,
    @EndUserText.label: 'WBSCreationDate'
    _WBSValueHelp.CreationDate as WBSCreationDate,         
    
    //Associations
    _ProjectValueHelp,
    _WBSValueHelp,
    _SettlementCostCenter,
    _ProjectStatusClosed
    
}

 

Here is the CDS view table function ZRTR_WBSHIERARCHY_TABLEFUNC:

//---------------------------------------------------------------------*
// Author        : Jay Malla                                           *
// Creation Date : December 2020                                       *
// Description   : WBS Hierarchy Table Function.  This is used by the  *
//                 Fiori WBS Hierarchy ZRTR_WBS_REPORT                 *
//                                                                     *
//---------------------------------------------------------------------*


@EndUserText.label: 'WBS Hierarchy Table Function'

define table function ZRTR_WBSHIERARCHY_TABLEFUNC
returns
{

   mandt: abap.clnt;
   counter : integer;
   nodeid : char_60;
   nodetype : char20;
   hierarchylevel : integer;
   parentcounter : integer;
   parentnodeid : char_60;
   description : char_60;
   project : char30;
   projectdescription : char_60;
   projectobjectnumber: char22;
   wbselement : char30;
   wbselementdescription : char_60;
   wbselementobjectnumber: char22;
   settlementcostcenter: kostl;

}
implemented by method
  zcl_rtr_wbshierarchy_tablefunc=>GET_RESULT;

 

This table function CDS view is the implemented by the class zcl_rtr_wbshierarchy_tablefunc and method GET_RESULT.  This method reads the WBS hierarchy for the project and sets all of the counters necessary for rendering the tree view – we have the counter, parent counter, and hierarchy level.

CLASS zcl_rtr_wbshierarchy_tablefunc DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb .

    TYPES:
      BEGIN OF ty_data,
        mandt      TYPE sy-mandt,
        pspnr      TYPE proj-pspnr,
        pspid      TYPE proj-pspid,
        post1      TYPE ps_post1,
        pspid_edit TYPE ps_pspid_edit,
      END OF ty_data .
    TYPES:
      tt_data TYPE STANDARD TABLE OF ty_data .

    CLASS-METHODS get_result FOR TABLE FUNCTION zrtr_wbshierarchy_tablefunc.

*  class-methods GET_RESULT
*    exporting
*      value(E_T_DATA) type TT_DATA
*    raising
*      CX_AMDP_ERROR .

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS ZCL_RTR_WBSHIERARCHY_TABLEFUNC IMPLEMENTATION.


  METHOD get_result
    BY DATABASE FUNCTION FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY

  USING proj prps prhi cobrb.


    declare lv_count integer;
    declare lv_counter integer;
    declare lv_project_counter integer;
    declare lv_wbs_counter integer;
    declare uname nvarchar(12);
    declare clnt nvarchar(3);
    declare parentnodeid varchar(60);
    declare lv_parent_counter integer;
    declare lv_parent_hierarchy integer;

    declare hierarchy_row row (  mandt char(3),
                                 counter integer,
                                 nodeid varchar(60),
                                 nodetype varchar(20),
                                 hierarchylevel integer,
                                 parentcounter integer,
                                 parentnodeid varchar(60),
                                 description varchar(60),
                                 project varchar(30),
                                 projectdescription varchar (60),
                                 projectobjectnumber varchar (22),
                                 wbselement varchar(30),
                                 wbselementdescription varchar(60),
                                 wbselementobjectnumber varchar(22),
                                 settlementcostcenter char(10));

    declare hierarchy_table table like :hierarchy_row;


    uname:= session_context('APPLICATIONUSER');
    clnt := session_context('CLIENT');

*   All of the projects from the proj table
    projects = SELECT mandt,
            pspnr,
            pspid,
            post1,
            pspid_edit,
            objnr
            FROM proj
            WHERE mandt = (SELECT session_context ('CLIENT') FROM dummy);

*   All of the WBS from the prps table
    wbs_hierarchy = SELECT w.mandt,
            w.pspnr,
            w.posid,
            w.post1,
            w.psphi,
            w.poski,
            w.objnr,
            h.posnr,
            h.up
            from prps w join prhi h
            on  w.mandt = h.mandt
            and w.pspnr = h.posnr
            where w.mandt = (SELECT session_context ('CLIENT') FROM dummy);

*   Joining project, wbs and hierarchy tables
    project_wbs_hierarchy = SELECT project.mandt,
            project.pspid_edit as project_number,
            project.post1 as project_description,
            project.pspnr as project_sequence_number,
            project.pspid as project_internal_number,
            project.objnr as project_object_number,
            wbs.posid_edit as wbs_number,
            wbs.post1 as wbs_description,
            wbs.pspnr as wbs_sequence_number,
            wbs.posid as wbs_internal_number,
            wbs.objnr as wbs_object_number,
            hierarchy.up as wbs_parent_sequence_number,
            settlement.kostl as settlementcostcenter
            from proj project join prps wbs
            on project.mandt = wbs.mandt
            and project.pspnr = wbs.psphi
            join prhi hierarchy
            on wbs.mandt = hierarchy.mandt
            and wbs.pspnr = hierarchy.posnr
            and wbs.psphi = hierarchy.psphi
            left outer join cobrb settlement
            on wbs.mandt = settlement.mandt
            and wbs.objnr = settlement.objnr
            where project.mandt = (SELECT session_context ('CLIENT') FROM dummy);

    SELECT COUNT(*) INTO lv_count FROM :project_wbs_hierarchy;

    lv_counter:= 0;

*   Loops through the projects - create a project entry and then the WBS entries for this
    for lv_project_counter in 1..record_count( :projects) DO
        lv_counter:= :lv_counter + 1;
*       For the project
*       hierarchy_row.nodeid = :projects.pspid_edit[ :lv_counter];
        hierarchy_row.mandt = :clnt;
        hierarchy_row.counter = :lv_counter;
        hierarchy_row.nodeid = concat ( :projects.pspid_edit[ :lv_project_counter], '00000000');
        hierarchy_row.nodetype = 'Project';
        hierarchy_row.hierarchylevel = 0;
        hierarchy_row.parentcounter = 0;

        hierarchy_row.parentnodeid = null;
        hierarchy_row.description = :projects.post1[ :lv_project_counter];
        hierarchy_row.project = :projects.pspid_edit[ :lv_project_counter];
        hierarchy_row.projectdescription = :projects.post1[ :lv_project_counter];
        hierarchy_row.projectobjectnumber = :projects.objnr[ :lv_project_counter];
        hierarchy_row.wbselement = null;
        hierarchy_row.wbselementdescription = null;
        hierarchy_row.wbselementobjectnumber = null;
        hierarchy_row.settlementcostcenter = null;


        temprow = select  :hierarchy_row.mandt,
                          :hierarchy_row.counter,
                          :hierarchy_row.nodeid,
                          :hierarchy_row.nodetype,
                          :hierarchy_row.hierarchylevel,
                          :hierarchy_row.parentcounter,
                          :hierarchy_row.parentnodeid,
                          :hierarchy_row.description,
                          :hierarchy_row.project,
                          :hierarchy_row.projectdescription,
                          :hierarchy_row.projectobjectnumber,
                          :hierarchy_row.wbselement,
                          :hierarchy_row.wbselementdescription,
                          :hierarchy_row.wbselementobjectnumber,
                          :hierarchy_row.settlementcostcenter
                          from DUMMY;

*       Insert the Project into the hierarchy table
        :hierarchy_table.insert( :temprow);

*       Get all of the WBS for the project....
        project_wbs_hierarchy_subset = select * from :project_wbs_hierarchy
                                                where project_number = :hierarchy_row.project
                                                order by wbs_sequence_number;

*       For all of the WBS for that project
        for lv_wbs_counter in 1..record_count( :project_wbs_hierarchy_subset) DO

            parentnodeid = concat ( :projects.pspid_edit[ :lv_project_counter],:project_wbs_hierarchy_subset.wbs_parent_sequence_number[ :lv_wbs_counter]);
            parent_node = select * from :hierarchy_table where nodeid = :parentnodeid;
            lv_parent_counter = :parent_node.counter[1];
            lv_parent_hierarchy = :parent_node.hierarchylevel[1];
            hierarchy_row.parentcounter = :lv_parent_counter;

            lv_counter:= :lv_counter + 1;
            hierarchy_row.counter = :lv_counter;
            hierarchy_row.nodeid = concat ( :projects.pspid_edit[ :lv_project_counter],:project_wbs_hierarchy_subset.wbs_sequence_number[ :lv_wbs_counter]);
            hierarchy_row.nodetype = 'WBS';

*           if this is a top most WBS
            if :project_wbs_hierarchy_subset.wbs_parent_sequence_number[:lv_wbs_counter] = '00000000'
                then
                    hierarchy_row.hierarchylevel = 1;
            else
                hierarchy_row.hierarchylevel = :lv_parent_hierarchy + 1;
            end if;


*           hierarchy_row.hierarchylevel = :project_wbs_hierarchy_subset.wbs_parent_sequence_number[:lv_wbs_counter];

            hierarchy_row.parentnodeid = :parentnodeid;
            hierarchy_row.description = :project_wbs_hierarchy_subset.wbs_description[ :lv_wbs_counter];
            hierarchy_row.project = :projects.pspid_edit[ :lv_project_counter];
            hierarchy_row.projectdescription = :projects.post1[ :lv_project_counter];
            hierarchy_row.projectobjectnumber = :projects.objnr[ :lv_project_counter];
            hierarchy_row.wbselement = :project_wbs_hierarchy_subset.wbs_number[:lv_wbs_counter];
            hierarchy_row.wbselementdescription = :project_wbs_hierarchy_subset.wbs_description[:lv_wbs_counter];
            hierarchy_row.wbselementobjectnumber = :project_wbs_hierarchy_subset.wbs_object_number[:lv_wbs_counter];
            hierarchy_row.settlementcostcenter = :project_wbs_hierarchy_subset.settlementcostcenter[:lv_wbs_counter];


            temprow = select  :hierarchy_row.mandt,
                              :hierarchy_row.counter,
                              :hierarchy_row.nodeid,
                              :hierarchy_row.nodetype,
                              :hierarchy_row.hierarchylevel,
                              :hierarchy_row.parentcounter,
                              :hierarchy_row.parentnodeid,
                              :hierarchy_row.description,
                              :hierarchy_row.project,
                              :hierarchy_row.projectdescription,
                              :hierarchy_row.projectobjectnumber,
                              :hierarchy_row.wbselement,
                              :hierarchy_row.wbselementdescription,
                              :hierarchy_row.wbselementobjectnumber,
                              :hierarchy_row.settlementcostcenter
                              from DUMMY;

*           Insert the WBS into the hierarchy table
            :hierarchy_table.insert( :temprow);


        end for;
    end for;


*    RETURN SELECT *
*            FROM :project_wbs_hierarchy;

    RETURN SELECT *
            FROM :hierarchy_table;


  ENDMETHOD.
ENDCLASS.

 

Here is the value help CDS ZRTR_I_SETTLEMENTCOSTCENTER_VH for the settlement cost center:

//---------------------------------------------------------------------*
// Author        : Jay Malla                                           *
// Creation Date : January 2021                                        *
// Description   : This is the Settlement Cost Center value help for   *
//                 the ZRTR_PROJ_WBS_HIERARCHY CDS view for the Project*
//                 WBS Hierarchy Display                               *
//                                                                     *
//---------------------------------------------------------------------*

@AbapCatalog.sqlViewName: 'ZRTR_STLCOSTVH'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Settlement Cost Center Value Help'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #MASTER
@VDM.viewType: #COMPOSITE


define view ZRTR_I_SETTLEMENTCOSTCENTER_VH
  as select from I_CostCenter

  association [0..*] to I_CostCenterText as _Text on  _Text.ControllingArea       = 'A000'
                                                  and $projection.CostCenter      = _Text.CostCenter
                                                  and $projection.ValidityEndDate = _Text.ValidityEndDate
{
      //I_CostCenter
      //    ControllingArea,

  key CostCenter, //This work around is needed preserve the leading zeroes in the value help for the smart filter to work
      _Text.CostCenterName,
      ValidityEndDate,
      ValidityStartDate,
      CompanyCode,
      //    BusinessArea,
      CostCtrResponsiblePersonName,
      //    CostCtrResponsibleUser,
      CostCenterCurrency,
      //    ProfitCenter,
      //    Department,
      //    CostingSheet,
      //    FunctionalArea,
      Country,
//      @EndUserText.label: 'CostCenterNoLeadingZeroes'
//      Ltrim(CostCenter,'0')      as CostCenterZeroTrimmed, //This work around is neto preserve the leading zeroes in the value help for the smart filter to work
//      @EndUserText.label: 'CostCenterString'
//      cast(CostCenter as char10) as CostCenterString,
      _Text.CostCenterDescription,
      //    CostCenter as CostCenterOriginal,
      //    Region,
      //    CityName,
      //    CostCenterStandardHierArea,
      //    CostCenterCategory,
      //    IsBlkdForPrimaryCostsPosting,
      //    IsBlkdForSecondaryCostsPosting,
      //    IsBlockedForRevenuePosting,
      //    IsBlockedForCommitmentPosting,
      //    IsBlockedForPlanPrimaryCosts,
      //    IsBlockedForPlanSecondaryCosts,
      //    IsBlockedForPlanRevenues,
      //    ConsumptionQtyIsRecorded,
      //    Language,
      //    CostCenterCreatedByUser,
      //    CostCenterCreationDate,

      /* Associations */
      //I_CostCenter
      //    _BusinessArea,
      //    _CompanyCode,
      //    _ControllingArea,
      //    _CostCenterCategory,
      //    _CostCenterHierarchyNode,
      //    _Country,
      //    _Currency,
      //    _FunctionalArea,
      //    _Language,
      //    _ProfitCenter,
      //    _Region,
      _Text
}
where
  ControllingArea = 'A000'

 

Here is the CDS view ZRTR_WBS_STATUS for the statuses – joining the jest and tj02t tables

//---------------------------------------------------------------------*
// Author        : Jay Malla                                           *
// Creation Date : December 2020                                       *
// Description   : CDS for WBS Status                                  *
//---------------------------------------------------------------------*


@AbapCatalog.sqlViewName: 'ZRTR_WBSSTATUS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'WBS Status'

define view ZRTR_WBS_STATUS
  as select from jest  as _IndividualObjectStatus
    inner join   tj02t as _SystemStatusTexts on  _IndividualObjectStatus.mandt = $session.client
                                             and _IndividualObjectStatus.stat = _SystemStatusTexts.istat
                                             and _SystemStatusTexts.spras     = $session.system_language
{

    key _IndividualObjectStatus.mandt, 
    key _IndividualObjectStatus.objnr, 
    key _IndividualObjectStatus.stat, 
    _IndividualObjectStatus.inact, 
    _IndividualObjectStatus.chgnr, 
    
    _SystemStatusTexts.istat, 
    _SystemStatusTexts.spras, 
    _SystemStatusTexts.txt04, 
    _SystemStatusTexts.txt30

}

 

Here is the annotation for the smart filter and table:

Annotations%20for%20Smart%20Filter%20and%20Smart%20Table

Annotations for Smart Filter and Smart Table

Annotations%20continued

Annotations continued

 

Here is the App.view.xml with the smart filter and tree table:

<mvc:View xmlns:mvc="sap.ui.core.mvc" xmlns:smartForm="sap.ui.comp.smartform" xmlns:smartMultiInput="sap.ui.comp.smartmultiinput"
	xmlns:core="sap.ui.core" xmlns:semantic="sap.f.semantic" xmlns:footerbar="sap.ushell.ui.footerbar"
	xmlns:smartFilterBar="sap.ui.comp.smartfilterbar" xmlns:smartTable="sap.ui.comp.smarttable" xmlns="sap.ui.table" xmlns:m="sap.m"
	controllerName="com.customer.wbs.report.ZRTR_WBS_REPORT.controller.App" height="100%">
	<semantic:SemanticPage xmlns:table="sap.ui.table" id="page" headerPinnable="false" toggleHeaderOnTitleClick="false">
		<semantic:titleHeading>
			<m:Title id="title" text="{i18n>title}" level="H2"/>
		</semantic:titleHeading>
		<semantic:content>
			<m:VBox id="Vbox" fitContainer="true">
				<m:items>
					<smartFilterBar:SmartFilterBar id="smartFilterBar" entityType="ZRTR_PROJ_WBS_HIERARCHYType" liveMode="true" showGoButton="true"
						showGoOnFB="true" showClearButton="true" showRestoreOnFB="true" deltaVariantMode="false" showFilterConfiguration="false"
						search="onSmartFilterSearch">
						<smartFilterBar:controlConfiguration>
							<smartFilterBar:ControlConfiguration id="SmartFilterConfig" key="Category" visibleInAdvancedArea="true"
								preventInitialDataFetchInValueHelpDialog="false"/>
						</smartFilterBar:controlConfiguration>
					</smartFilterBar:SmartFilterBar>
					<!--<TreeTable id="table" enableColumnFreeze="true" expandFirstLevel="true" visibleRowCountMode="Interactive" fixedRowCount="16"-->
					<!--	columnHeaderVisible="true" visibleRowCount="16" width="98%" selectionMode="None"-->
					<!--	rows="{path:'nodeModel>/nodeRoot', parameters: {arrayNames:['children']}}" enableSelectAll="false" alternateRowColors="true">-->
					<TreeTable id="table" enableColumnFreeze="true" expandFirstLevel="true" visibleRowCountMode="Interactive" 
						columnHeaderVisible="true" width="98%" selectionMode="None"
						rows="{path:'nodeModel>/nodeRoot', parameters: {arrayNames:['children']}}" enableSelectAll="false" alternateRowColors="true">
						<toolbar>
							<m:Toolbar>
								<m:Button id="ShowHierarchyButton" text="ShowHierarchy" type="Transparent" press="onShowHierarchyButtonPress" icon="sap-icon://tree"/>
								<m:Button id="DownloadTableButton" text="Download" type="Transparent" press="onDownloadTableButtonPress" icon="sap-icon://download"/>
							</m:Toolbar>
						</toolbar>
						<columns>
							<!--<Column id="Description_Column" label="Description" width="280px">-->
							<Column id="Description_Column" label="Description" width="280px" autoResizable="true">
								<template>
									<!--<m:Text id="Description_Text" text="{nodeModel>Description}" wrapping="false"/>-->
									<m:HBox alignContent="Inherit">
										<core:Icon src="{ path: 'nodeModel>NodeType',formatter: '.formatter.getIcon'}" tooltip="{nodeModel>NodeType}" width="2rem"/>
										<m:Text text="{nodeModel>Description}"></m:Text>
									</m:HBox>
								</template>
							</Column>
							<!--<Column id="Project_Column" label="Project" width="120px">-->
							<!--<Column id="Project_Column" label="Project" width="100%">-->
							<Column id="Project_Column" label="Project" width="120px" autoResizable="true">
								<template>
									<m:Link id="Project_Link" text="{nodeModel>Project}" press="onProjectPress"/>
								</template>
							</Column>
							<!--<Column id="ProjectDescription_Column" label="ProjectDescription" width="200px">-->
							<Column id="ProjectDescription_Column" label="ProjectDescription" width="200px" autoResizable="true">
								<template>
									<m:Text id="ProjectDescription_Text" text="{nodeModel>ProjectDescription}" wrapping="false"/>
								</template>
							</Column>
							<!--<Column id="WBSElement_Column" label="WBSElement" width="170px">-->
							<!--<Column id="WBSElement_Column" label="WBSElement" width="100%">-->
							<Column id="WBSElement_Column" label="WBSElement" width="170px" autoResizable="true">
								<template>
									<m:Link id="WBSElement_Link" text="{nodeModel>WBSElement}" press="onWBSElementPress"/>
								</template>
							</Column>
							<!--<Column id="WBSDescription_Column" label="WBSDescription" width="150px">-->
							<Column id="WBSDescription_Column" label="WBSDescription" width="200px" autoResizable="true">
								<template>
									<m:Text id="WBSDescription_Text" text="{nodeModel>WBSDescription}" wrapping="false"/>
								</template>
							</Column>
							<!--<Column id="SettlementCostCenter_Column" label="SettlementCostCenter" width="80px">-->
							<Column id="SettlementCostCenter_Column" label="SettlementCostCenter" width="80px" autoResizable="true">
								<template>
									<m:Text id="SettlementCostCenter_Text" text="{nodeModel>SettlementCostCenter}"/>
								</template>
							</Column>
							<!--<Column id="SettlementCostCenterName_Column" label="SettlementCostCenterName" width="150px">-->
							<Column id="SettlementCostCenterName_Column" label="SettlementCostCenterName" width="150px" autoResizable="true">
								<template>
									<m:Text id="SettlementCostCenterName_Text" text="{nodeModel>SettlementCostCenterName}"/>
								</template>
							</Column>
							<Column id="ActiveFlag_Column" label="ACTIVE/INACTIVE" width="120px" >
								<template>
									<m:Text id="ActiveFlag_Text" text="{nodeModel>ActiveFlag}" wrapping="false"/>
								</template>
							</Column>


							<Column id="ProjectCreatedByUser_Column" label="ProjectCreatedByUser" width="120px" >
								<template>
									<m:Text id="ProjectCreatedByUser_Text" text="{nodeModel>ProjectCreatedByUser}" wrapping="false"/>
								</template>
							</Column>
							<Column id="ProjectCreationDate_Column" label="ProjectCreationDate" width="150px" >
								<template>
									<m:Text id="ProjectCreationDate_Text" text="{nodeModel>ProjectCreationDate}" wrapping="false"/>
								</template>
							</Column>
							<Column id="WBSCreatedByUser_Column" label="WBSCreatedByUser" width="120px" >
								<template>
									<m:Text id="WBSCreatedByUser_Text" text="{nodeModel>WBSCreatedByUser}" wrapping="false"/>
								</template>
							</Column>
							<Column id="WBSCreationDate_Column" label="WBSCreationDate" width="150px" >
								<template>
									<m:Text id="WBSCreationDate_Text" text="{nodeModel>WBSCreationDate}" wrapping="false"/>
								</template>
							</Column>




							<Column id="Counter_Column" label="Counter">
								<template>
									<m:Text id="Counter_Text" text="{nodeModel>Counter}" wrapping="false"/>
								</template>
							</Column>
							<Column id="ParentCounter_Column" label="ParentCounter">
								<template>
									<m:Text id="ParentCounter_Text" text="{nodeModel>ParentCounter}" wrapping="false"/>
								</template>
							</Column>
							<Column id="HierarchyLevel_Column" label="HierarchyLevel">
								<template>
									<m:Text id="HierarchyLevel_Text" text="{nodeModel>HierarchyLevel}" wrapping="false"/>
								</template>
							</Column>
							<Column id="NodeID_Column" label="NodeID">
								<template>
									<m:Text id="NodeID_Text" text="{nodeModel>NodeID}" wrapping="false"/>
								</template>
							</Column>
							<Column id="ParentNodeID_Column" label="ParentNodeID">
								<template>
									<m:Text id="ParentNodeID_Text" text="{nodeModel>ParentNodeID}" wrapping="false"/>
								</template>
							</Column>
							<Column id="NodeType_Column" label="NodeType">
								<template>
									<m:Text id="NodeType_Text" text="{nodeModel>NodeType}" wrapping="false"/>
								</template>
							</Column>
						</columns>
					</TreeTable>
				</m:items>
			</m:VBox>
		</semantic:content>
	</semantic:SemanticPage>
</mvc:View>

 

Here is the App.controller.js.

These methods may come in handy in your SAPUI5 coding for rendering the hierarchical data in the tree view, forward navigation to the subsequent app for costs, and the Project and WBS semantic objects, dynamic filter manipulation logic, and export data to excel functionality :

  • _transformFlatToDeepData method – to transform data to tree structure with ParentCounter and Counter of the each Node – this is used to render the tree view
  • onProjectPress and onWBSElementPress – for the CrossApplicationNavigation external navigation
  • onDownloadTableButtonPress – to download to excel
  • _getFilters – to get the filters from the UI and also change them on the fly – there is some nice tricks I have used here to get around the standard SAP functionality

 

sap.ui.define([
	"sap/ui/core/mvc/Controller",
	"sap/ui/model/json/JSONModel",
	"sap/ui/model/Filter",
	"sap/ui/model/FilterOperator",
	'sap/ui/export/library',
	'sap/ui/export/Spreadsheet',
	'com/customer/wbs/report/ZRTR_WBS_REPORT/model/formatter'
], function(Controller, JSONModel, Filter, FilterOperator, exportLibrary, Spreadsheet, formatter) {
	"use strict";

	var EdmType = exportLibrary.EdmType;
	
	var oDataResults = {};

	return Controller.extend("com.customer.wbs.report.ZRTR_WBS_REPORT.controller.App", {

		formatter: formatter,

		/* =========================================================== */
		/* lifecycle methods                                           */
		/* =========================================================== */
		/**
		 * Called when the worklist controller is instantiated.
		 * @public
		 */
		onInit: function() {

			//Let's create a List of projects
			var oProjects = {
				Projects : []
			};
			
			var oProjectsModel = new JSONModel(oProjects);
			this.setModel(oProjectsModel,"projectsModel");
			

			// Add the worklist page to the flp routing history
			this.addHistoryEntry({
				title: this.getResourceBundle().getText("title"),
				icon: "sap-icon://table-view",
				intent: "#WBSReport-display"
			}, true); 
			
			//No need for this
			//var dataModel = this.getOwnerComponent().getModel("mainService");
			//this.getView().setModel(dataModel,"DataModel");
			
		},

		/********************************************************************************************************************/
		/**
		 * Convenience method for accessing the router.
		 * @public
		 * @returns {sap.ui.core.routing.Router} the router for this component
		 */
		getRouter: function() {
			return UIComponent.getRouterFor(this);
		},

		/********************************************************************************************************************/
		/**
		 * Convenience method for getting the view model by name.
		 * @public
		 * @param {string} [sName] the model name
		 * @returns {sap.ui.model.Model} the model instance
		 */
		getModel: function(sName) {
			return this.getView().getModel(sName);
		},

		/********************************************************************************************************************/
		/**
		 * Convenience method for setting the view model.
		 * @public
		 * @param {sap.ui.model.Model} oModel the model instance
		 * @param {string} sName the model name
		 * @returns {sap.ui.mvc.View} the view instance
		 */
		setModel: function(oModel, sName) {
			return this.getView().setModel(oModel, sName);
		},

		/********************************************************************************************************************/
		/**
		 * Getter for the resource bundle.
		 * @public
		 * @returns {sap.ui.model.resource.ResourceModel} the resourceModel of the component
		 */
		getResourceBundle: function() {
			return this.getOwnerComponent().getModel("i18n").getResourceBundle();
		},

		/********************************************************************************************************************/
		/**
		 * Adds a history entry in the FLP page history
		 * @public
		 * @param {object} oEntry An entry object to add to the hierachy array as expected from the ShellUIService.setHierarchy method
		 * @param {boolean} bReset If true resets the history before the new entry is added
		 */
		addHistoryEntry: (function() {
			var aHistoryEntries = [];

			return function(oEntry, bReset) {
				if (bReset) {
					aHistoryEntries = [];
				}

				var bInHistory = aHistoryEntries.some(function(oHistoryEntry) {
					return oHistoryEntry.intent === oEntry.intent;
				});

				if (!bInHistory) {
					aHistoryEntries.push(oEntry);

					// if (this.getOwnerComponent().getService("ShellUIService")) {
					// 	this.getOwnerComponent().getService("ShellUIService").then(function(oService) {
					// 		oService.setHierarchy(aHistoryEntries);
					// 	});
					// }
				}
			};
		})(),


		/********************************************************************************************************************/

		// /* =========================================================== */
		// /* event handlers                                              */
		// /* =========================================================== */

		/********************************************************************************************************************/
		/**
		 * onProjectPress handler
		 * @param {sap.ui.base.Event} oEvent 
		 * @private
		 */
		onProjectPress: function(oEvent) {
			var oControl = oEvent.getSource();
			//alert("Project: " + oControl.getText());

			var project = oControl.getText()

			var oCrossAppNavigator = sap.ushell.Container.getService("CrossApplicationNavigation"); // get a handle on the global XAppNav service

			var hash = (oCrossAppNavigator && oCrossAppNavigator.hrefForExternal({
			  target: {
			  semanticObject: "ZProjectWBS",
			  action: "display"
			},
			params: {
			"Project": project
			}
			})) || ""; // generate the Hash to display the project
			
			oCrossAppNavigator.toExternal({
			  target: {
			  shellHash: hash
			}
			}); // navigate to Project WBS Cost application

		},

		/********************************************************************************************************************/
		/**
		 * onWBSElementPress handler
		 * @param {sap.ui.base.Event} oEvent 
		 * @private
		 */
		onWBSElementPress: function(oEvent) {
			var oControl = oEvent.getSource();
			//alert("WBSElement: " + oControl.getText());

			var wbs = oControl.getText()

			var oCrossAppNavigator = sap.ushell.Container.getService("CrossApplicationNavigation"); // get a handle on the global XAppNav service

			var hash = (oCrossAppNavigator && oCrossAppNavigator.hrefForExternal({
			  target: {
			  semanticObject: "ZProjectWBS",
			  action: "display"
			},
			params: {
			"WBSElement": wbs
			}
			})) || ""; // generate the Hash to display the project
			
			oCrossAppNavigator.toExternal({
			  target: {
			  shellHash: hash
			}
			}); // navigate to Project WBS Cost application


		},

		/********************************************************************************************************************/

		onDownloadTableButtonPress: function() {
			//sap.m.MessageToast.show("onDownloadTablePress event handler");

			var aCols, oRowBinding, oSettings, oSheet, oTable;

			if (!this._oTable) {
				this._oTable = this.byId('table');
			}

			oTable = this._oTable;
			oRowBinding = oTable.getBinding("");

			aCols = this._createColumnConfig();

			var oModel = oRowBinding.getModel();

			oSettings = {
				workbook: { columns: aCols },
				dataSource: this.oDataResults
			};

			oSheet = new sap.ui.export.Spreadsheet(oSettings);
			oSheet.build().finally(function() {
				oSheet.destroy();
			});
		},

		/********************************************************************************************************************/

		onShowHierarchyButtonPress: function() {
			//sap.m.MessageToast.show("onShowHierarchyButtonPress event handler");
			
			//Get the project filters
			var oProjectFilterArray = this._getProjectFilters();
			this._invokeODataCall(oProjectFilterArray);
			
		},

		/********************************************************************************************************************/

		onSmartFilterSearch: function(oEvent) {
			//This code was generated by the layout editor.
			//var binding = oEvent.getParameter("bindingParams");
			var oControl = oEvent.getSource();
			//sap.m.MessageToast.show("SmartFilterSearch");

			//
			if (oControl.getFilters()[0]) {
			//	this._SearchCall();
				this._invokeODataCall(this._getFilters());
			} else {
				//sap.m.MessageToast.show("No Filters");
			}
		},

		/********************************************************************************************************************/
		/* =========================================================== */
		/* internal methods                                            */
		/* =========================================================== */



		_padLeadingZeros: function (num, size) {
				var s = num + "";
				while (s.length < size) s = "0" + s;
				return s;
		},


		/********************************************************************************************************************/
		/**
		 * Get Filters - adds the leading zeroes for the SettlementCostCenter
		 * @function
		 * @param inputFilters 
		 * @private
		 */
		 
		_getFilters: function() {

			var oFilters = this.byId("smartFilterBar").getFilters();

			var aFilters = [],
				aFilter, oaFilter, oaaFilter, oaaaFilter;

			for (oaFilter of oFilters) {
				// console.log(oaFilter);
				for (oaaFilter of oaFilter.aFilters) {
					// console.log(oaaFilter);
					// console.log("sPath = " + oaaFilter.sPath + " sOperator = " + oaaFilter.sOperator + " oValue1 = " + oaaFilter.oValue1 + " oValue2 = " + oaaFilter.oValue2);

					if (oaaFilter.sPath == "SettlementCostCenter") {
						// console.log("Need to prepend zeroes for " + oaaFilter.oValue1);
						oaaFilter.oValue1 = this._padLeadingZeros(oaaFilter.oValue1, 10);

					}

					if (oaaFilter.aFilters) {
						for (oaaaFilter of oaaFilter.aFilters) {
							// console.log(oaaaFilter);
							// console.log("sPath = " + oaaaFilter.sPath + " sOperator = " + oaaaFilter.sOperator + " oValue1 = " + oaaaFilter.oValue1 + " oValue2 = " + oaaaFilter.oValue2);

							if (oaaaFilter.sPath == "SettlementCostCenter") {
								// console.log("Need to prepend zeroes for " + oaaaFilter.oValue1);
								oaaaFilter.oValue1 = this._padLeadingZeros(oaaaFilter.oValue1, 10);
							}
						}
					}
				}
			}

			return oFilters;
		},


		/********************************************************************************************************************/
		/**
		 * Invoke OData Call
		 * @function
		 * @param inputFilters 
		 * @private
		 */

		_invokeODataCall: function(inputFilters) {

			var that = this;
			var oTreeTable = this.byId("table");
			oTreeTable.setBusy(true);
			var oModel = that.getOwnerComponent().getModel();

			//Since no name for the model
			var sPath = "/ZRTR_PROJ_WBS_HIERARCHY";

			
			oModel.read(sPath, {
				filters: inputFilters,
				success: function(oData) {
					if (oData) {
						var flatData = oData.results;
						
						// Let's store the results an attribute for export
						that.oDataResults = oData.results;
						// Get's the unique list of projects...
						that._extractUniqueProjectList(flatData);
						var deepData = that._transformFlatToDeepData(flatData);
						that._setModelData(deepData);
						that.byId("table").setBusy(false);
						var oTable = that.byId("table");
						//To dynamically set the column values
						//oTable.getColumns().map((col, index) => oTable.autoResizeColumn(index));

						//oSmartTable.entitySet="ZRTR_PROJ_WBS_HIERARCHY";
						//oSmartTable.rebindTable();
					}
				},
				error: function(oError) {
					console.log(oError);
					alert("Error reading from OData" + oError.responseText);
					sap.m.MessageToast.show("Error reading from OData" + JSON.stringify(oError));
					that.byId("table").setBusy(false);
				}
			});			
			
		},

		/********************************************************************************************************************/
		/**
		 * Get unique list of projects from the results of the OData call and set this as the projectsModel model for use later on
		 * @function
		 * @param nodesIn 
		 * @private
		 */

		_extractUniqueProjectList: function(results) {

			//Let's create a List of projects
			var oProjects = {
				Projects : []
			};
			

			for (var result of results) {
//				This only works if we have a project node...so this is no good
//				if (result.NodeType=="Project") {
//				This logic is to only add
				if (oProjects.Projects.indexOf(result.Project,0)===-1) {
					oProjects.Projects.push(result.Project); 
				}
			}

			var oProjectsModel = new JSONModel(oProjects);
			
			// Save the projects
			this.setModel(oProjectsModel,"projectsModel");
			
		},

		/********************************************************************************************************************/
		/**
		 * Get filters from projectsModel
		 * @function
		 * @param nodesIn 
		 * @private
		 */
		_getProjectFilters: function() {

			var projectsModel = this.getModel("projectsModel");
			
			var projectFilters = [];
			
			for (var project of projectsModel.oData.Projects) { 
				// console.log(project);
	
				var aFilter = new sap.ui.model.Filter({
					path: "Project",
					operator: "EQ",
					value1: project
				});

			projectFilters.push(aFilter);
			}
			
			var mainProjectFilter = new sap.ui.model.Filter({ filters: projectFilters }) 
			mainProjectFilter.bAnd = false;
			mainProjectFilter._bMultiFilter = true;
			
			var arrayOfFilters = [];
			arrayOfFilters.push(mainProjectFilter);
			
			// Return the project filters
			return arrayOfFilters;
		},


		/********************************************************************************************************************/
		/**
		 *Tranform Data to tree structure with ParentCounter and Counter of the each Node
		 * @function
		 * @param nodesIn 
		 * @private
		 */
		_transformFlatToDeepData: function(nodesIn) {
			var nodes = [];
			//'deep' object structure
			var nodeMap = {};
			//'map', each node is an attribute
			if (nodesIn) {
				var nodeOut;
				var parentId;
				for (var i = 0; i < nodesIn.length; i++) {
					var nodeIn = nodesIn[i];
					nodeOut = {
						id: nodeIn.Counter,
						Counter: nodeIn.Counter,
						ParentCounter: nodeIn.ParentCounter,
						Description: nodeIn.Description,
						HierarchyLevel: nodeIn.HierarchyLevel,
						NodeID: nodeIn.NodeID,
						ParentNodeID: nodeIn.ParentNodeID,
						Project: nodeIn.Project,
						ProjectDescription: nodeIn.ProjectDescription,
						WBSElement: nodeIn.WBSElement,
						WBSDescription: nodeIn.WBSDescription,
						SettlementCostCenter: nodeIn.SettlementCostCenter,
						SettlementCostCenterName: nodeIn.SettlementCostCenterName,
						ActiveFlag: nodeIn.ActiveFlag,
						NodeType: nodeIn.NodeType,
						ProjectCreatedByUser: nodeIn.ProjectCreatedByUser,
						ProjectCreationDate: nodeIn.ProjectCreationDate,
						WBSCreatedByUser: nodeIn.WBSCreatedByUser,
						WBSCreationDate: nodeIn.WBSCreationDate,
						children: []
					};
					parentId = nodeIn.ParentCounter;
					if (parentId !== "0" && parentId && parentId.toString.length > 0) {
						//we have a parent, add the node there
						//NB because object references are used, changing the node
						//in the nodeMap changes it in the nodes array too
						//(we rely on parents always appearing before their children)
						var parent = nodeMap[nodeIn.ParentCounter];
						if (parent) {
							parent.children.push(nodeOut);
						} else {
							nodes.push(nodeOut);
						}
					} else {
						//there is no parent, must be top level
						nodes.push(nodeOut);
					}
					//add the node to the node map, which is a simple 1-level list of all nodes
					nodeMap[nodeOut.id] = nodeOut;
				}
			}
			return nodes;
		},

		/********************************************************************************************************************/
		/**
		 *store the nodes in the JSON model, so the view can access them
		 * @function
		 * @param nodes 
		 * @private
		 */
		_setModelData: function(nodes) {
			//store the nodes in the JSON model, so the view can access them
			var nodesModel = new sap.ui.model.json.JSONModel();
			nodesModel.setData({
				nodeRoot: {
					children: nodes
				}
			});
			this.setModel(nodesModel, "nodeModel");
		},

		/********************************************************************************************************************/
		/**
		 * Create Column Configuration for export
		 * @function
		 * @param 
		 * @private
		 */
		_createColumnConfig: function() {
			var aCols = [];

			aCols.push({
				property: 'Description',
				type: EdmType.String
			});

			aCols.push({
				property: 'Project',
				type: EdmType.String
			});

			aCols.push({
				property: 'ProjectDescription',
				type: EdmType.String
			});

			aCols.push({
				property: 'WBSElement',
				type: EdmType.String
			});

			aCols.push({
				property: 'WBSDescription',
				type: EdmType.String
			});

			aCols.push({
				property: 'SettlementCostCenter',
				type: EdmType.String
			});

			aCols.push({
				property: 'SettlementCostCenterName',
				type: EdmType.String
			});

			aCols.push({
				property: 'Counter',
				type: EdmType.String
			});

			aCols.push({
				property: 'ParentCounter',
				type: EdmType.String
			});

			aCols.push({
				property: 'HierarchyLevel',
				type: EdmType.String
			});

			aCols.push({
				property: 'NodeID',
				type: EdmType.String
			});

			aCols.push({
				property: 'ParentNodeID',
				type: EdmType.String
			});

			aCols.push({
				property: 'NodeType',
				type: EdmType.String
			});

			aCols.push({
				property: 'ActiveFlag',
				type: EdmType.String
			});

			aCols.push({
				property: 'ProjectCreatedByUser',
				type: EdmType.String
			});

			aCols.push({
				property: 'ProjectCreationDate',
				type: EdmType.String
			});

			aCols.push({
				property: 'WBSCreatedByUser',
				type: EdmType.String
			});

			aCols.push({
				property: 'WBSCreationDate',
				type: EdmType.String
			});

			return aCols;
		}

	});
});

 

This was a fun project and a lot of neat Fiori/SAP UI5 concepts are being used that can come handy on other custom Fiori projects.  I hope this helps shed some light into developing CDS views with hierarchical data and table functions along with SAP UI5 smart filter and tree tables.

Happy Coding!

All the best!

Jay Malla

Licensed To Code

 

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