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

[VijayCR]

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.