Filtering on Association property using Table Function/AMDP (VS ABAP Virtual Elements)

Introduction

Filtering on the Association property(sub entity) via the odata service(with SADL & CDS) is not a straight forward task especially if the cardinality is 0..N. I’ve already written a blog post about filtering the entity data based on the sub entity(association) property using Virtual Elements in CDS view.

https://blogs.sap.com/2020/01/16/filtering-on-association-property-in-fiori-element-app-via-abap-cds-virtual-elements/

But in this post, I will show the way to apply filter on the association property using the table function(AMDP) approach.

Use this approach in case if the Virtual Elements are not available in your ABAP version as from my understanding Virtual Elements performance is way better compared to Table function approach.

Table Function & AMDP approach

If we use the virtual elements as described in the above blog, SADL layer will generate an “EXISTS” statement in the where condition dynamically and filter out the parent entity data.

As we cannot use that approach in AMDP, we use STRING_AGG function to concatenate multiple items fields and produce a single string. Follow this blog by Felipe de Mello Rodrigues for more details

https://blogs.sap.com/2017/12/12/concatenate-multiple-records-in-a-single-field-using-abap-cds-table-function/

and we expose table function as an association in the root CDS view but with cardinality 0..1. By this way, we can use expose the field which has all the items data concatenated in the root CDS view and from the UI, filter can be applied on that field.

Table Function: To consume AMDP

@ClientHandling.type: #CLIENT_DEPENDENT
@EndUserText.label: 'Flight Country Search Table FUnction'
define table function ZC_FLIGHT_COUNTRY_FROM with parameters 
@Environment.systemField: #CLIENT
p_client: s_mandt
returns {
  key client : s_mandt;
  key carrid : s_carrid;
  from_country : text240;
}
implemented by method ZFLIGHT_CSEARCH=>COUNTRY_FROM;

AMDP: Using STRING_AGG to concatenate multiple countries from which the Flight starts into a single field.

CLASS ZFLIGHT_CSEARCH DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    CLASS-METHODS COUNTRY_FROM FOR TABLE FUNCTION ZC_FLIGHT_COUNTRY_FROM.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS ZFLIGHT_CSEARCH IMPLEMENTATION.
  METHOD COUNTRY_FROM BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
    USING spfli.

    return  select mandt as client,
                   carrid,
                   STRING_AGG ( countryfr, ',' ) as from_country
              from spfli
              WHERE mandt = :p_client
              GROUP BY mandt, carrid;

  ENDMETHOD.
ENDCLASS.

CDS View: same cds view which is used in Virtual elements blog but enhanced with table function consuption

@AbapCatalog.sqlViewName: 'ZVCCARFLTTEST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Car1 Test'
@OData.publish: true
define view ZC_CarrierFilterTest
  as select from    scarr
    left outer join ZC_FLIGHT_COUNTRY_FROM(p_client : $session.client ) as _countrySearchTblFnc on scarr.carrid = _countrySearchTblFnc.carrid
  association [0..*] to ZC_SPFLI1 as _spfli   on $projection.carrid = _spfli.carrid
  association [0..*] to sgeocity  as _geoCity on $projection.country_from = _geoCity.country
{
      @UI.lineItem: [
      { position: 10 }]
  key scarr.carrid,
      @UI.selectionField: [{ position: 20 }]
      @UI.lineItem: [{ position: 20 }]
      scarr.carrname,
      @UI.selectionField: [{ position: 30 }]
      @UI.lineItem: [{ position: 30 }]
      scarr.currcode,
      @UI.lineItem: [{ position: 50 }]
      scarr.url,
      @ObjectModel.virtualElement
      @ObjectModel.filter.transformedBy: 'ABAP:ZCL_VE_CAR1'
      @EndUserText.label: 'Country From Virtual Element'
      @UI.selectionField: [{ position: 50 }]
      @Consumption.valueHelp: '_geoCity'
      cast( '   ' as land1 preserving type ) as country_from,

      @EndUserText.label: 'Country From table Function'
      @UI.selectionField: [{ position: 60 }]
      /**** Exposing the table function field in the main CDS view, not mandatory in case of association*****/
      _countrySearchTblFnc.from_country      as country_from_tblfnc,

      /* Expose the associations*/
      _spfli,
      _geoCity
}

All done, but only thing pending is to inform the front end developer to send the filter via “Contain” condition and not “Equal” condition, as we have all the child items concatenated in a single field.

Note: In the above screenshot, I am sending the filter as *DE* (contains condition).

Some more Technical Details for the Geeks

I liked and also received some nice feedback for this header, so whenever it is possible, I will reuse it  😛 😀

So in the beginning, I mentioned that the performance is better while using ABAP virtual elements instead of Table function. I came to this conclusion by testing it out on bulk data, parent table (100 thousand ) and item table around (500 thousand).

When the Virtual Elements are used,

1. Filter is first applied on the item data directly at first. (less data, filtered)
2. and the filtered item data will be joined with header data for filtering out the header data

When the Table Function is used,

1. First all the item data will be aggregated via STRNG_AGG function (more data)
2. Then filter will be applied  on the item data(less data, filtered)
3. At the end, item data will be joined with the header data.

Here the performance hit is at the items aggregation level, where rows will be concatenated into a single line in the case of Table Function scenario. It is taking about 75% of the total execution time.

I used vizplan to do this. check the below blog by Ravi Dasari

https://blogs.sap.com/2018/12/31/planviz-analysis-on-cds-views/

Conclusion

If you have access to Virtual elements use it and if the system is old and supports table function, use this approach.

Let me know your thoughts on this 🙂

Thanks,
Mahesh