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.