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

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:

SAP S/4 HANA, SAP ABAP Exam Prep, SAP ABAP Certification, SAP ABAP Learning, SAP ABAP Preparation, SAP ABAP Career

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;

* 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"

columnHeaderVisible="true" width="98%" selectionMode="None"

rows="{path:'nodeModel>/nodeRoot', parameters: {arrayNames:['children']}}" enableSelectAll="false" alternateRowColors="true">

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

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

<m:Link id="Project_Link" text="{nodeModel>Project}" press="onProjectPress"/>

</template>

</Column>

<m:Text id="ProjectDescription_Text" text="{nodeModel>ProjectDescription}" wrapping="false"/>

</template>

</Column>

<m:Link id="WBSElement_Link" text="{nodeModel>WBSElement}" press="onWBSElementPress"/>

</template>

</Column>

<m:Text id="WBSDescription_Text" text="{nodeModel>WBSDescription}" wrapping="false"/>

</template>

</Column>

<m:Text id="SettlementCostCenter_Text" text="{nodeModel>SettlementCostCenter}"/>

</template>

</Column>

<m:Text id="SettlementCostCenterName_Text" text="{nodeModel>SettlementCostCenterName}"/>

</template>

</Column>

<m:Text id="ActiveFlag_Text" text="{nodeModel>ActiveFlag}" wrapping="false"/>

</template>

</Column>

<m:Text id="ProjectCreatedByUser_Text" text="{nodeModel>ProjectCreatedByUser}" wrapping="false"/>

</template>

</Column>

<m:Text id="ProjectCreationDate_Text" text="{nodeModel>ProjectCreationDate}" wrapping="false"/>

</template>

</Column>

<m:Text id="WBSCreatedByUser_Text" text="{nodeModel>WBSCreatedByUser}" wrapping="false"/>

</template>

</Column>

<m:Text id="WBSCreationDate_Text" text="{nodeModel>WBSCreationDate}" wrapping="false"/>

</template>

</Column>

<m:Text id="Counter_Text" text="{nodeModel>Counter}" wrapping="false"/>

</template>

</Column>

<m:Text id="ParentCounter_Text" text="{nodeModel>ParentCounter}" wrapping="false"/>

</template>

</Column>

<m:Text id="HierarchyLevel_Text" text="{nodeModel>HierarchyLevel}" wrapping="false"/>

</template>

</Column>

<m:Text id="NodeID_Text" text="{nodeModel>NodeID}" wrapping="false"/>

</template>

</Column>

<m:Text id="ParentNodeID_Text" text="{nodeModel>ParentNodeID}" wrapping="false"/>

</template>

</Column>

<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.

SAP ABAP Central

Pages

Friday 19 March 2021

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

No comments:

Post a Comment