Hello everyone, in this tutorial we will learn how to use $filter query option in SAP OData service.
Before proceeding further we assume that you know how to build OData service in sap gateway. Access all SAP OData 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
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.
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 built 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.
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 by providing the max rows.
4. 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.
5. Copy 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/filtered data.
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.
8. 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 1 (or) more fields to $filter query option
Known 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
Congrats! you have successfully learned about $filter query option in SAP OData service and how to implement it in DPC Extension cl