Implementing search the S/4HANA way

An efficient search is one of the most important feature required in an application. This is what allows us to find objects that we want to work on.

In this blog series, me and my colleague Rajesh Khatwa would like to share our experience of implementing search functionality for a custom object built on top of S/4HANA. We used tools and technologies provided out of the box in S/4HANA to minimize the development effort.

Pre-requisite:

Basic knowledge of OData and CDS

Requirement:

Before getting all technical, let’s see what was the motive behind designing the search this way.

A new custom master data object is developed with numerous attributes – both at header level as well as item level. In addition, it is linked to another custom master data object using foreign key relationship.

The requirement is that the application should be able to perform the search and list the results in a list.

The search should support below capability:

1. Ability to specify filter criteria on any number of object attributes
2. Ability to specify sort criteria on any number of object attributes
3. Ability to specify group criteria to show the results in list in a tree like format supporting multi-level grouping
4. Ability to select attributes which should be displayed in result list
5. Ability to further filter result list using a free text search string (without specifying a particular object attribute)

Implementation:

A typical S/4HANA application is built with SAP Fiori as user interface with REST based APIs (in our case OData) calls to backend server to get /post required information.

Let’s see how we can design the ODATA and implement it to provide required results to UI layer.

Development Summary:

A search is essentially a SELECT statement on database.

To enable this, we would create S/4HANA Virtual Data Model (VDM) for our custom object. Essentially, these will be ABAP Core Data Services that will select the data from required database tables.

We would then create a ODATA Service and map its implementation directly to a CDS. This Service Implementation would be implemented using standard layer called SADL (Service Adaption Definition Language). This layer will automatically take the filter/sort criteria from ODATA parameters and convert them to corresponding SQL statement.

One important aspect here is that UI would be implemented in a such a manner that only those object attributes which are selected to be displayed in Search List, would be passed to ODATA parameters. This essentially means that only these fields will be selected using SQL from database and returned to UI. This greatly improves the performance.

Let’s have a look at these steps in detail.

Step 1: Creating VDM for custom object

The first step is to create a Virtual Data Model for your custom object.

This includes creating a CDS view for each of your database tables and create required associations between them. If you are using standard master data in your object like Company Code or Unit of Measurement, create associations with standard CDS provided by SAP as well.

Example:

Let’s assume that we have below Database tables in which object data is stored:

◉ ZOBJHDR (Header table)

◉ ZOBJITM (Item table)

A typical VDM for above database would involves creating CDS as below:

1. ZI_ObjHeader

Create interface CDS ZI_ObjHeader on top of table ZOBJHDR with associations to other CDS (like Text Associations or associations with foreign key relationships to objects like Company Code, Sales Organization).

@AbapCatalog.sqlViewName: 'ZI_V_OBJHEADER'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.type: #INHERITED

@ClientHandling.algorithm: #SESSION_VARIABLE

@EndUserText.label: 'Object Header'

@ObjectModel.usageType.serviceQuality: #B

@ObjectModel.usageType.sizeCategory: #L

@ObjectModel.usageType.dataClass: #TRANSACTIONAL

@VDM.viewType: #BASIC

@ObjectModel.representativeKey: 'ObjectID'

define view ZI_ObjHeader

  as select from zobjhdr

  association [0..1] to I_BusinessPartner     as _SoldToParty         on $projection.SoldToParty = _SoldToParty.BusinessPartner

  association [0..1] to I_CompanyCode         as _CompanyCode         on $projection.CompanyCode = _CompanyCode.CompanyCode

  association [0..1] to I_SalesOrganization   as _SalesOrganization   on $projection.SalesOrganization = _SalesOrganization.SalesOrganization

  association [0..1] to I_DistributionChannel as _DistributionChannel on $projection.DistributionChannel = _DistributionChannel.DistributionChannel

  association [0..1] to I_Division            as _Division            on $projection.Division = _Division.Division

  association [1..1] to I_User                as _CreatedByUser       on $projection.CreatedByUser = _CreatedByUser.UserID

  association [1..1] to I_User                as _ChangedByUser       on $projection.LastChangedByUser = _ChangedByUser.UserID

  association [0..*] to ZI_ObjItem            as _ObjectItems         on $projection.DBKey = _ObjectItems.ObjectID

