OData with Dynamic Filters for SELECT Queries,Entity Set and Dynamic Conversion Exits

OData with Dynamic Filters for SELECT Queries, Entity Set and Dynamic Conversion Exits

As there will be many Fiori List reports are developed now a days with ECC backend,Not every report can be built using a CDS view due to performance problems. The below blog will provide an overview about how to dynamically build the dynamic WHERE conditions on the tables with out declartion select options and how to filter internal table using the WHERE condition .

There are also multiple conversion exits for the input selection fields which are required. This can be achieved with out the declaring the ranges for that particular fields and can be build dynamically using the below classes and methods :

Dynamic Where from the filters  in the front end  :

Assumptions :

The Entityset is created with reference to a Strucutre with Domains from Standard fields and have conversion exits defined.

Lets say a Case where you have to select the Assets from different Asset Master tables like ANLA,ANLZ,ANLU based on 100 select fields from a List report .You also have a field Project number which can be used as filter not related to Asset Maser Tables but PRPS and then Determine the Asset Number and filter.How can we handles this kind of cases dynamically :

Select the List of filters from the importing parameters of a GET_ENTITYSET method.

**-Get the Filter lo_filter = io_tech_request_context->get_filter( ). *--Build the Filters mr_fc_helper->get_filters( it_filter = io_tech_request_context->get_filter( )->get_filter_select_options( ) io_filter = lo_filter ).

In the method get_filters implement the below code to build the dynamic where condition :

First of all Group the fitlers using a method build_filters , This can be also maintianed in a custom table so that it can be dynamically changed.

 METHOD build_filters. *--ANLZ APPEND VALUE #( field = 'BUKRS' table = mc_anlz group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'ANLN1' table = mc_anlz group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'ANLN2' table = mc_anlz group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'BDATU' table = mc_anlz group = 1 ) TO mt_filter_grp. *-- ANLA APPEND VALUE #( field = 'ANLKL' table = mc_anla group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'ERDAT' table = mc_anla group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'AENAM' table = mc_anla group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'AEDAT' table = mc_anla group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'XSPEB' table = mc_anla group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'XLOEV' table = mc_anla group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'KTOGR' table = mc_anla group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'ANLTP' table = mc_anla group = 1 ) TO mt_filter_grp. APPEND VALUE #( field = 'ZUJHR' table = mc_anla group = 1 ) TO mt_filter_grp. ....... ... *--Proj APPEND VALUE #( field = 'PROJID' table = mc_prps group = 2 ) TO mt_filter_grp. ENDMETHOD.

so that based on the group number different string for a WHERE condition cab be build .

 METHOD get_filters. DATA : ls_so TYPE /iwbep/s_cod_select_option, lt_so TYPE /iwbep/t_cod_select_options, ls_convert TYPE /iwbep/s_mgw_select_option. *-- Build Dyanamic Filters build_filters( ). LOOP AT it_filter ASSIGNING FIELD-SYMBOL(<fs_filters>). REFRESH lt_so . *-- Select Filter groups READ TABLE mt_filter_grp ASSIGNING FIELD-SYMBOL(<fs_flt_grp>) WITH KEY field = <fs_filters>-property. IF sy-subrc IS INITIAL. REFRESH lt_so . CLEAR ls_convert. ls_convert = <fs_filters>. *-- Dyanamic Conversion Exits CALL METHOD me->convert_select_options EXPORTING io_filter = io_filter CHANGING ct_select_option = ls_convert. *-- Convert the Select Option lt_so = ls_convert-select_options. IF <fs_flt_grp>-group EQ 1. CALL METHOD me->build_where_statement_range EXPORTING is_field = <fs_flt_grp>-field it_range = lt_so is_table = <fs_flt_grp>-table CHANGING cs_where = ms_assets_all. ELSEIF <fs_flt_grp>-group EQ 2. CALL METHOD me->build_where_statement_range EXPORTING is_field = <fs_flt_grp>-field it_range = lt_so is_table = <fs_flt_grp>-table CHANGING cs_where = ms_proj_where. ENDIF. ENDIF. ENDIF. ENDLOOP. ENDMETHOD.

