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: 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
- 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 !!!
Very helpful!
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
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( ).
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.
Nice explanation and Very Helpful.
Thanks,
Syam
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 🙂
I have the same questions too. An example for POST, PUT etc would help for sure
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
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
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
Hi Steffen,
How you are approached multiple input tables have in get_entity method.
Thanks,
Syam
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.
Hi Andre,
how would you implement additional query parameters, such as a display currency, which is not technically a filter?
Best regards
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
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.
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
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
Not yet.
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