{

  key db_key        as DBKey,

      obj_id        as ObjectID,

      obj_type      as ObjectType,

      @ObjectModel.foreignKey.association: '_CompanyCode'

      ccode         as CompanyCode,

      @ObjectModel.foreignKey.association: '_SalesOrganization'

      vkorg         as SalesOrganization,

      @ObjectModel.foreignKey.association: '_DistributionChannel'

      vtweg         as DistributionChannel,

      @ObjectModel.foreignKey.association: '_Division'

      spart         as Division,

      @ObjectModel.foreignKey.association: '_SoldToParty'

      sold_to_party as SoldToParty,

      @ObjectModel.foreignKey.association: '_CreatedByUser'

      created_by    as CreatedByUser,

      created_on    as CreatedOnDateTime,

      valid_from    as ValidFromDateTime,

      valid_to      as ValidToDateTime,

      status        as Status,

      @ObjectModel.foreignKey.association: '_ChangedByUser'

      changed_by    as LastChangedByUser,

      changed_on    as LastChangedOnDateTime,

      /* Associations */

      _CompanyCode,

      _SalesOrganization,

      _DistributionChannel,

      _Division,

      _ChangedByUser,

      _CreatedByUser,

      _SoldToParty,

      _ObjectItems

}

2. ZI_ObjItem

Create interface CDS ZI_ObjItem on top of table ZOBJITM with foreign key association to ZI_ObjHeader

@AbapCatalog.sqlViewName: 'ZI_V_OBJITEM'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.type: #INHERITED

@ClientHandling.algorithm: #SESSION_VARIABLE

@EndUserText.label: 'Object Items'

@ObjectModel.usageType.serviceQuality: #B

@ObjectModel.usageType.sizeCategory: #L

@ObjectModel.usageType.dataClass: #TRANSACTIONAL

@VDM.viewType: #BASIC

define view ZI_ObjItem

  as select from zobjitm

  association [1..1] to ZI_ObjHeader    as _ObjectHeader  on $projection.ObjectID = _ObjectHeader.DBKey

  association [1..1] to I_UnitOfMeasure as _UnitOfMeasure on $projection.UnitOfMeasure = _UnitOfMeasure.UnitOfMeasure

{

  db_key     as ObjectItemKey,

  parent_key as ObjectID,

  item_num   as ObjectItemNum,

  products   as Products,

  volume     as Volume,

  uom        as UnitOfMeasure,

  start_date as StartDate,

  end_date   as EndDate,

  created_by as CreatedBy,

  created_on as CreatedOn,

  changed_by as ChangedBy,

  changed_on as ChangedOn,

  /* Associations */

  _ObjectHeader

}

3. ZI_Object

Create interface CDS ZI_Object on top of CDS ZI_ObjHeader with exposed associations to ZI_ObjItem and ZI_ObjHeader

@AbapCatalog.sqlViewName: 'ZI_V_OBJECT'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.type: #INHERITED

@ClientHandling.algorithm: #SESSION_VARIABLE

@EndUserText.label: 'Objects'

@ObjectModel.usageType.serviceQuality: #B

@ObjectModel.usageType.sizeCategory: #L

@ObjectModel.usageType.dataClass: #TRANSACTIONAL

@VDM.viewType: #BASIC

define view ZI_Object

  as select from ZI_ObjHeader

{

  //ZI_ObjHeader

  DBKey,

  ObjectID,

  ObjectType,

  @ObjectModel.foreignKey.association : '_CompanyCode'

  CompanyCode,

  @ObjectModel.foreignKey.association : '_SalesOrganization'

  SalesOrganization,

  @ObjectModel.foreignKey.association : '_DistributionChannel'

  DistributionChannel,

  @ObjectModel.foreignKey.association : '_Division'

  Division,

  @ObjectModel.foreignKey.association : '_SoldToParty'

  SoldToParty,

  @ObjectModel.foreignKey.association : '_CreatedByUser'

  CreatedByUser,

  CreatedOnDateTime,

  ValidFromDateTime,

  ValidToDateTime,

  Status,

  @ObjectModel.foreignKey.association : '_ChangedByUser'

  LastChangedByUser,

  LastChangedOnDateTime,

  /* Associations */

  _ChangedByUser,

  _CompanyCode,

  _CreatedByUser,

  _DistributionChannel,

  _Division,

  _ObjectItems,

  _SalesOrganization,

  _SoldToParty

}

Step 2a: Create List CDS “ZC_ObjList”

