HOW TO GUIDE OBJECTIVES

In this how-to guide, you will learn how to add filtering capabilities to an OData service with SAP NetWeaver Gateway that is built using code-based implementation. This how-to guide assumes that you have gone through the following how-to guide “How to Develop a Gateway Service using Code based Implementation” https://scn.sap.com/docs/DOC-43030 which describes the basic implementation steps of the service that we are now going to enhance.

The sample service leverages demo data from the Enterprise Procurement Model which is part of every SAP NetWeaver ABAP server as of 7.02.

After completing this lesson, you will be able to:

• Retrieve query options that are passed to an OData service (GW)
• Implement filtering capabilities in the GET_ENTITYSET method in the data provider extension class
• Implement client side paging capabilities in the GET_ENTITYSET method in the data provider extension class

Business Example

You want to build an application that shows a list of products that supports filtering for certain properties and that supports client-side paging.

PERQUISITES

You have implemented an OData service as described in the how-to guide  “How to Develop a Gateway Service using Code based Implementation”   https://scn.sap.com/docs/DOC-43030

Overview of Tasks

In this how-to guide, you will implement filtering capabilities to the attributes ProductID, Supplier Name, Category and Price to the entity set Products. In addition you will add $skip and $top support to the entity set Products.

Task 1: Set the sap:filterable attribute for the properties ProductID, Supplier Name, Category and Price in the entity type Product 

1. Open the project ZGW_PRODUCT in the Service Builder and maintain the filterable flag for the above mentioned properties
2. Regenerate the project and check the $metadata document

Task 2: Add a filter for the attributes ProductID, Supplier Name, Category and Price 

1. Add the function to support a filter on company name to method ZCL_GW_PRODUCT_DPC_EXT->PRODUCTS_GET_ENTITYSET.
2. Request all products with a product ID between A and D. 

Hints:

• Use io_tech_request_context->get_filter( )->get_filter_select_options( ) to get the filter parameters in the format of select options. (data type /iwbep/t_mgw_select_option)
• The field property contains the field name for which the filter needs to be applied.
• Use the parameter SELPARAMPRODUCTID of BAPI_EPM_PRODUCT_GET_LIST to filter the product ID’s
• Use the parameter SELPARAMSUPPLIERNAMES of BAPI_EPM_PRODUCT_GET_LIST to filter the supplier names
• Use the parameter SELPARAMCATEGORIES of BAPI_EPM_PRODUCT_GET_LIST to filter the product categories
• Since the BAPI does not allow for filtering of products you have to apply the filter for the price on the data that is retrieved by the BAPI after applying the other select parameters before returning the result set ET_ENTITYSET to the consumer.
• Use the URI /sap/opu/odata/sap/ZGW_PRODUCT_SRV/Products?$filter=ProductID ge ‘A’ and ProductID le ‘C’ 

Task 3: Add client requested paging 

1. Add the function to support $top and $skip to method PRODUCTS_GET_ENTITYSET in the extension class of the data provider class ZCL_GW_PRODUCT_DPC_EXT. 

Hints:

• The method io_tech_request_context->get_top( ) returns the $top value.
• io_tech_request_context->get_skip( ) returns the $skip value
• Use the parameter max_rows of BAPI_EPM_PRODUCT_GET_LIST to limit the number of records that are selected from the database.

How To Section

Task 1: Add sap:filterable annotation to properties

1. Start transaction SEGW
2. If the project ZGW_PRODUCT is not yet opened select Open Project

   

3. Enter the project name ZGW_PRODUCT in the Open Project dialogue box

   

4. Expand the node Entity Types and then the node Product and double click on Product

   

5. Press the Generate button 

   

6. Check the $metadata document by running the Gateway client and entering the request URI
   /sap/opu/odata/sap/ZGW_PRODUCT_SRV/$metadata

Please note:

When checking the $metadata document you will find no entry such as sap:filterable = “true” for the properties you have maintained previously.  However you will notice the setting sap:filterable = “false” being active for those properties that you have not maintained.

Task 2: Add a filter for the attributes ProductID, Supplier Name, Category and Price

Step:  Implement a query with filter options.

1. Expand Service Implementation, then expand Products and right click on GetEntitySet (Query) and select Go to ABAP Workbench.     

   

2. This will open automatically open the method ZCL_GW_PRODUCT_DPC_EXT->PRODUCTS_GET_ENTITYSET in SE80.
   (Alternatively you can open the class ZCL_ZGW_PRODUCT_DPC_EXT by expanding the node Runtime Artifacts and double-click Runtime Artifacts)
3. Ensure you are in edit mode (Ctrl+F1)
4. Replace the existing coding in the method by the coding below. 

   What has been added in the code to support $filter for the properties ProductID, Supplier Name, Category and Price ?

method PRODUCTS_GET_ENTITYSET.

  DATA: ls_data               LIKE LINE OF           et_entityset,
       lt_headerdata         TYPE STANDARD TABLE OF bapi_epm_bp_header,
       ls_headerdata         TYPE                   bapi_epm_bp_header,
       lv_maxrows            TYPE                   bapi_epm_max_rows,
       lv_top                TYPE                   string,
       lv_skip               TYPE                   I,
       lv_skiptoken          TYPE                   string,
       lt_return             TYPE TABLE OF          bapiret2,
       lo_message_container  TYPE REF TO            /iwbep/if_message_container,
       lt_filters            TYPE                   /iwbep/t_mgw_select_option,
       ls_filter             TYPE                   /iwbep/s_mgw_select_option,
       ls_so                 TYPE                   /iwbep/s_cod_select_option,
       lt_product_id         TYPE TABLE OF          BAPI_EPM_PRODUCT_ID_RANGE,
       ls_product_id         TYPE                   BAPI_EPM_PRODUCT_ID_RANGE,
       lt_supplier_name      TYPE TABLE OF          BAPI_EPM_SUPPLIER_NAME_RANGE,
       ls_supplier_name      TYPE                   BAPI_EPM_SUPPLIER_NAME_RANGE,
       lt_category           TYPE TABLE OF          BAPI_EPM_PRODUCT_CATEG_RANGE,
       ls_category           TYPE                   BAPI_EPM_PRODUCT_CATEG_RANGE
       .

  DATA:   lt_price TYPE RANGE OF BAPI_EPM_PRODUCT_HEADER-PRICE,
          ls_price like LINE OF lt_price.

*-get filter
  lt_filters = io_tech_request_context->get_filter( )->get_filter_select_options( ).

*-get filter for ProductID
  READ TABLE lt_filters WITH TABLE KEY property = ‘PRODUCT_ID’ INTO ls_filter.

  IF sy-subrc EQ 0.
    LOOP AT ls_filter-select_options INTO ls_so.
      MOVE-CORRESPONDING ls_so TO ls_product_id.
      INSERT ls_product_id INTO TABLE lt_product_id.
    ENDLOOP.
  ENDIF.

*-get filter for category
  READ TABLE lt_filters WITH TABLE KEY property = ‘CATEGORY’ INTO ls_filter.

  IF sy-subrc EQ 0.
    LOOP AT ls_filter-select_options INTO ls_so.
      MOVE-CORRESPONDING ls_so TO ls_category.
      INSERT ls_category INTO TABLE lt_category.
    ENDLOOP.
  ENDIF.

*-get filter for supplier name
  READ TABLE lt_filters WITH TABLE KEY property = ‘SUPPLIER_NAME’ INTO ls_filter.

  IF sy-subrc EQ 0.
    LOOP AT ls_filter-select_options INTO ls_so.
      MOVE-CORRESPONDING ls_so TO ls_supplier_name.
      INSERT ls_supplier_name INTO TABLE lt_supplier_name.
    ENDLOOP.
  ENDIF.

    CALL FUNCTION ‘BAPI_EPM_PRODUCT_GET_LIST’