Dynamic Conversion Exists : 

For the simple conversion extis with Ranges definition this blog can be referred :

https://blogs.sap.com/2016/10/21/conversions-sap-gateway-foundation-part-1/

There are multiple ways this can be achived however the one in this blog is for the cases where the Ranges declaration is not possible :

Get the properties of the Entity type using the below code for reading the default conversion exits :

*-- Get Entity properties for Conversion Exit lo_facade ?= /iwbep/if_mgw_conv_srv_runtime~get_dp_facade( ). lo_read_model ?= lo_facade->/iwbep/if_mgw_dp_int_facade~get_model( ). lr_entity_type ?= lo_read_model->/iwbep/if_mgw_odata_re_model~get_entity_type( mr_fc_helper->mc_ent_type_ast ). mr_fc_helper->mt_properties = lr_entity_type->/iwbep/if_mgw_odata_fw_etype~get_properties( ).

Using the properties build the filters dynamicallly irrespective of the ALHPA or Other exits this will be determined automatically from the Properties of the Domain of the Entityset.

 METHOD convert_select_options. DATA: lv_no_conversion TYPE abap_bool, ls_source_option TYPE /iwbep/s_cod_select_option, ls_odata_property TYPE /iwbep/if_mgw_odata_fw_prop=>ty_s_mgw_odata_property. DATA: lv_conv_exit TYPE /iwbep/if_mgw_med_odata_types=>ty_e_med_conv_exit, lv_function TYPE string, lv_round_sign TYPE char1, lr_data TYPE REF TO data. FIELD-SYMBOLS: <value> TYPE any. *-- Simple Type Odata Property lesen IF lv_max_level = 1. lv_abap_name = iv_property_name. READ TABLE mt_properties INTO ls_odata_property WITH KEY technical_name = lv_abap_name. EXIT. ENDIF. IF ls_odata_property IS INITIAL. EXIT. ENDIF. *-- No conversion prüfen lv_no_conversion = ls_odata_property-property->get_no_conversion( ). * Convert LOW and HIGH Values LOOP AT ct_select_option-select_options ASSIGNING FIELD-SYMBOL(<fs_source_option>). IF lv_no_conversion IS INITIAL. lv_conv_exit = ls_odata_property-property->get_conversion_exit( ). IF lv_conv_exit IS NOT INITIAL AND <fs_source_option>-low IS NOT INITIAL. *-- Dynamic Output CREATE DATA lr_data TYPE (ct_select_option-property). ASSIGN lr_data->* TO <value>. lv_round_sign = ls_odata_property-property->get_round_sign( ). *-- Exit Aufrufen CONCATENATE 'CONVERSION_EXIT_' lv_conv_exit '_INPUT' INTO lv_function. CALL FUNCTION lv_function EXPORTING input = <fs_source_option>-low * unit = ls_unit_code-value_iso round_sign = lv_round_sign IMPORTING output = <value>. <fs_source_option>-low = <value>. ENDIF. IF lv_conv_exit IS NOT INITIAL AND <fs_source_option>-high IS NOT INITIAL. lv_round_sign = ls_odata_property-property->get_round_sign( ). *-- Exit Aufrufen CONCATENATE 'CONVERSION_EXIT_' lv_conv_exit '_INPUT' INTO lv_function. CALL FUNCTION lv_function EXPORTING input = <fs_source_option>-high round_sign = lv_round_sign IMPORTING output = <value>. <fs_source_option>-high = <value>. ENDIF. ENDIF. ENDLOOP. ENDMETHOD.

By using the above two methods the conversion exits can be done for multiple fields with out definition of the ranges for each field.

Dynamic WHERE String  for Select query :

Now that the converted values are available how to build dynamic where conditions :

Use the code in the below method to build the WHERE in a string

NOTE  : In case of a single table the where is build with field  = (‘VALUE’ )and field (‘VALUE’).In case of multiple tables in a single query it can be build using TABLE~Fieldname =(‘VALUE’) … son on.

IF the input parameter is a table name then provide a where with ‘~’ is built, else only only field name is taken into WHERE string.

