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

June 20, 2013 | 40,643 Views |

Andre Fischer

SAP Gateway

former jive document sap netweaver gateway

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

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

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

Add the function to support a filter on company name to method ZCL_GW_PRODUCT_DPC_EXT->PRODUCTS_GET_ENTITYSET.
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

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

Start transaction SEGW
If the project ZGW_PRODUCT is not yet opened select Open Project
Enter the project name ZGW_PRODUCT in the Open Project dialogue box
Expand the node Entity Types and then the node Product and double click on Product
Press the Generate button
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.

Expand Service Implementation, then expand Products and right click on GetEntitySet (Query) and select Go to ABAP Workbench.
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)
Ensure you are in edit mode (Ctrl+F1)
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

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

What has changed in the coding

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

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

Start the Gateway client by calling transaction /IWFND/GW_CLIENT
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.

24 Comments

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

Former Member July 15, 2013 at 7:03 am

Very helpful!

like (0)
Steffen Froehlich July 16, 2013 at 7:26 am

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

like (0)
1. Andre Fischer Post authorJuly 16, 2013 at 9:09 am
  
  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( ).
  
  like (0)
  1. Former Member July 22, 2013 at 7:11 pm
    
    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.
    
    like (0)
Syam Babu August 2, 2013 at 11:19 am

Nice explanation and Very Helpful.

Thanks,

Syam

like (0)
Kell Vagtholm August 29, 2013 at 12:06 pm

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 🙂

like (0)
1. Yuvaraj S February 25, 2014 at 7:09 am
  
  I have the same questions too. An example for POST, PUT etc would help for sure
  
  like (0)
  1. Steffen Froehlich February 25, 2014 at 7:45 am
    
    Hi Yuvaraj,
    
    please have a look at the available documentation, here: SAP NetWeaver Gateway How-to Guides
    
    and here:
    
    How to Write an OData Channel Gateway Service. Part 2 – The Runtime Data Provider Class
    
    —
    
    Steffen
    
    like (0)
Former Member October 19, 2013 at 9:16 am

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

like (0)
Steffen Froehlich February 10, 2014 at 7:57 pm

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

like (0)
1. Syam Babu May 27, 2014 at 7:36 am
  
  Hi Steffen,
  
  How you are approached multiple input tables have in get_entity method.
  
  Thanks,
  
  Syam
  
  like (0)
Rahul Keshav May 22, 2015 at 11:41 am

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.

like (0)
Benedikt Hoehne July 29, 2015 at 2:32 pm

Hi Andre,

how would you implement additional query parameters, such as a display currency, which is not technically a filter?

Best regards

like (0)
1. Andre Fischer Post authorDecember 4, 2015 at 11:39 am
  
  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
  
  like (0)
Vinayak Nath Singh March 6, 2017 at 11:37 am

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.

like (0)
1. Former Member August 4, 2017 at 9:47 am
  
  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
  
  like (0)
Former Member July 13, 2017 at 6:00 pm

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

like (0)
1. Andre Fischer Post authorJuly 16, 2017 at 8:05 am
  
  Not yet.
  
  like (0)
Former Member August 4, 2017 at 9:56 am

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

like (0)
Former Member March 27, 2018 at 5:19 am

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

like (0)
Syed Omer Hussain June 27, 2018 at 8:26 am

How to Pass Filter Query Values from SAPUI5 App to OData Service ?

like (0)
1. Andre Fischer Post authorJune 27, 2018 at 8:47 am
  
  Please post a question in https://answers.sap.com/questions/ask.html and tag it with SAPUI5.
  
  like (0)
Taryck BENSIALI July 6, 2018 at 7:53 am
Hi,

Interesting, but I’m looking for documentation about how to fullfill the $filter parameter in order to provide all combinaison of the range retrieved with
```
io_tech_request_context->get_filter( )->get_filter_select_options( )
```
We’ve got your exemple : $filter=ProductID ge ‘A’ and ProductID le ‘C’

That provide :

I GE A

I LE C

in the range.

But how do we provide

E BT A C

for instance.

How do we provide high value and Exclusion ?

Where do I find official full documentation on how fill $filter ?!?

Any idea ?
like (0)
1. Andre Fischer Post authorJuly 6, 2018 at 8:20 am
  
  Sure.
  
  Please check the syntax for $filter (section 4.5) at
  
  http://www.odata.org/documentation/odata-version-2-0/uri-conventions/
  
  Combine for example a statement with ‘LE’ and ‘GE’ with ‘or’.
  
  Regards,
  
  Andre
  
  like (0)