Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
maheshpalavalli
Active Contributor
Some time back there was a auestion about filtering the Fiori Element application by association property of the CDS view.

https://answers.sap.com/questions/758309/fiori-elements-filter-based-on-association-propert.html

At that time, I debugged both the UI5 Fiori Elements APIs and backend odata and found that Fiori Elements & SAP Gateway both doesn't support it.

Actually It supports the filter on association property but only with the associations having cardinality of 1..1 and it doesn't support 1..n .  Not sure if something has changed in between as I debugged it last year Feb 2019.

In fact, I was planning to blog about it ๐Ÿ˜„ funny, I didn't have a solution at that time for that but still created a blog post and hoped in that process I will figure out the solution ๐Ÿ™‚ ๐Ÿ™‚ believe me it works ๐Ÿ™‚ ๐Ÿ˜„ and it is one of the best way to learn & dig deep into the subject ๐Ÿ™‚  But I was lazy and left that blog post in the draft state ๐Ÿ˜„

Now recently there was some activity in that question asking for a proper solution and I replied with a workaround of using the table function but now again I found a different way, which uses the Virtual Elements in ABAP CDS ๐Ÿ™‚

So in this blog post, I will show the way to filter the data by association property with 1..n cardinality using the Virtual Elements in CDS view. I believe this will be applicable for ABAP Restful programming model as well as the SADL layer(Data fetching logic of CDS view) is common for both ABAP programming model for Fiori and RAP.

For filtering via AMDP/Table Function in CDS, check the below blog.

https://blogs.sap.com/2020/01/27/filtering-on-association-property-using-table-function-amdp-vs-abap...


Solution


I will use an example of Carrier CDS view, which has an association(1..n cardinality) with Flights CDS view.

OData service is generated based on the annotations and consumed in the Fiori Elements List Report application.



 

Now the functionality to fetch all the carries is based on the "Country From"(COUNTRYFR) should be provided. But this field is not part of SCARR table, it is part of the association table SPFLI(1..n).

For this we will use the virtual element annotations and create a class as shown in the below steps.

Step:1


Creating both the root(parent) and child cds views.

 

Root CDS view - ZC_CARRIER
@AbapCatalog.sqlViewName: 'ZVCCARRIER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Car1 Test'
@OData.publish: true
define view ZC_CARRIER
as select from scarr
association [0..*] to ZC_SPFLI 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.virtualElementCalculatedBy: 'ABAP:ZCL_VE_CAR1'
@ObjectModel.filter.transformedBy: 'ABAP:ZCL_VE_CAR1'
@EndUserText.label: 'Country From'
@UI.selectionField: [{ position: 50 }]
@Consumption.valueHelp: '_geoCity'
cast( ' ' as land1 preserving type ) as country_from,

/* Expose the associations*/
_spfli,
_geoCity
}

Child CDS view
@AbapCatalog.sqlViewName: 'ZVCSPFLI'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Spfli1'
define view ZC_SPFLI
as select from spfli
association [0..1] to ZC_CARRIER as _carrier on spfli.carrid = _carrier.carrid
{
key spfli.carrid,
key spfli.connid,
spfli.countryfr,
spfli.cityfrom,
spfli.airpfrom,
spfli.countryto,
spfli.cityto,
spfli.airpto,
_carrier
}

They both are pretty simple CDS views, but the Root CDS view has the new Virtual Element "country_from" with annotations related to Virtual Elements.

More about it in SAP Help link:

https://help.sap.com/viewer/cc0c305d2fab47bd808adcad3ca7ee9d/7.51.7/en-US/a7fc007921d44263b09ccc0923...
@ObjectModel.virtualElement

Above annotation tells that the property is a Virtual Element.
@ObjectModel.filter.transformedBy: 'ABAP:ZCL_VE_CARRIER'

above one will tell the framework that if  the filter is applied on the Virtual Element then call the ABAP class "ZCL_VE_CARRIER" to get the filter condition.

 

Step:2


Let's create the class and implement the interface as shown below:
CLASS ZCL_VE_CARRIER DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_sadl_exit .
INTERFACES if_sadl_exit_filter_transform .
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS ZCL_VE_CARRIER IMPLEMENTATION.
METHOD if_sadl_exit_filter_transform~map_atom.
DATA(country_from) = cl_sadl_cond_prov_factory_pub=>create_simple_cond_factory( )->element( '_SPFLI.COUNTRYFR' ).
ro_condition = country_from->equals( iv_value ).
ENDMETHOD.
ENDCLASS.

 

In the above code,  I created a condition on the Association Property "_SPFLI.COUNTRYFR" and sent is back to the framework.

For more information, check the SAP help from the step1, It's just a basic code and in production scenarios, it should be able to handle the different type of of filter conditions(EQ, Contains..,).

 

