Skip to Content
Technical Articles
Author's profile photo Mahesh Palavalli

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

 

 

 

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michelle Crapo
      Michelle Crapo

      So I've read this - and went back and read the previous blog.   Nice job bringing them together.

      AMDP - I don't understand why you didn't use any other code prior to the selection.  You could easily limit the records and avoid the string_agg functionality.   Is there a reason?

      Thank you!

      Michelle

      Author's profile photo Mahesh Palavalli
      Mahesh Palavalli
      Blog Post Author

      Thanks for the feedback Michelle 🙂 🙂

      You can see I am exposing CDS view as the odata service and "Limiting" of the data data comes from the odata service dynamically by "SKIP" and "TOP" parameters. Then SADL Layer here will pass that limit filters to the CDS view select query dynamically. So for this reason, i shouldn't limit the data that AMDP produces, it should be limited dynamically from the filter coming from the top level cds view else it will produce inconsistent data.

      But a LIMIT filter on a CDS view is applied only at the end of the data selection. Here in our case, first

      1. AMDP will aggregate the data(CountryFrom association data)
      2. The association data will be joined with header data
      3. Then the Filter(CountryFrom) we sent from the UI will applied on the combined data.
      4. Then the Limit filter is applied.

      But incase of Virtual Elements scenario, there will be no aggregation, SADL Layer use "EXISTS" statement on the CDS view dynamically and so the performance is increased by many folds.

       

      Thanks,

      Mahesh

      Author's profile photo Michelle Crapo
      Michelle Crapo

      Makes sense to me now.

      Thank you!

      Author's profile photo Vijay Sharma
      Vijay Sharma

      Super cool!! Nice blog Mahesh :)..

       

      Regards

      Vijay

      Author's profile photo Mahesh Palavalli
      Mahesh Palavalli
      Blog Post Author

      Thanks Vijay 🙂

      Author's profile photo Ravi Singh
      Ravi Singh

      Good one Mahesh. Very nice informative blog.

      Author's profile photo B@lu .
      B@lu .

      Can't we use CDS Table Function as association instead of left outer join?

      because i tried to write association to CDS Table function but it was giving syntax error.

       

      Regards,

      Balu