Once we have a VDM created for our custom object, we next move to create a CDS specific for object search.

Create a new Consumption CDS ZC_ObjList on top of CDS ZI_Object with all fields in field section of ZI_Object and any exposed associations.

Furthermore, based on need, additional fields can be added to this CDS as needed on Search List UI. For e.g. In our implementation, we added Date fields by converting them from Timestamp fields. For this, the Current Date and TimeZone have been made as parameters. During ODATA implementation (part 2 of this blog), we will see how to pass the data to these parameters.

@AbapCatalog.sqlViewName: 'ZC_V_OBJLIST'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Object List'

@ObjectModel.usageType.serviceQuality: #C

@ObjectModel.usageType.sizeCategory: #L

@ObjectModel.usageType.dataClass: #TRANSACTIONAL

@VDM.viewType: #CONSUMPTION

define view ZC_ObjList

  with parameters

    p_date  : abap.dats, //Current Date

    p_tzone : suidtzone  //Timezone in which the Current Date lies

  as select from ZI_Object

{

  //ZI_Object

  DBKey,

  ObjectID,

  ObjectType,

  @ObjectModel.foreignKey.association : '_CompanyCode'

  CompanyCode,

  @ObjectModel.foreignKey.association : '_SalesOrganization'

  SalesOrganization,

  @ObjectModel.foreignKey.association : '_DistributionChannel'

  DistributionChannel,

  @ObjectModel.foreignKey.association : '_Division'

  Division,

  @ObjectModel.foreignKey.association : '_SoldToParty'

  SoldToParty,

  @ObjectModel.foreignKey.association : '_CreatedByUser'

  CreatedByUser,

  CreatedOnDateTime,

  ValidFromDateTime,

  ValidToDateTime,

  Status,

  LastChangedByUser,

  LastChangedOnDateTime,

  /* Date-Time Calculations */

  

  @Semantics.businessDate.from: true

  cast ( tstmp_to_dats( ValidFromDateTime, :p_tzone,

                         $session.client, 'NULL' )  as datum )  as ValidFromDate,

  @Semantics.businessDate.to: true

  cast ( tstmp_to_dats( ValidToDateTime, :p_tzone,

                         $session.client, 'NULL' )  as datum )  as ValidToDate,

  /* Associations */

  //ZI_Object

  _ChangedByUser,

  _CompanyCode,

  _CreatedByUser,

  _DistributionChannel,

  _Division,

  _ObjectItems,

  _SalesOrganization,

  _SoldToParty

}

Step 2b: Create Expanded CDS “ZC_ObjListExpanded”

All the CDS created till now have exposed associations so that data is selected from associated CDS only when fields are actually required from them. These will be basis of our selection from database (more on that later).

However, we still need a structure which has all the possible object attributes which can possibly be part of Search List (or can have Filter, Sort, or Group on them). These can be either directly from header or from item/subitem or from other objects linked using foreign key associations.

To do so, create a new Consumption CDS ZC_ObjListExpanded on top of CDS ZC_ObjList. Include all possible fields, either coming directly from ZC_ObjList or from exposed associations. There would be no exposed associations in this CDS.

@AbapCatalog.sqlViewName: 'ZV_OBJ_EXP'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Object List Expanded'

define view ZC_ObjListExpanded

  with parameters

    p_date  : abap.dats,

    p_tzone : suidtzone

  as select from ZC_ObjList( p_date: :p_date, p_tzone: :p_tzone )

  //Date is not needed to be passed, as the purpose of this CDS is just to have a ABAP DDIC structure

{

      //A new key Generated_Id is created

      //This key is required by Analytical Table on UI Layer

      @Consumption.hidden: true

  key cast(( ObjectID ) as abap.sstring(1000) ) as GeneratedId,

      //ZC_ObjList

      @Consumption.hidden: true

      DBKey,

      @Search.defaultSearchElement: true

      ObjectID,

      ObjectType,

      @Search.defaultSearchElement: true

      CompanyCode,

      @Search.defaultSearchElement: true

      SalesOrganization,

      @Search.defaultSearchElement: true

      DistributionChannel,

      Division,

      SoldToParty,

      @Semantics.user.createdBy: true

      CreatedByUser,

      @Semantics.businessDate.from: true

      CreatedOnDateTime,

      @Semantics.businessDate.from: true

      ValidFromDateTime,

      @Semantics.businessDate.from: true

      ValidToDateTime,

      Status,

      LastChangedByUser,

      @Semantics.businessDate.from: true

      LastChangedOnDateTime,

      @Semantics.businessDate.from: true

      ValidFromDate,

      @Semantics.businessDate.from: true

      ValidToDate,

      @Semantics.text: true

      @Search.defaultSearchElement: true

      _SoldToParty.BusinessPartnerFullName                                                                                                                                                                              as SoldToPartyName,

      @Consumption.hidden: true

      _SoldToParty.FirstName                                                                                                                                                                                            as SoldToPartyFirstName,

      @Consumption.hidden: true

      _SoldToParty.LastName                                                                                                                                                                                             as SoldToPartyLastName,

      @Consumption.hidden: true

      _SoldToParty.OrganizationBPName1                                                                                                                                                                                  as SoldToPartyOrgName1,

      @Consumption.hidden: true

      _SoldToParty.OrganizationBPName2                                                                                                                                                                                  as SoldToPartyOrgName2,

      @Consumption.hidden: true

      _SoldToParty.GroupBusinessPartnerName1                                                                                                                                                                            as SoldToPartyGroupName1,

      @Consumption.hidden: true

      _SoldToParty.GroupBusinessPartnerName2,

      @Search.defaultSearchElement: true

      _SalesOrganization._Text[1: Language = $session.system_language ].SalesOrganizationName                                                                                                                           as SalesOrganizationName,

      @Search.defaultSearchElement: true

      _CompanyCode.CompanyCodeName                                                                                                                                                                                      as CompanyCodeName,

      @Search.defaultSearchElement: true

      _DistributionChannel._Text[1: Language = $session.system_language ].DistributionChannelName                                                                                                                       as DistributionChannelName,

      @Search.defaultSearchElement: true

      _Division._Text[1: Language = $session.system_language ].DivisionName,

      @Consumption.hidden: true

      _ObjectItems. ObjectItemKey,

      @Consumption.hidden: true

      _ObjectItems.ObjectID                                                                                                                                                                                             as ItemParentKey,

      @Consumption.hidden: true

      _ObjectItems. ObjectItemNum,

      _ObjectItems. Products,

      @DefaultAggregation:#SUM

      _ObjectItems. Volume,

      _ObjectItems. UnitOfMeasure,

      _ObjectItems. StartDate,

      _ObjectItems. EndDate,

      @Consumption.hidden: true

      _ObjectItems. CreatedBy,

      @Consumption.hidden: true

      _ObjectItems. CreatedOn,

      @Consumption.hidden: true

      _ObjectItems. ChangedBy,

      @Consumption.hidden: true

      _ObjectItems. ChangedOn

}

Points to Note:

◉ The primary key field is renamed as GENERATED_ID. This is because this data will be shown using Analytical Table control on UI. This table expects only one key field with this name if group by has to work seamlessly.

◉ ODATA Metadata can be manipulated using the annotations defined in CDS. This will be detailed in subsequent steps.

Step 3: Create a SAP Gateway project having OData Entity using the Expanded CDS

Create a new SAP Gateway project ‘Z_OBJECTS’ . Right click on Data Model->Import->DDIC Structure . Create the Entity ‘ ObjectList’ and Entity Set ‘ObjectListSet’ using the DDIC Structure ‘ZV_OBJ_EXP’.

‘ZV_OBJ_EXP’ is the SQL view generated from the CDS ‘ZC_ObjListExpanded’.

Step 4: Map the Service Implementation of OData Entity to List CDS

In your SAP Gateway project, expand node Service Implementation -> ObjectListSet. Right-click on entity set and choose “Map to Data Source”.

Choose Type “4 Business Entity” and press F4 in the field ‘Name’. Choose SADL Model Type CDS and SADL Model: ZC_ObjList.

Please note that for the creation of the OData properties, we had used the SQL view generated from the CDS ZC_ObjListExpanded but while mapping the data source we are using CDS ZC_ObjList.

ZC_ObjListExpanded was used for OData properties creation, since we want all the fields from Header as well as Item, to be shown in a single table on UI.

Drag and drop the elements of the mapped Business Entity ZC_ObjList to the mapping table. The property ‘Generatedid’ does not need any mapping information. Leave the mapping element empty.

Register the service ‘Z_OBJECTS_SRV’ generated using Transaction Code: /IWFND/MAINT_SERVICE.