*    EXPORTING
*      MAX_ROWS              = lv_maxrows
      TABLES
        HEADERDATA            = et_entityset
        SELPARAMPRODUCTID     = lt_product_id
        SELPARAMSUPPLIERNAMES = lt_supplier_name
        SELPARAMCATEGORIES    = lt_category
*     RETURN                =
      .

*-get filter for Price
  READ TABLE lt_filters WITH TABLE KEY property = ‘PRICE’ INTO ls_filter.

  IF sy-subrc EQ 0.
    LOOP AT ls_filter-select_options INTO ls_so.
      MOVE-CORRESPONDING ls_so TO ls_price.
      INSERT ls_price INTO TABLE lt_price.
    ENDLOOP.
  ENDIF.

* remove data returned by BAPI that does not match the
* additional filter criteria for price

  DELETE et_entityset where price not IN lt_price.

ENDMETHOD.

Step: Test the service and request all products based on certain selection criteria

1. Look for products with a price higher than 4999 Euro, where the supplier name starts with a letter that is equal or larger than ‘T’ and where the product ID is equal or larger than ‘HT’.

   /sap/opu/odata/sap/ZGW_PRODUCT_SRV/Products?$filter=Price ge 4999 and SupplierName ge ‘T’ and ProductID ge ‘HT’

   The result should be only one product with the ID ‘HT-1502’.     

   If you change the query such that ProductID le ‘HT’ does not deliver anything.

<m:properties>

  <d:ProductID>HT-1502</d:ProductID>

  <d:Category>Workstation ensemble</d:Category>

  <d:Name>Server Power Pro</d:Name>

  <d:Description>Dual socket, quad-core processing server with 1644 MHz Front Side Bus with 100Gb connectivity</d:Description>

  <d:SupplierID>100000025</d:SupplierID>

  <d:SupplierName>Tessile Casa Di Roma</d:SupplierName>

  <d:Price>25000.0000</d:Price>

  <d:CurrencyCode>EUR</d:CurrencyCode>

</m:properties>

Task 3: Add client requested paging

Step: Implement $top and $skip

1. What has changed in the coding

1. Retrieve values for $top and $skip

   *- get number of records requested 
   lv_top  = io_tech_request_context->get_top( ). 
   lv_skip = io_tech_request_context->get_skip( ).
   

2. Calculate value for maxrows only if $top has been passed as a query parameter        

        *- value for maxrows must only be calculated if the request also contains a $top

        IF lv_top IS NOT INITIAL.

           lv_maxrows-bapimaxrow = lv_top + lv_skip.

        ENDIF.

1. Remove entries from result set that have to be skipped   

   IF lv_skip IS NOT INITIAL.     
      DELETE et_entityset TO lv_skip. 
   endif.
   

2.     As in the previous tasks you may copy the code from the following code snippet:

method PRODUCTS_GET_ENTITYSET.

  DATA: ls_data               LIKE LINE OF           et_entityset,
       lt_headerdata         TYPE STANDARD TABLE OF bapi_epm_bp_header,
       ls_headerdata         TYPE                   bapi_epm_bp_header,
       lv_maxrows            TYPE                   bapi_epm_max_rows,
       lv_top                TYPE                   string,
       lv_skip               TYPE                   I,
       lv_skiptoken          TYPE                   string,
       lt_return             TYPE TABLE OF          bapiret2,
       lo_message_container  TYPE REF TO            /iwbep/if_message_container,
       lt_filters            TYPE                   /iwbep/t_mgw_select_option,
       ls_filter             TYPE                   /iwbep/s_mgw_select_option,
       ls_so                 TYPE                   /iwbep/s_cod_select_option,
       lt_product_id         TYPE TABLE OF          BAPI_EPM_PRODUCT_ID_RANGE,
       ls_product_id         TYPE                   BAPI_EPM_PRODUCT_ID_RANGE,
       lt_supplier_name      TYPE TABLE OF          BAPI_EPM_SUPPLIER_NAME_RANGE,
       ls_supplier_name      TYPE                   BAPI_EPM_SUPPLIER_NAME_RANGE,
       lt_category           TYPE TABLE OF          BAPI_EPM_PRODUCT_CATEG_RANGE,
       ls_category           TYPE                   BAPI_EPM_PRODUCT_CATEG_RANGE
       .

  DATA:   lt_price TYPE RANGE OF BAPI_EPM_PRODUCT_HEADER-PRICE,
          ls_price like LINE OF lt_price.

