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

[jmalla]

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.

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 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 I-000066

 

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

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 projects

 

Here is the WBS Element value help:

WBS Element value help

 

Here is the value help for the settlement cost center:

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 for Smart Filter and Smart Table

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

 

You can connect with me at - Jay Malla on LinkedIn