Skip to Content
Technical Articles

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

/
Be the first to leave a comment
You must be Logged on to comment or reply to a post.