*- get number of records requested
  lv_top = io_tech_request_context->get_top( ).
*- get number of lines that should be skipped
  lv_skip = io_tech_request_context->get_skip( ).
*- value for maxrows must only be calculated if the request also contains a $top
  IF lv_top IS NOT INITIAL.
    lv_maxrows-bapimaxrow = lv_top + lv_skip.
  ENDIF.

*-get filter
  lt_filters = io_tech_request_context->get_filter( )->get_filter_select_options( ).

*-get filter for ProductID
  READ TABLE lt_filters WITH TABLE KEY property = ‘PRODUCT_ID’ INTO ls_filter.

  IF sy-subrc EQ 0.
    LOOP AT ls_filter-select_options INTO ls_so.
      MOVE-CORRESPONDING ls_so TO ls_product_id.
      INSERT ls_product_id INTO TABLE lt_product_id.
    ENDLOOP.
  ENDIF.

*-get filter for category
  READ TABLE lt_filters WITH TABLE KEY property = ‘CATEGORY’ INTO ls_filter.

  IF sy-subrc EQ 0.
    LOOP AT ls_filter-select_options INTO ls_so.
      MOVE-CORRESPONDING ls_so TO ls_category.
      INSERT ls_category INTO TABLE lt_category.
    ENDLOOP.
  ENDIF.

*-get filter for supplier name
  READ TABLE lt_filters WITH TABLE KEY property = ‘SUPPLIER_NAME’ INTO ls_filter.

  IF sy-subrc EQ 0.
    LOOP AT ls_filter-select_options INTO ls_so.
      MOVE-CORRESPONDING ls_so TO ls_supplier_name.
      INSERT ls_supplier_name INTO TABLE lt_supplier_name.
    ENDLOOP.
  ENDIF.

  CALL FUNCTION ‘BAPI_EPM_PRODUCT_GET_LIST’
    EXPORTING
      MAX_ROWS              = lv_maxrows
    TABLES
      HEADERDATA            = et_entityset
      SELPARAMPRODUCTID     = lt_product_id
      SELPARAMSUPPLIERNAMES = lt_supplier_name
      SELPARAMCATEGORIES    = lt_category
*     RETURN                =
    .

*-get filter for Price
  READ TABLE lt_filters WITH TABLE KEY property = ‘PRICE’ INTO ls_filter.

  IF sy-subrc EQ 0.
    LOOP AT ls_filter-select_options INTO ls_so.
      MOVE-CORRESPONDING ls_so TO ls_price.
      INSERT ls_price INTO TABLE lt_price.
    ENDLOOP.
  ENDIF.

* remove data returned by BAPI that does not match the
* additional filter criteria for price

  DELETE et_entityset where price not IN lt_price.

* skipping entries specified by $skip

  IF lv_skip IS NOT INITIAL.
    DELETE et_entityset TO lv_skip.
  endif.

endmethod.

Step: Test the service

1. Start the Gateway client by calling transaction /IWFND/GW_CLIENT
2. Enter the following URI to test your implementation:

   /sap/opu/odata/sap/ZGW_PRODUCT_SRV/Products?$skip=10&$top=3&$select=ProductID

You are done !!! 