cancel
Showing results for 
Search instead for 
Did you mean: 

Custom CDS view using UNION

former_member306668
Participant
0 Kudos

Hi;

Is it possible to join two CDS views to display the combined result of two views. For example CDS view 1 returns following

Invoice No Invoice Amount Payment Date Payment Amount

1 10,000

CDS view 2 returns following

Invoice No Invoice Amount Payment Date Payment Amount

1 01.01.2019 6,000

1 02.02.2019 3,000

The final view should display following

Invoice No Invoice Amount Payment Date Payment Amount

1 10,000

1 01.01.2019 6,000

1 02.02.2019 3,000

The scenario is that we like to display invoice information, with payment information. In case of multiple payments, the invoice amount is duplicated in the results which leads to incorrect results.

Thanks

Thomas

Accepted Solutions (1)

Accepted Solutions (1)

Feras_Al-Basha
Advisor
Advisor
0 Kudos

Hi Thomas,

I have seen this concept being applied to different scenarios. In case the cardinality association rules at the CDS level i.e. (1:1) or (1:N) in combination with your operator condition for the join fall short of fulfilling the requirement, then you may look at doing a simple join on the field 'invoice no' with association 1:N [since an invoice could have multiple payments]. Thereafter, you may consider creating custom fields to represent 'invoice amount' , 'payment date' and 'payment amount' to reflect the logic in the table you are looking for. Essentially those custom fields would refer to the standard fields but embed a logic for not displaying null values and handling the 1:N hits. In the absence of your CDS details at hand I cannot make further comments.

On another note, a rather simple go-to. Have you considered using the Fiori apps for supplier or customer line items? Essentially with some grouping/filtering of variants you would be able to replicate the information desired.

Hope this information helps,

Thank you,

Feras

Feras_Al-Basha
Advisor
Advisor
0 Kudos

Hi borntosucceed ,

Would like to follow-up on my previous post. Besides the customer/supplier line items apps I had mentioned a few terms and concepts tied together to form and propose a theory. Below please find the reference material.

https://help.sap.com/viewer/788fa4c7585e415187e347e904010cee/1902.500/en-US/8573b810511948c8a99c0672...

https://launchpad.support.sap.com/#/notes/2540696

Looking back at your question.

“Is it possible to join two CDS views to display the combined result of two views. For example CDS view 1 returns following”

The answer is most likely yes. We can achieve this by building solutions in line with the virtual data model. A solution may also involve not one but a set of custom CDS views built on top for the end result.

In technical terms you would be looking at achieving this by creating possibly a set of consumption views that may be expressed in various formats (custom CDS views, analytical queries, KPIs, tiles, etc.)

All consumption views, in this case your custom CDS views are not based directly on database tables. Instead they are based on [interface views] and therefore in determining join conditions the associations among [interface] views become a key success factor.

One may also interpret [interface] views as a technical term for the friendlier concept of business semantics.

In constructing your custom CDS views please refer to weather the underlying CDS view you are basing your view on is a dimension, fact or cube.

Hope this information helps,

Thank you,

Feras

0 Kudos

Hello Feras,

We would like to perform the union in the S/4 cloud Multi tenant. We have the exact situation as above. When you get a chance,could you please provide steps how to do the union in S/4 HANA cloud.

Thank you very much.

Regards

Ram

Feras_Al-Basha
Advisor
Advisor
0 Kudos

Hi Ram,

Please find the below KBA that explain the content rules and information on how to go about analytical scenarios. The blog goes into detail about maintian associations for views with multiple data sources.

https://launchpad.support.sap.com/#/notes/2540696

https://launchpad.support.sap.com/#/notes/0002657021

https://blogs.sap.com/2020/03/06/custom-cds-views-using-multiple-data-sources/

Hope this helps.

Feras

ALE_
Discoverer
0 Kudos

Hi Ram,

we would like to perform the union in S4 cloud MTE, too. When you get a solution please let me know.

Thank you.

Best Regards

Anna

Answers (1)

Answers (1)

epeet
Participant
0 Kudos

ferasbasha3 we have a question on a similar topic. We want to have a view with projects on one row with the belonging project manager and project controller. We use the team member view for this. However, when we have for instance 2 project controllers, the project gets two rows. We want to force the join to pick one row. Is this possible wit a join?

Regards,

Esmee