Technical Articles
How to get select options for a $filter that contains two substringof functions combined with an OR operator or for $filter statements that cannot be transformed to select options by the SAP Gateway Framework
The use cases
At a SAP CodeJam event in Gütersloh back in 2018 I got a question from a participant that turned out to be more tricky than I thought.
Use case 1 – Combining two substringof functions with OR
Suppose you have a SAPUI5 application that allows a Google like search for two properties of your OData service but the underlying API being called in the SAP backend only supports select options as input parameters.
Since the application only offers one search field the resulting OData request would look like follows:
.../ProductSet?$filter=substringof('ABC',ProductId) or substringof('ABC',SupplierName)
When you try to retrieve select options via the following statement
lt_filter_select_options = io_tech_request_context->get_filter( )->get_filter_select_options( ).you will notice that nothing is returned in table lt_filter_select_options.
This is because select options can only be combined logically with an ‘and’.
Use case 2 – Select options that are not correctly handled by the SAP Gateway Framework
Just recently I learned that SmartFilter Controls can create $filter statements that are (currently) not resolved into selection options by the SAP Gateway Framework though this should be possible from a technical point of view.
Examples of such requests are the following
$filter=SupplierName eq 'Telecomunicaciones Star' and SupplierName ne 'Talpa' and SupplierName ne 'DelBont Industries'
$filter=substringof('Star',SupplierName) and SupplierName ne 'Talpa' and SupplierName ne 'DelBont Industries'
The solution(s)
In the following I will present 3 possible solutions
- Option 1: The OData request cannot be changed – try to use the SADL framework ( –> Caution, use of SAP internal, not released API’s)
- Option 2: The OData request cannot be changed – parse the filter tree
- Option 3 (the OData request can be changed)
The first solution that I offered back in 2018 was to evaluate the filter tree. This works but the coding is quite complicated and thus error prone. However it in principal offers to handle every filter statement that is supported by the SAP Gateway framework.
The coding becomes much more easier when we leverage classes that are offered by the SADL framework. Here I have to thank Evgeniy Kolesnikov for providing the code snippet that I now have added to the coding.
But caution:
The methods from the SADL framework are internal methods that have not been released for public use but only for use within the SADL framework itself.
So it can (unfortunately) not be taken for granted that the above mentioned SADL framework API will always work as shown in this blog. (As a matter of fact they do not deliver any result for the above mentioned sample in SAP S/4HANA 2020).
For completeness I also have left the coding that shows how to deal with the filter tree.
Option 1: The OData request cannot be changed – try to use the SADL framework
In this case you can try to use the following coding to get the select options without having to touch the rest of your code based implementation.
lt_filter_select_options = io_tech_request_context->get_filter( )->get_filter_select_options( ).
IF lt_filter_select_options IS INITIAL.
lo_filter_tree = io_tech_request_context->get_filter_expression_tree( ).
IF lo_filter_tree IS BOUND.
NEW cl_sadl_gw_filter_tree_parser( )->get_complex_condition(
EXPORTING
io_filter_tree = lo_filter_tree
IMPORTING
et_condition = DATA(lt_condition) ).
NEW cl_sadl_cond_to_ranges( )->convert_sadl_cond_to_ranges(
EXPORTING
it_sadl_conditions = lt_condition
IMPORTING
et_ranges = DATA(lt_ranges) ).
ENDIF.
DATA ls_filter_select_option TYPE /iwbep/s_mgw_select_option .
LOOP AT lt_ranges INTO DATA(ls_ranges).
ls_filter_select_option-property = ls_ranges-name.
ls_filter_select_option-select_options = CORRESPONDING #( ls_ranges-range ).
APPEND ls_filter_select_option TO lt_filter_select_options .
ENDLOOP.
ENDIF.When we find that the SAP Gateway framework does not provide select options when calling the following method:
*-get select options from SAP Gateway framework
lt_filters = io_tech_request_context->get_filter( )->get_filter_select_options( ).
We can instead retrieve the filter tree and use two classes from the SADL framework to retrieve
a) the filter statement in reverse polish notation (RPN). We can the give it a try whether the SADL framework is able to provide select options for us.
This will help us when we run into the problem with using a smart filter control that generates $filter statements that can (currently) not be translated into select options by the SAP Gateway framework.
lo_filter_tree = io_tech_request_context->get_filter_expression_tree( ).
IF lo_filter_tree IS BOUND.
NEW cl_sadl_gw_filter_tree_parser( )->get_complex_condition(
EXPORTING
io_filter_tree = lo_filter_tree
IMPORTING
et_condition = DATA(lt_condition) ).
NEW cl_sadl_cond_to_ranges( )->convert_sadl_cond_to_ranges(
EXPORTING
it_sadl_conditions = lt_condition
IMPORTING
et_ranges = DATA(lt_ranges) ).
ENDIF.
When running the above mentioned request we will find the following values in the internal table lt_ranges.
value #(
( TYPE = `simpleValue` VALUE = `*HT*` ATTRIBUTE = `` )
( TYPE = `attribute` VALUE = `` ATTRIBUTE = `PRODUCT_ID` )
( TYPE = `containsPattern` VALUE = `` ATTRIBUTE = `` )
( TYPE = `simpleValue` VALUE = `*Star*` ATTRIBUTE = `` )
( TYPE = `attribute` VALUE = `` ATTRIBUTE = `SUPPLIER_NAME` )
( TYPE = `containsPattern` VALUE = `` ATTRIBUTE = `` )
( TYPE = `or` VALUE = `` ATTRIBUTE = `` )
).Assuming that we only get this kind of request we can add a hard-coded check like the following to make sure that only statements using substrings for the fields ProductID and SupplierName have been provided.
IF NOT ( lt_condition[ 1 ]-type = 'simpleValue' AND lt_condition[ 1 ]-attribute IS INITIAL AND
lt_condition[ 4 ]-type = 'simpleValue' AND lt_condition[ 4 ]-attribute IS INITIAL AND
lt_condition[ 3 ]-type = 'containsPattern' AND lt_condition[ 3 ]-attribute IS INITIAL AND
lt_condition[ 6 ]-type = 'containsPattern' AND lt_condition[ 6 ]-attribute IS INITIAL
).
lv_wrong_filter = abap_true.
ENDIF.
IF lt_condition[ 2 ]-attribute = 'PRODUCT_ID' AND lt_condition[ 5 ]-attribute = 'SUPPLIER_NAME' .
DATA(supplier) = lt_condition[ 4 ]-value.
DATA(product) = lt_condition[ 1 ]-value.
lt_supplier_name = VALUE #( ( sign = 'I' option = 'CP' low = supplier ) ).
lt_product_id = VALUE #( ( sign = 'I' option = 'CP' low = product ) ).
ELSEIF lt_condition[ 5 ]-attribute = 'PRODUCT_ID' AND lt_condition[ 2 ]-attribute = 'SUPPLIER_NAME' .
supplier = lt_condition[ 1 ]-value.
product = lt_condition[ 4 ]-value.
lt_supplier_name = VALUE #( ( sign = 'I' option = 'CP' low = supplier ) ).
lt_product_id = VALUE #( ( sign = 'I' option = 'CP' low = product ) ).
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
Option 2: The OData request cannot be changed – parse the filter tree
For such a special use case we are going to use the filter tree which is unfortunately not easy to handle in the OData V2 framework of SAP Gateway.
The filter tree can be obtained using the following statement.
lo_filter_tree = io_tech_request_context->get_filter_expression_tree( ).In our simple use case the filter tree will be a simple binary tree of type (/iwbep/if_mgw_expr_binary) that contains two nodes of type (/iwbep/if_mgw_expr_function).
By casting the objects to the appropriate types we are able to use the type specific methods.
The following code allows us to check that the function ‘substringof’ has been used.
lv_function = lo_function->function.
IF lv_function <> 'substringof'.
lv_filter_error = 'Only substringof is supported. '.
lv_wrong_filter = abap_true.
ENDIF.The property being used can be retrieved using the following statement:
lt_param_tab = lo_function->parameters.It returns a table that contains two objects. The first object of type (/iwbep/if_mgw_expr_property) allows the retrieval of the property name while the filter string can be obtained via an object of type (/iwbep/if_mgw_expr_literal).
IF lt_param_tab[ 1 ]->kind = /iwbep/if_mgw_expr_node~kind_literal.
lo_literal ?= lt_param_tab[ 1 ].
lv_literal = lo_literal->literal_converted.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
IF lt_param_tab[ 2 ]->kind = /iwbep/if_mgw_expr_node~kind_property.
lo_property ?= lt_param_tab[ 2 ].
lv_property = lo_property->property_name.
ELSE.
lv_wrong_filter = abap_true.
ENDIFAfter having retrieved the data necessary to create the select options for the ProductId and the SupplierName we call the BAPI BAPI_EPM_PRODUCT_GET_LIST twice.
The first time using the select options of the ProductId and a second time with the select options for the SupplierName.
Both result sets are finally merged.
When using the following query parameters
.../ProductSet?$filter=substringof('2001',ProductId) or substringof('AVANT',SupplierName)
we get the following response.
The product ‘HT-2001‘ from the supplier ‘Panorama Studios‘ and three products from the supplier ‘AVANTEL‘.
Option 3 (the OData request can be changed)
If you are able to change the OData request it would be more easy to use the query option search.
?search=<search string>This way you would be able to retrieve the search string in the coding of your data provider extenstion class.
data: lv_search_string type string.
lv_search_string = io_tech_request_context->get_search_string( ).
and you could create select options like this
ls_supplier_name-sign = 'I'.
ls_supplier_name-option ='CP'.
ls_supplier_name-low = '*' && lv_search_string && '*'.
APPEND ls_supplier_name TO lt_supplier_name.
ls_product_id-sign = 'I'.
ls_product_id-option ='CP'.
ls_product_id-low = '*' && lv_search_string && '*'.
APPEND ls_product_id TO lt_product_id.
The source code for leveraging the SADL framework
In the following I am showing the coding that leverages the SADL framework classes.
CLASS zcl_zaf_complex_filter_dpc_ext DEFINITION
PUBLIC
INHERITING FROM zcl_zaf_complex_filter_dpc
CREATE PUBLIC .
PUBLIC SECTION.
PROTECTED SECTION.
METHODS productset_get_entityset REDEFINITION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_zaf_complex_filter_dpc_ext IMPLEMENTATION.
METHOD productset_get_entityset.
DATA : lo_filter_tree TYPE REF TO /iwbep/if_mgw_expr_node,
lo_left_node TYPE REF TO /iwbep/if_mgw_expr_node,
lo_right_node TYPE REF TO /iwbep/if_mgw_expr_node,
lo_binary TYPE REF TO /iwbep/if_mgw_expr_binary,
lo_function TYPE REF TO /iwbep/if_mgw_expr_function,
lo_property TYPE REF TO /iwbep/if_mgw_expr_property,
lo_literal TYPE REF TO /iwbep/if_mgw_expr_literal,
lt_param_tab TYPE /iwbep/if_mgw_expr_function=>parameter_t,
ls_param_tab TYPE LINE OF /iwbep/if_mgw_expr_function=>parameter_t,
lv_operator TYPE string,
lv_function TYPE string,
lv_literal TYPE string,
lv_property TYPE string,
lv_supported_filter_string TYPE string,
lv_filter_error TYPE string,
lv_wrong_filter TYPE abap_bool,
lt_filter_select_options TYPE /iwbep/t_mgw_select_option.
CONSTANTS : lc_kind_unary TYPE c LENGTH 1 VALUE 'U',
lc_kind_binary TYPE c LENGTH 1 VALUE 'B',
lc_kind_literal TYPE c LENGTH 1 VALUE 'C',
lc_kind_function TYPE c LENGTH 1 VALUE 'F',
lc_kind_member TYPE c LENGTH 1 VALUE 'M',
lc_kind_property TYPE c LENGTH 1 VALUE 'P'.
DATA: lt_headerdata TYPE STANDARD TABLE OF bapi_epm_product_header,
ls_headerdata TYPE bapi_epm_product_header,
lv_maxrows TYPE bapi_epm_max_rows,
ls_entity LIKE LINE OF et_entityset,
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,
lt_return TYPE TABLE OF bapiret2,
lo_message_container TYPE REF TO /iwbep/if_message_container.
DATA supplier_name_range_pair TYPE sabp_s_name_range_pair.
DATA product_categ_range_pair TYPE sabp_s_name_range_pair.
DATA product_id_range_pair TYPE sabp_s_name_range_pair.
*- get number of records requested
DATA(lv_top) = io_tech_request_context->get_top( ).
DATA(lv_skip) = io_tech_request_context->get_skip( ).
IF lv_top IS NOT INITIAL.
lv_maxrows-bapimaxrow = lv_top + lv_skip.
ENDIF.
lo_filter_tree = io_tech_request_context->get_filter_expression_tree( ).
IF lo_filter_tree IS BOUND.
NEW cl_sadl_gw_filter_tree_parser( )->get_complex_condition(
EXPORTING
io_filter_tree = lo_filter_tree
IMPORTING
et_condition = DATA(lt_condition) ).
NEW cl_sadl_cond_to_ranges( )->convert_sadl_cond_to_ranges(
EXPORTING
it_sadl_conditions = lt_condition
IMPORTING
et_ranges = DATA(lt_ranges) ).
ENDIF.
lv_wrong_filter = abap_false.
IF lt_ranges IS NOT INITIAL.
READ TABLE lt_ranges WITH TABLE KEY name = 'SUPPLIER_NAME' INTO supplier_name_range_pair.
IF sy-subrc EQ 0.
lt_supplier_name = CORRESPONDING #( supplier_name_range_pair-range ).
ENDIF.
READ TABLE lt_ranges WITH TABLE KEY name = 'PRODUCT_ID' INTO supplier_name_range_pair.
IF sy-subrc EQ 0.
lt_product_id = CORRESPONDING #( product_id_range_pair-range ).
ENDIF.
READ TABLE lt_ranges WITH TABLE KEY name = 'CATEGORY' INTO product_categ_range_pair.
IF sy-subrc EQ 0.
lt_category = CORRESPONDING #( product_categ_range_pair-range ).
ENDIF.
ELSE.
*value #(
*( TYPE = `simpleValue` VALUE = `*HT*` ATTRIBUTE = `` )
*( TYPE = `attribute` VALUE = `` ATTRIBUTE = `PRODUCT_ID` )
*( TYPE = `containsPattern` VALUE = `` ATTRIBUTE = `` )
*( TYPE = `simpleValue` VALUE = `*Star*` ATTRIBUTE = `` )
*( TYPE = `attribute` VALUE = `` ATTRIBUTE = `SUPPLIER_NAME` )
*( TYPE = `containsPattern` VALUE = `` ATTRIBUTE = `` )
*( TYPE = `or` VALUE = `` ATTRIBUTE = `` )
* )
IF lines( lt_condition ) <> 7.
lv_wrong_filter = abap_true.
ENDIF.
IF NOT ( lt_condition[ 1 ]-type = 'simpleValue' AND lt_condition[ 1 ]-attribute IS INITIAL AND
lt_condition[ 4 ]-type = 'simpleValue' AND lt_condition[ 4 ]-attribute IS INITIAL AND
lt_condition[ 3 ]-type = 'containsPattern' AND lt_condition[ 3 ]-attribute IS INITIAL AND
lt_condition[ 6 ]-type = 'containsPattern' AND lt_condition[ 6 ]-attribute IS INITIAL
).
lv_wrong_filter = abap_true.
ENDIF.
IF lt_condition[ 2 ]-attribute = 'PRODUCT_ID' AND lt_condition[ 5 ]-attribute = 'SUPPLIER_NAME' .
DATA(supplier) = lt_condition[ 4 ]-value.
DATA(product) = lt_condition[ 1 ]-value.
lt_supplier_name = VALUE #( ( sign = 'I' option = 'CP' low = supplier ) ).
lt_product_id = VALUE #( ( sign = 'I' option = 'CP' low = product ) ).
ELSEIF lt_condition[ 5 ]-attribute = 'PRODUCT_ID' AND lt_condition[ 2 ]-attribute = 'SUPPLIER_NAME' .
supplier = lt_condition[ 1 ]-value.
product = lt_condition[ 4 ]-value.
lt_supplier_name = VALUE #( ( sign = 'I' option = 'CP' low = supplier ) ).
lt_product_id = VALUE #( ( sign = 'I' option = 'CP' low = product ) ).
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ENDIF.
IF lv_wrong_filter = abap_true.
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error_unlimited
message_unlimited = lv_filter_error && lv_supported_filter_string.
ENDIF.
*
* "first call of BAPI for select options for 'ProductId'.
IF lt_ranges IS INITIAL.
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 = lt_return.
IF lt_return IS NOT INITIAL.
*---Message Container
lo_message_container = mo_context->get_message_container( ).
lo_message_container->add_messages_from_bapi(
it_bapi_messages = lt_return
iv_determine_leading_msg = /iwbep/if_message_container=>gcs_leading_msg_search_option-first
).
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error
message_container = lo_message_container.
ENDIF.
"second call of BAPI with select options for 'SupplierName'
CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST'
EXPORTING
max_rows = lv_maxrows
TABLES
headerdata = lt_headerdata
* selparamproductid = lt_product_id
selparamsuppliernames = lt_supplier_name
* SELPARAMCATEGORIES = lt_category
return = lt_return.
IF lt_return IS NOT INITIAL.
*---Message Container
lo_message_container = mo_context->get_message_container( ).
lo_message_container->add_messages_from_bapi(
it_bapi_messages = lt_return
iv_determine_leading_msg = /iwbep/if_message_container=>gcs_leading_msg_search_option-first
).
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error
message_container = lo_message_container.
ENDIF.
ELSE.
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 = lt_return.
IF lt_return IS NOT INITIAL.
*---Message Container
lo_message_container = mo_context->get_message_container( ).
lo_message_container->add_messages_from_bapi(
it_bapi_messages = lt_return
iv_determine_leading_msg = /iwbep/if_message_container=>gcs_leading_msg_search_option-first
).
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error
message_container = lo_message_container.
ENDIF.
ENDIF.
LOOP AT lt_headerdata INTO ls_headerdata.
"check if product is already part of the result set
READ TABLE et_entityset WITH KEY product_id = ls_headerdata-product_id TRANSPORTING NO FIELDS.
IF sy-subrc <> 0.
APPEND ls_headerdata TO et_entityset.
ENDIF.
ENDLOOP.
IF lv_skip IS NOT INITIAL.
DELETE et_entityset TO lv_skip.
ENDIF.
ENDMETHOD.
ENDCLASS.
The source Code for evaluating the filter tree
As a simple example I created an OData service with one entity set ProductSet using DDIC import of the structure BAPI_EPM_PRODUCT_HEADER. Data is retrieved using the function module BAPI_EPM_PRODUCT_GET_LIST that offers three select options for product categories, supplier names and product ids.
The code first checks whether select options can be retrieved. Here code would have to be implemented for the normal data retrieval.
When our search URL that searches via select options for product ids or for supplier names is reaching our service implementation the code switches to the handling of the filter tree.
The source code throws an error if it finds out that the filter statement does not match the expected use case.
METHOD productset_get_entityset.
DATA : lo_filter_tree TYPE REF TO /iwbep/if_mgw_expr_node,
lo_left_node TYPE REF TO /iwbep/if_mgw_expr_node,
lo_right_node TYPE REF TO /iwbep/if_mgw_expr_node,
lo_binary TYPE REF TO /iwbep/if_mgw_expr_binary,
lo_function TYPE REF TO /iwbep/if_mgw_expr_function,
lo_property TYPE REF TO /iwbep/if_mgw_expr_property,
lo_literal TYPE REF TO /iwbep/if_mgw_expr_literal,
lt_param_tab TYPE /iwbep/if_mgw_expr_function=>parameter_t,
ls_param_tab TYPE LINE OF /iwbep/if_mgw_expr_function=>parameter_t,
lv_operator TYPE string,
lv_function TYPE string,
lv_literal TYPE string,
lv_property TYPE string,
lv_supported_filter_string TYPE string,
lv_filter_error TYPE string,
lv_wrong_filter TYPE abap_bool,
lt_filter_select_options TYPE /iwbep/t_mgw_select_option.
CONSTANTS : lc_kind_unary TYPE c LENGTH 1 VALUE 'U',
lc_kind_binary TYPE c LENGTH 1 VALUE 'B',
lc_kind_literal TYPE c LENGTH 1 VALUE 'C',
lc_kind_function TYPE c LENGTH 1 VALUE 'F',
lc_kind_member TYPE c LENGTH 1 VALUE 'M',
lc_kind_property TYPE c LENGTH 1 VALUE 'P'.
DATA: lt_headerdata TYPE STANDARD TABLE OF bapi_epm_product_header,
ls_headerdata TYPE bapi_epm_product_header,
ls_entity LIKE LINE OF et_entityset,
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,
lt_return TYPE TABLE OF bapiret2,
lo_message_container TYPE REF TO /iwbep/if_message_container.
lv_wrong_filter = abap_false.
lv_supported_filter_string = 'Only the following filterstring is supported: substringof(<some string>,SupplierName) or substringof(<some string>,ProductID)'.
lt_filter_select_options = io_tech_request_context->get_filter( )->get_filter_select_options( ).
IF lt_filter_select_options IS NOT INITIAL.
"implement coding to retrieve data via select options
ELSE.
lo_filter_tree = io_tech_request_context->get_filter_expression_tree( ).
IF lo_filter_tree IS BOUND.
IF lo_filter_tree->kind = lc_kind_binary. " 'B'
lo_filter_tree->prepare_converted_values( ).
lo_binary ?= lo_filter_tree.
lv_operator = lo_binary->operator.
lo_left_node = lo_binary->left_operand.
lo_right_node = lo_binary->right_operand.
IF lo_left_node IS BOUND.
IF lo_left_node->kind = lc_kind_function. " 'F' .
lo_function ?= lo_left_node.
lv_function = lo_function->function.
IF lv_function <> 'substringof'.
lv_filter_error = 'Only substringof is supported. '.
lv_wrong_filter = abap_true.
ENDIF.
lt_param_tab = lo_function->parameters.
IF lt_param_tab IS NOT INITIAL.
IF lt_param_tab[ 1 ]->kind = lc_kind_literal.
lo_literal ?= lt_param_tab[ 1 ].
lv_literal = lo_literal->literal_converted.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
IF lt_param_tab[ 2 ]->kind = lc_kind_property.
lo_property ?= lt_param_tab[ 2 ].
lv_property = lo_property->property_name.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
IF lv_property = 'SUPPLIER_NAME'.
ls_supplier_name-sign = 'I'.
ls_supplier_name-option ='CP'.
ls_supplier_name-low = '*' && lv_literal && '*'.
APPEND ls_supplier_name TO lt_supplier_name.
ELSEIF lv_property = 'PRODUCT_ID'.
ls_product_id-sign = 'I'.
ls_product_id-option ='CP'.
ls_product_id-low = '*' && lv_literal && '*'.
APPEND ls_product_id TO lt_product_id.
ELSE.
" raise error message that filter string does not match the expected format
" an additional property was found in the filter string
lv_filter_error = 'Property:' && lv_property && ' is not supported. '.
lv_wrong_filter = abap_true.
ENDIF.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ENDIF.
CLEAR lo_function.
CLEAR lo_property.
CLEAR lo_literal.
CLEAR lt_param_tab.
CLEAR ls_param_tab.
CLEAR lv_operator.
CLEAR lv_function.
CLEAR lv_literal.
CLEAR lv_property.
IF lo_right_node IS BOUND.
IF lo_right_node->kind = lc_kind_function. " 'F' .
lo_function ?= lo_right_node.
lv_function = lo_function->function.
IF lv_function <> 'substringof'.
lv_filter_error = 'Only substringof is supported. '.
lv_wrong_filter = abap_true.
ENDIF.
lt_param_tab = lo_function->parameters.
IF lt_param_tab IS NOT INITIAL.
IF lt_param_tab[ 1 ]->kind = lc_kind_literal.
lo_literal ?= lt_param_tab[ 1 ].
lv_literal = lo_literal->literal_converted.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
IF lt_param_tab[ 2 ]->kind = lc_kind_property.
lo_property ?= lt_param_tab[ 2 ].
lv_property = lo_property->property_name.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
IF lv_property = 'SUPPLIER_NAME'.
ls_supplier_name-sign = 'I'.
ls_supplier_name-option ='CP'.
ls_supplier_name-low = '*' && lv_literal && '*'.
APPEND ls_supplier_name TO lt_supplier_name.
ELSEIF lv_property = 'PRODUCT_ID'.
ls_product_id-sign = 'I'.
ls_product_id-option ='CP'.
ls_product_id-low = '*' && lv_literal && '*'.
APPEND ls_product_id TO lt_product_id.
ELSE.
" raise error message that filter string does not match the expected format
" an additional property was found in the filter string
lv_filter_error = 'Property:' && lv_property && ' is not supported. '.
lv_wrong_filter = abap_true.
ENDIF.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ELSE.
lv_wrong_filter = abap_true.
ENDIF.
ENDIF.
" IF lo_right_node->kind = /iwbep/if_mgw_expr_node~kind_function. " 'F' .
"lr_function ?= lo_right_node.
ELSE.
lv_filter_error = ' Filter is not binary. '.
lv_wrong_filter = abap_true.
ENDIF.
ENDIF.
IF lv_wrong_filter = abap_true.
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error_unlimited
message_unlimited = lv_filter_error && lv_supported_filter_string.
ENDIF.
"first call of BAPI for select options for 'ProductId'.
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 = lt_return.
IF lt_return IS NOT INITIAL.
*---Message Container
lo_message_container = mo_context->get_message_container( ).
lo_message_container->add_messages_from_bapi(
it_bapi_messages = lt_return
iv_determine_leading_msg = /iwbep/if_message_container=>gcs_leading_msg_search_option-first
).
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error
message_container = lo_message_container.
ENDIF.
"second call of BAPI with select options for 'SupplierName'
CALL FUNCTION 'BAPI_EPM_PRODUCT_GET_LIST'
* EXPORTING
* MAX_ROWS = lv_maxrows
TABLES
headerdata = lt_headerdata
* selparamproductid = lt_product_id
selparamsuppliernames = lt_supplier_name
* SELPARAMCATEGORIES = lt_category
return = lt_return.
IF lt_return IS NOT INITIAL.
*---Message Container
lo_message_container = mo_context->get_message_container( ).
lo_message_container->add_messages_from_bapi(
it_bapi_messages = lt_return
iv_determine_leading_msg = /iwbep/if_message_container=>gcs_leading_msg_search_option-first
).
RAISE EXCEPTION TYPE /iwbep/cx_mgw_busi_exception
EXPORTING
textid = /iwbep/cx_mgw_busi_exception=>business_error
message_container = lo_message_container.
ENDIF.
LOOP AT lt_headerdata INTO ls_headerdata.
"check if product is already part of the result set
READ TABLE et_entityset WITH KEY product_id = ls_headerdata-product_id TRANSPORTING NO FIELDS.
IF sy-subrc <> 0.
APPEND ls_headerdata TO et_entityset.
ENDIF.
ENDLOOP.
ENDIF.
ENDMETHOD.
Hi
I don´t rember having seen the search query option mentioned anyhwere !
where you meaning the Select option
Regards Sigurdur
Hi Sigurdur,
sorry, but I am not sure whether I have understood your Question correctly.
I have mentioned the search Option 2.
There I show how to retrieve the search string.
lv_search_string = io_tech_request_context->get_search_string( ).
and then I Show how this string can be used to fill a select option:
ls_supplier_name-sign = 'I'. ls_supplier_name-option ='CP'.
ls_supplier_name-low = '*' && lv_search_string && '*'.
APPEND ls_supplier_name TO lt_supplier_name.
Regards,
Andre
Hello Andre,
I think Sigurdur is trying to say that this is the first time that anyone mentions the existence of a search parameter.
Thus it's not exactly clear how a valid URL should look like. Is it something like this?:
Follow up question: Is it possible that the following URL cannot be transformed into the ?search variant?
(.../ProductSet?$filter=substringof('2001',ProductId) or substringof('AVANT',SupplierName))
In cases like this, Option 1 seems to be the only option.
What if I want filter for not 2 Property but for more properties?
Then the filter tree lo_filter_tree becomes more complicated and you would have to write more code to evaluate the content of it.
Unfortunately it is not possible to provide a simple sample code for that.
Hi Andre Fischer – If i have a gateway project that exposes an underlying CDS view, is there a way to just change the filter dynamically that is passed in to the CDS view query without having to code calling the CDS view and doing all of the paging logic etc.
E.g. I just want to add a few more query filter criteria that is not supplied by the front end (I actually need to determine these entries at by querying another table to get the keys and pass in these keys to the CDS view) – and then I want this to be sent to the CDS view and the rest to behave as is. I do not want to reimplement the full GET_ENTITYSET logic.
Thanks,
Jay
If you use the Referenced Data Source approach there is no way to tweak the query options.
There was a way to do so in the older approach called "mapped data source" as described in this archived how to guide.
https://archive.sap.com/documents/docs/DOC-62591
Regards,
Andre
Thanks Andre - I had to reimplement GET_ENTITYSET and change the filters there. In the redefinition I was able to call the super method and also add my custom logic.
Thanks,
Jay
Hi Andre,
sorry to disturb, I got an issue which is quite similar with yours.
In some cases the table 'IT_FILTER_SELECT_OPTIONS' will be initial, but in the same time 'IT_FILTER_STRING' will not be.
for example when I trying to use below filter selections which has two same conditions 'ProcessStatus':
?$filter=(((CreationDate ge datetime'2021-01-01T00:00:00' and CreationDate le datetime'2021-09-10T00:00:00') and ProcessStatus eq 'CREA') and ProcessStatus eq 'CREA') and Process eq 'ESSIIDOC' )
in this case I still want to use the table 'IT_FILTER_SELECT_OPTIONS', do you have any suggestion on this?
thanks in advance
The parameter IT_FILTER_SELECT_OPTIONS should not be used since it is deprecated.
Instead you should try to retrieve the select options via using the object io_tech_request context. Lik in the following statement.
If that fails, that means if lt_filter_select_options is initial that means that the filter options provided via $filter cannot be translated into select options.
Hi Andre,
I solve this issue by using following code, make a record here if anyone who may face the same issue.
DATA: lo_processor TYPE REF TO cl_fis_shlp_processor,
lt_sel_options TYPE REF TO data,
lt_filter TYPE /iwbep/t_mgw_select_option.
CREATE OBJECT lo_processor.
lt_filter = io_tech_request_context->get_filter( )->get_filter_select_options( ).
" Get Expression tree
TRY.
io_tech_request_context->get_filter_expression_tree( )->mo_filter_parser->get_expression_tree( IMPORTING eo_filter = DATA(expr) ).
CATCH /iwbep/cx_mgw_busi_exception .
ENDTRY.
" Get the select option table through Expression tree
CASE expr->kind.
WHEN expr->kind_unary.
WHEN expr->kind_binary.
TRY.
lt_sel_options = lo_processor->/iwbep/if_mgw_expr_visitor~process_binary( io_binary = CAST #( expr ) ).
CATCH /iwbep/cx_mgw_expr_vistr_excp.
* RAISE EXCEPTION TYPE /iwbep/cx_mgw_tech_exception.
ENDTRY.
WHEN expr->kind_function.
TRY.
lt_sel_options = lo_processor->/iwbep/if_mgw_expr_visitor~process_function( io_function = CAST #( expr ) ).
CATCH /iwbep/cx_mgw_expr_vistr_excp.
* RAISE EXCEPTION TYPE /iwbep/cx_mgw_tech_exception.
ENDTRY.
WHEN expr->kind_member.
ENDCASE.
" Organize the select option table
CHECK lt_sel_options IS BOUND.
ASSIGN lt_sel_options->* TO FIELD-SYMBOL(<fs_sel_opt>).
lt_filter = <fs_sel_opt>.
Hi Andre Fischer ,
for filters like
$filter=substringof('ABC',ProductId) or substringof('ABC',SupplierName)the folowing code works well
LT_RANGES result:
Great piece of code, should be added to the main blog. Works perfectly! 🙂
Hi Evgeniy Kolesnikov,
I did try above code, but lt_ranges[] is blank.
$filter=substringof('2001',ProductId) or substringof('AVANT',SupplierName)
Thanks,
Subba
This method below returns ranges with 'I' include sign only although the filter condition in the smart table with exclude is defined. This causes inconsistent behavior during data selection. Is there any work around other than implementing own logic?
This is mapping method below which builds the ranges. The sign 'I' or 'E' is decided conditionally based on the importing parameter 'iv_sign'. But unfortunately the incoming parameter 'iv_sign' is always set to be true.