The below method can also be used in various methods to concatenate the filters in case of addtional input filters should be taken into the SELECT query

All the below Filter actions can be handled using the below dynamic WHERE Condition Class:

 METHOD build_where_statement_range. *----------------------------------------------------------------------* CONSTANTS : lc_and(3) TYPE c VALUE 'AND'. DATA: lr_tab_descr TYPE REF TO cl_abap_tabledescr, lr_str_descr TYPE REF TO cl_abap_structdescr, lt_f4_selopt TYPE STANDARD TABLE OF ddshselopt. DATA: lref_rt_tab TYPE REF TO data, lref_rt_struc TYPE REF TO data. DATA: ls_where TYPE string, ls_where_f4 TYPE string, lt_range TYPE rs_t_rscedst, ls_range TYPE rscedst. FIELD-SYMBOLS: <fst_data> TYPE STANDARD TABLE, <fs_data> TYPE any. *--------------------------------------------------------------------* lr_tab_descr ?= cl_abap_tabledescr=>describe_by_data( it_range ). lr_str_descr ?= lr_tab_descr->get_table_line_type( ). CREATE DATA lref_rt_tab TYPE HANDLE lr_tab_descr. CREATE DATA lref_rt_struc TYPE HANDLE lr_str_descr. ASSIGN lref_rt_tab->* TO <fst_data>. ASSIGN lref_rt_struc->* TO <fs_data>. <fst_data>[] = it_range[]. LOOP AT <fst_data> ASSIGNING <fs_data>. ls_range-fnam = is_field. MOVE-CORRESPONDING <fs_data> TO ls_range. APPEND ls_range TO lt_range. ENDLOOP. CLEAR : ls_where_f4,ls_where_f4. *__ Pattern _________________________________________________________ LOOP AT lt_range ASSIGNING FIELD-SYMBOL(<ls_range>). APPEND VALUE #( shlpname = is_table shlpfield = is_field sign = <ls_range>-sign option = <ls_range>-option low = <ls_range>-low high = <ls_range>-high ) TO lt_f4_selopt. IF is_table IS NOT INITIAL. CALL FUNCTION 'F4_CONV_SELOPT_TO_WHERECLAUSE' EXPORTING gen_alias_names = abap_true IMPORTING where_clause = ls_where TABLES selopt_tab = lt_f4_selopt. ELSE. CALL FUNCTION 'F4_CONV_SELOPT_TO_WHERECLAUSE' EXPORTING gen_alias_names = abap_false IMPORTING where_clause = ls_where TABLES selopt_tab = lt_f4_selopt. ENDIF. ENDLOOP. IF cs_where IS NOT INITIAL. CONCATENATE cs_where lc_and ls_where INTO cs_where SEPARATED BY space. ELSE. cs_where = ls_where. ENDIF. ENDMETHOD.

Example select query with the dynamic where

*--ANLZ,ANLA,ANLU Inner Join SELECT anlz~bukrs anlz~anln1 anlz~anln2 *-- ANLA anla~anlkl anla~erdat anla~aenam anla~aedat anla~xspeb anla~xloev anla~ktogr anla~anltp FROM anlz AS anlz INNER JOIN anla AS anla ON anla~bukrs = anlz~bukrs AND anla~anln1 = anlz~anln1 AND anla~anln2 = anlz~anln2 INTO TABLE mt_assets_all UP TO mv_max ROWS " Pagination WHERE (ms_assets_all).

Dynamic WHERE String  for Entityset Filter :

Sometime the same dynamic where can also be used in filtering the final Entityset Internal table.

In this case it not possible to directly use the WHERE condtion in a LOOP Instead Regular Expressions should be as shown below in order to convert the WHERE so that it is compatable in a LOOP as shown in the below code :

 REPLACE ALL OCCURRENCES OF 'LIKE' IN ms_change_hist WITH 'CP'. REPLACE ALL OCCURRENCES OF REGEX '[[%punct%]' IN ms_change_hist WITH '*'. LOOP AT lt_position ASSIGNING FIELD-SYMBOL(<ls_position>) WHERE (ms_change_hist). ENDLOOP.

Please let me know your valuable comments.