Skip to Content
Actions

How to Develop Query Options for an OData Service Using Code-Based Implementation

June 20, 2013 | 33,918 Views |
 former jive documentsap netweaver gateway
Follow RSS

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

    /wp-content/uploads/2013/06/image002_234461.png

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

    /wp-content/uploads/2013/06/image003_234462.png

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

    /wp-content/uploads/2013/06/image004_234465.png

  5. Press the Generate button 

    /wp-content/uploads/2013/06/image005_234464.png

  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.

/wp-content/uploads/2013/06/image006_234466.png

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.     

    /wp-content/uploads/2013/06/image007_234471.png

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

To report this post you need to login first.

20 Comments

You must be Logged on to comment or reply to a post.

  2. Steffen Froehlich

    Hi Andre,

    nice tutorial on filtering and paging capabilities for NW Gateway.

    I have a question on IO_TECH_REQUEST_CONTEXT parameter. I read as of SP05 this parameter should be used to retrieve parameters send via HTTP request. In get_entity method, if you I want to retrieve all keys from URI, I can use GET_KEYS(). But in get_entityset this method doesn’t exist for reference parameter IO_TECH_REQUEST_CONTEXT.

    If I need to get an entityset via navigation (URI: SalesOrders(‘1’)/Items) I cannot use GET_KEYS() method, because it’s not there. But KEY_TAB table for object IO_TECH_REQUEST_CONTEXT is filled properly.

    Today I’m solving that doing workaround / old way via method parameter IT_KEY_TAB.

    What is the right way to access key tab in any case?

    P.S.: working currently on a NW GW 2.0 SP06

    —

    Steffen

    (0) 
    1. Andre Fischer Post author

      Hi Steffen,

      you can use the method get_source_keys( ) in   io_tech_request_context

      DATA:

         lt_keys               TYPE              /iwbep/t_mgw_tech_pairs,
              ls_keys               TYPE              /iwbep/s_mgw_tech_pair.

         IF iv_source_name EQ ‘SalesOrder’.
          lt_keys = io_tech_request_context->get_source_keys( ).

      (0) 
      1. Venkat P

        Hi

        I want pass input values to that BAPI which are not get return . how I can map that approach.

        Ex: Input parameters are Sale Org and Company code .

        results are, getting all sales orders for company code and Org.

        Can you please let me know how can I do this.

        (0) 
  4. Kell Vagtholm

    Great work!

    I was able to create my own service this way with GetEntitySet and GetEntity – also added the filtering option.

    Did you make a guide on how to post (create or update) on the same service?

    I need help doing a post or update of data in my service.

    Best regards,

    Kell Vagtholm

    PS: Might also need a guide for the Delete part 🙂

    (0) 
  5. Fahad Ajaib

    Hi Guys,

    How to change the header of the OData Response. I want to allow cross domain request and thats why I need Access-Control-Allow-Origin: * in the response header. Can some one guide me in this regard.

    Thanks and bets regards.

    Fahad

    (0) 
  6. Steffen Froehlich

    Hi Andre,

    I stumbled over your very good blog again and I realized your workaround how to provide table like input data to an OData service. It’s done while filling filter query option.

    Do you think it’s still the best option to “transfer” table data to an OData service which uses this as input data? Maybe in NW 7.4 GW SP6 are better ways to do so (don’t have such a system yet)?

    Currently I’m working in a project where all backend data has to be called via RFCs (Development in the Hub). Most of these RFCs have some fields and one or more tables as input parameter. I try to minimize the effort for client and network pressure to provide it as batch processing. But then I’ve got many unnecessary RFC calls, which could be one. In my case filters could not be sufficient, if I need to support many diverse tables.

    RFC sample definition: It could be asked for specific roles a user has got… (just a very simple example)

    Importing:

    – user TYPE bname

    – roles TYPE ty_roles

    Export:

    – user TYPE bname

    – pernr TYPE pernr

    – workcenter TYPE arbpl

    – roles TYPE ty_roles

    ty_roles consist of (role_id, role, active_flag)

    Some people say, when there’s a need for such data input to get proper values from service back, then it’s a very bad designed service at all. In my understanding that’s truth and such a service didn’t follows REST principals.

    EDIT: ok we’ve done a workaround via Filters and $batch processing to support my requirement. Furthermore it would be possible to send required data in one post message via CREATE_DEEP_ENTITY, but then the payload could be higher in some cases.

    Thanks, regards

    Steffen

    (0) 
  7. Rahul Keshav

    Hi Andre,

    Thanks for the blog, the day i started exploring Odata, i was struggling with Filters, skip,top.

    It was not working… 🙁

    Now i got the code. 😉

    Once again Thanks.

    (0) 
    1. Andre Fischer Post author

      Hi Benedikt,

      sorry for the late reply.

      If you want to implement query options that cannot handled as a filter because they are not a property of the entity set you should use a function import that would accept the query option as a paramter.

      Best Regards,

      Andre

      (0) 
  9. Vinayak Nath Singh

    Hi Andre,

    We are developing an SAPUI5 app with smart filter bar with a couple of filter fields in it. These filter fields are associated with Value help.

    When we apply filter on some filter field and press Go button, in GET_ENTITYSET, io_tech_request_context->get_filter( )->get_filter_select_options( ) is returning the filter values.

    But when we apply some special filter condition(like exclusion using Value help), in GET_ENTITYSET, io_tech_request_context->get_filter( )->get_filter_select_options( ) is not returning the filter values.

     

    (0) 
    1. Rajkumar M

      Hi Vinayak,

      Can you please let me know whether you are using the existing filters or you have added custom filters to the standard app? If you have extended the standard APP (say Monitor Material Coverage) for filters please let me know how to do so..

      Thanks and Regards,
      Rajkumar M

      (0) 
  10. Siddharth Purohit

    HI Andre,

    It’s a great reference document for the filter functionality.

    Do we have any reference article for sorting or $orderby functionality.

    Regards

    Siddharth

    (0) 
  11. Rajkumar M

    Hi Andre,

    Thanks! Your blogs are very much helpful during the development of APPs.

    Can you please let me know is there a possibility to add custom filters to the optional filter option available in the standard APP?

    PFA the snapshot.

    BR,
    Rajkumar M

    (0) 
  12. kranthi kiran

    Hi,

    I have requirement for the to get the Details from a table which consists of 20 Filterable fields, Instead of using the Read Statement and Case is there any alternative for it ?

     

    Regards

    Kranthi

     

     

    (0) 

Leave a Reply