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 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.
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.
so that based on the group number different string for a WHERE condition cab be build .
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 :
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.
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:
Example select query with the dynamic where
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 :
Please let me know your valuable comments.
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 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.
*-- ANLA
APPEND VALUE #( field = 'ANLKL' table = mc_anla group = 1 ) TO mt_filter_grp.
..
.......
...
*--Proj
APPEND VALUE #( field = 'POSID' 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
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_Asset
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_table ASSIGNING FIELD-SYMBOL(<ls_table>) WHERE (ms_change_hist).
ENDLOOP.Please let me know your valuable comments.
- SAP Managed Tags:
