$filter Query Option in SAP OData Service

1
10755

[adsenseyu2]

Hello everyone, in this tutorial we will learn how to use query option $filter in SAP netweaver gateway OData service. Before proceeding further we assume that you know how to build OData service in sap gateway.Access all SAP Netweaver Gateway tutorials here.Lets get started

What $filter query option does ?

Query option $filter adds filtering capabilities to the SAP gateway OData service. So that you can able to filter the OData service feed/collection based on the fields available in the Entity Type set.

Supported System Version

SAP NetWeaver Gateway Release 2.0 Support Package >=03

Business Example

A gateway OData service is getting all the products from the back-end system, but you don’t want to get all the products and you want to apply filters on the OData service so that required products are retrieved.

Syntax

http://<server>:<port>/sap/opu/odata/sap/<service_name>/ProductsSet?$filter=ProductId eq ‘HT-1000’

$filter = <fieldname> <operator> <value> , where:

  • <fieldname> is the name of the field.

  • <operator> must be from the list of supported operators.

  • <value> can be a string value and should be enclosed in quotation marks (‘value’).

How to Implement $filter query option in OData Service

In this section we will adjust the OData service to respond to $filter query option. $filter query option should be applied only to Entity Type sets. We assume that you have already build the OData service to get the list of products. if you have not click here.

1. After successful creation of OData service. Go back to service builder SEGW, Navigate to the ABAP workbench for the Product Entity set.

Go to ABAP Workbench

2. Code inside the method PRODUCTSSET_GET_ENTITYSET will look like below i.e before adjusting the service to $filter query option.

DATA: lt_products  TYPE STANDARD TABLE OF bapi_epm_product_header,
      ls_products  TYPE bapi_epm_product_header,
      es_entityset TYPE zcl_zdemo_gw_srv_mpc=>ts_products,
      lv_max_rows  TYPE bapi_epm_max_rows.

    lv_max_rows-bapimaxrow = 10.
    CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST'
      EXPORTING
        max_rows   = lv_max_rows
      TABLES
        headerdata = lt_products.
    IF lt_products IS NOT INITIAL.
      LOOP AT lt_products INTO ls_products.
        MOVE-CORRESPONDING ls_products TO es_entityset.
        APPEND es_entityset TO et_entityset.
      ENDLOOP.
    ENDIF.

3. In the above code snippet, we have retrieved the list of products generally by providing the max rows.Now we will adjust the code to adapt it to the $filter query option. Filter query option is available in the method by accessing the importing parameter IT_FILTER_SELECT_OPTIONS.

4. Put the below code in the method PRODUCTSSET_GET_ENTITYSET to repond to the $filter query option.

   DATA:   ls_filter         TYPE /iwbep/s_mgw_select_option,
           lt_products       TYPE STANDARD TABLE OF bapi_epm_product_header,
           ls_products       TYPE bapi_epm_product_header,
           es_entityset      TYPE zcl_zdemo_gw_srv_mpc=>ts_products,
           lr_product_id     TYPE TABLE OF  bapi_epm_product_id_range,
           ls_product_id     TYPE bapi_epm_product_id_range,
           ls_select_options TYPE /iwbep/s_cod_select_option.

* Get the filter option for product ID
    READ TABLE it_filter_select_options
        INTO ls_filter
        WITH KEY property = 'ProductId'.
    IF sy-subrc = 0.
      LOOP AT ls_filter-select_options INTO ls_select_options.
        MOVE-CORRESPONDING ls_select_options TO ls_product_id.
        APPEND ls_product_id TO lr_product_id.
      ENDLOOP.
    ENDIF.

* Call the BAPI by providing the filter options
    CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST'
      TABLES
        headerdata        = lt_products
        selparamproductid = lr_product_id.
    IF lt_products IS NOT INITIAL.
      LOOP AT lt_products INTO ls_products.
        MOVE-CORRESPONDING ls_products TO es_entityset.
        APPEND es_entityset TO et_entityset.
      ENDLOOP.
    ENDIF.

5. What we did in the above step.First we got the filter select options for the “ProductId” and pass the select option range to the BAPI to get the required data.

[adsenseyu1]

6. From code perspective we are ready, so lets test the service by using the sap gateway client /IWFND/GW_CLIENT.

7. If we execute the service without adding $filer query option, then we will get all the products available in the system. If we add the $filter query option to the service our service will respond to that and it will give the results based on the filters provided. Test the service by providing the following URI with $filter on ProductId = HT-1000.

/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/ProductsSet?$filter=ProductId eq ‘HT-1000’.

8. Check the collection/feed, now you should get only the product what we have given in the filter. You can add N number fields with $filter query option.

Gateway ClientKnown Constraints on $filter query option

There are currently some limitations for query options $filter. Logical Operators supported as of now are  ‘eq’ , ‘ne’ , ‘le’ , ‘lt’ , ‘ge’ , ‘gt’ . startswith and endwith is not supported.Full list of supported options are listed in https://service.sap.com/sap/support/notes/1574568

Please stay tuned to us for more SAP Netweaver Gateway/OData tutorials.Please feel free to comment and let us know your feedback. You feedback will keep us alive.

like-128

  • Christian Sislak

    But how do I call this via UI5 like: var oModel2 = new sap.ui.model.odata.ODataModel(“/sap/opu/odata/sap/Z_ZMAIN_DBD_DASHBOARD_TEST_SRV/EtDbDatenQ1AnzSet?$filter=Datum eq ‘20160212’”, true);

    This call doesn’t work for me.