$filter query in SAP OData Service

05/19/2015

94009

Hello everyone, in this tutorial we will learn how to use $filter query 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 in SAP OData?

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

This enables you to retrieve only the relevant data you need, improving performance and reducing the response size.
You can construct $filter queries using logical operators (like eq, ne, gt, lt, and, or) that operate on the entity’s properties.

Supported System Version

SAP Gateway release 2.0 Support Package >=03

Business Example

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

<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’).

Examples:

To retrieve SalesOrder entities with an ID of 12345:

/SalesOrderSet?$filter=OrderID eq 12345

To retrieve Product entities with a price greater than $100:

/ProductSet?$filter=Price gt 100

To retrieve Invoice entities with a status of “Shipped” or “Delivered”:

/InvoiceSet?$filter=(Status eq 'Shipped' or Status eq 'Delivered')

Here are a few more examples demonstrating how to use the $filter query option in SAP OData services:

Basic Filtering:

/EntitySet?$filter=PropertyName eq 'Value'

This example filters the entities in the “EntitySet” where the property “PropertyName” is equal to the specified value ‘Value’.

Filter with Logical Operators:

/EntitySet?$filter=Property1 eq 'Value' and Property2 ne 'AnotherValue'

You can use logical operators like and, or, and not to combine multiple conditions for more complex filtering.

Numeric Filtering:

/EntitySet?$filter=NumericProperty gt 100

This example filters entities where the numeric property “NumericProperty” is greater than 100.

Date Filtering:

/EntitySet?$filter=DateProperty ge 2022-01-01

For date properties, you can use comparison operators like ge (greater than or equal to) to filter based on date values.

String Functions:

/EntitySet?$filter=substringof('substring', StringProperty)

The substringof function can be used to check if a substring is present in a string property.

Combining Filters:

/EntitySet?$filter=(Property1 eq 'Value' or Property2 ne 'AnotherValue') and NumericProperty gt 50

You can combine multiple filters using parentheses to create complex filtering conditions.

Filter with DateTime Functions:

/EntitySet?$filter=year(DateProperty) eq 2022

Date and time functions can be used to extract components like year, month, etc., for filtering based on specific criteria.

Filter with Navigation Property:

/EntitySet('Key')/NavigationProperty?$filter=RelatedEntity/RelatedProperty eq 'Value'

If you have navigation properties, you can filter based on related entities and their properties.

How to Implement $filter query in OData Service

In this section we will adjust the OData service to respond to $filter query. The $filter query 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 filtered data.

6. From code perspective we are ready, so lets test the service by using the SAP Gateway Client transaction code /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 response feed, now you should get only the products matching the specified filter. You can add 1 (or) more fields to $filter query

Limitations on $filter query

There are currently some limitations for query options $filter. Logical Operators supported as of now are ‘eq’ , ‘ne’ , ‘le’ , ‘lt’ , ‘ge’ , ‘gt’ .

Comparison operators “startswith” and “endwith” is not supported. Full list of supported options are listed in https://service.sap.com/sap/support/notes/1574568