That's it, just run the application to see the new Country From Filter, select  a value and apply the filter to get all the carries based on the Country From:



 

Some more Technical Details for the Geeks


When the search is executed in the Fiori app, SADL layer will request the Virtual Element filters before performing the actual CDS view Select execution below:



 

These conditions taken from that exit will be converted to a where condition by SADL layer in the below format:
EXISTS ( SELECT ZC_CAR1~CARRID 
FROM ZC_CAR1 AS ZC_CAR1
LEFT OUTER JOIN ZC_SPFLI1 AS nav_TO_SPFLIZC_SPFLI1_0
ON ZC_CAR1~CARRID = nav_TO_SPFLIZC_SPFLI1_0~CARRID
WHERE nav_TO_SPFLIZC_SPFLI1_0~COUNTRYFR = 'DE' AND ( ZC_CAR1~CARRID = ZC_CAR1__BSAX~CARRID OR ZC_CAR1~CARRID IS NULL AND ZC_CAR1__BSAX~CARRID IS NULL ) )

It's a general subquery, but I never used it and never knew about it (What a shame ๐Ÿ˜„ ๐Ÿ˜„ ) , thought it is a new syntax for a second there ๐Ÿ˜› ๐Ÿ˜› ๐Ÿ˜„ ๐Ÿ˜„

 

BTW I found this virtual elements association filtering concept when I was analysing a standard application to understand the behavior of the Virtual Elements ๐Ÿ™‚ ๐Ÿ™‚

Also, this might be heavy on the performance due to the Sub Query, not sure though. If yes, this can be handled using the Table Function, which I mentioned in the Original Question's answer. Maybe in an another blog post ๐Ÿ™‚

<Update>: Performance is better with Virtual Elements via subquery, check the below blog post.

https://blogs.sap.com/2020/01/27/filtering-on-association-property-using-table-function-amdp-vs-abap...

 

So that's it folks ๐Ÿ™‚ and please share your thoughts and suggestions about this.

 

And Don't forget to check the blogging initiative by phil.cooley  This year is the year of SAP blogs 2020YearofSAPBlogs  ๐Ÿ˜„ ๐Ÿ™‚  I will be creating atleast one blog post per month ๐Ÿ™‚ So don't wait to share your SAP findings and knowledge. Learn, Share & Repeat ๐Ÿ™‚

 

Thanks,

Mahesh

 
23 Comments
whatsap
Participant
helpful.
Vijay
Active Contributor
Very informative maheshkumar.palavalli ... thanks for sharing and keep sharing ๐Ÿ™‚

 

vijay
nabheetscn
Active Contributor
Great stuff Mahesh! Apart from the technical stuff loved your heading?

Some more Technical Details for the Geeks


maheshpalavalli
Active Contributor
0 Kudos
Thanks for the feedback Vijay and surely will ๐Ÿ™‚
maheshpalavalli
Active Contributor
0 Kudos

Thanks Nabheet ๐Ÿ™‚   Adding โ€œGeeksโ€ is the key ingredient ? ?

VenkateshM
Explorer
Awesome Mahesh..!

Well explained and helpful ๐Ÿ™‚
maheshpalavalli
Active Contributor
0 Kudos
๐Ÿ™‚ Thanks Venky for the feedback..
mmcisme1
Active Contributor
Very nice blog!   I always love examples.

Michelle
maheshpalavalli
Active Contributor
0 Kudos
Thanks c436ae948d684935a91fce8b976e5aa7 for the feedback ๐Ÿ™‚
dhiraj_more
Participant
Hey Mahesh,

Thanks for such a nice blog!!! ๐Ÿ˜‰

This is one of the common scenario when it comes to CDS and I always had this question in mind. Even it's always an difficult to explain Functional folks about this technically ๐Ÿ˜‰ but finally I can say we had something which we can do in these scenario's. Thank You very much for this blog I was searching this for long time.

Even I believe it's good to use Virtual elements instead of table function. Because I had experience where table function is also putting lot of load on system when filtering for exact same scenario.

Thanks,

Dhiraj M

 

 

 
maheshpalavalli
Active Contributor
0 Kudos
Thanks for the feedback Dhiraj, really appreciate it ๐Ÿ™‚ ๐Ÿ™‚

Yeah it is a lot better to use Virtual Elements compared to Table function in this scenario, initially I assumed that the table function will be a lot better but I was wrong and when I wrote an another blog post, comparing the both, I found VE is a lot better in this case.

https://blogs.sap.com/2020/01/27/filtering-on-association-property-using-table-function-amdp-vs-abap...

 

-Mahesh
dhiraj_more
Participant
Hey Mahesh,

Yeah we face one issue when we were using AMDP Table function to concatenate system/user status. And when using filters on concatenated fields, we saw it was loading all data into memory which caused high memory usage and performance issues. And then we went for the Virtual elements and everything was fine.

But now one question which is bothering is then we should not use AMDP table functions at all because eventually it will give performance issues and memory consumption issue when we have more data. That's a big disadvantage we have with AMDP and will stop everyone from using it.

So I was wondering is it the issue with AMDP or were we using it incorrectly.

Any thoughts on this or someone with more experience on AMDP can comment: better:)

Thanks,

Dhiraj M
maheshpalavalli
Active Contributor
0 Kudos
I don't know about the performance of AMDP, the only comparison I've done so far is using the virtual elements.

Probably the issue is with the way we use the AMDP. Check this answer comments.

https://answers.sap.com/questions/12596965/slow-performance-in-amdp.html

Probably it works better in other cases.

If you find anything about this topic, make sure to blog about it ๐Ÿ™‚ ๐Ÿ™‚
former_member705525
Discoverer
Hi Mahesh,

Thanks for nice blog and detailed explanation.

We have used virtual element to do filtering on Assocation fileds having (0-n).

I have question how to display this countryfrom  virtual field in Result list Table.We have requirement to show field both (virtual element) in filtering and result table.

Thanks,

Amith

 
maheshpalavalli
Active Contributor
0 Kudos

Hi Amit, Really appreciate the feedback ๐Ÿ™‚ thanks.

I am not sure if it is possible because you need to consider this scenario: What if there are multiple association values, in this case, how can you show them in a single line in the list report(parent entity)? as itโ€™s not possible, we cannot do it, the only way is to navigate to the object page, where we can show them.

Else, you can do via table function as shown below: here you can concatenate all the child and you can also show them in the parent row, but I donโ€™t think this is the right solution. Still would advise you to show them on the object page.

https://blogs.sap.com/2020/01/27/filtering-on-association-property-using-table-function-amdp-vs-abap...

Regards,

Mahesh

mrvila1
Explorer
0 Kudos

Hi Mahesh,

Thanks for this detailed blog.

I wonder if itโ€™s possible to search in assosiation field using the default search element.

I tried this:

@ObjectModel.virtualElement
@ObjectModel.filter.transformedBy: 'ABAP:ZCL_VE_CAR1'
@Search: {defaultSearchElement: true}
cast( ' ' as land1 preserving type ) as country_from

But it doesnโ€™t work as expected. Itโ€™s not event calling the class.

I also tried with an amdp but I get the following error:

Database returned the SQL code 7. Error text: feature not supported: CONTAINS predicates on aggregate functions

Is there any workaround?

Regards,

Jordi

mahesh_sap
Product and Topic Expert
Product and Topic Expert
0 Kudos
Not sure Jordi, by the looks of it, it might not support.. Or did you manage to solve this?
udita10
Explorer
0 Kudos
Hello maheshkumar.palavalli ,

 

Is it true that adding a filter on a field calculated by virtual class can lead to performance downgrading?

Then why is it advised to put a filter on a field whose value is getting calculated via a virtual class?

Thanks,

Udita Saklani
maheshpalavalli
Active Contributor
0 Kudos
I believe it is better at performance Udita. Any reason why you have mentioned that it will result in performance downgrading?

Regards,

Mahesh
udita10
Explorer
0 Kudos
Hi maheshkumar.palavalli

 

The below mentioned blog states :

https://blogs.sap.com/2018/12/20/safeguard-performance-of-abap-cds-views-part-3-rules-for-good-perfo...

It says 'Calculated fields should not be used as main filter field โ€“ filters cannot be pushed down through calculations ' .

 

Thanks ,

Udita Saklani
maheshpalavalli
Active Contributor
0 Kudos

Ahh, Those are for calculated fields, these are virtual elements, so I am guessing it will not be a problem, as this will go as a subquery & will only be triggered when you apply filter on that field.

From that blogpost:

Fields that result from string operations like CONCAT, RTRIM or SUBSTRING, from functions like COALESCE, or from numeric calculations are called calculated fields. Also, fields that are defined within a CASE โ€ฆ WHEN โ€ฆ ELSE construct are calculated fields.

Thanks for a really nice blog! ๐Ÿ™‚

I am trying to implement similar logic in my RAP application,

but it seems transformedBy is not supported ๐Ÿ˜ž

Any idea how to overcome an issue?

Error: "Use of Annotation 'ObjectModel.filter.transformedBy' is not permitted (not released)"

Thanks in advance!


Kamil



dmitriulianov
Discoverer
0 Kudos
Same issue in my case. I tried to implement this type of filter in a BTP application. Did they recently disable this annotation? Leaves literally no way to filter out by 1..n attributes  ๐Ÿ™
Labels in this area