Aggregate CDS View lines before joining into another CDS View

[former_member728618]

Hello Experts,

I have a requirement to perform aggregation on CDS View and join the aggregated result into another CDS View.

Ex: I want a report at PO level where I want to show total PO value and total GR Value.
I tried to create a CDS View on I_PurchaseOrderHistoryAPI with I_MaterialDocument to fetch GR Amount and used 'SUM' aggregation on it.

I create second CDS View on I_PurchaseOrderItemAPI to 'SUM' on NetOrder Amount.

I created a Cube on above 2 views and created a Analytical Query.

But in an example where there was 3 partial GR's against a single PO, I am getting 3 lines from CDS View and because every line contains PO Amount, so in final Analytical Query Report, I am getting 3 times value of PO Amount.

How to solve this problem ?

[former_member132363]

Here are some SAP resources you may review based on your topic "perform | aggregate cds view lines | cds view | cds"

Create and Consume a custom CDS view in SAP S/4 HANA Cloud ...

Mar 22, 2019 ... For this, we will need to have a URL (which will act as an API) to access the view. The following steps need to be performed to do so. 2 2. Create?...

https://blogs.sap.com/?p=750

How to do Unit of Measure Conversion in Sales analytical ...

Jun 3, 2020 ... In SAP's standard sales analytics applications or CDS views, the ... of different products to a uniformed UoM and see the aggregated data in the?...

https://blogs.sap.com/?p=631

How to Achieve YTD/QTD/MTD-based Analysis with Extensibility Tools

Jun 3, 2020 ... Create Customer Cube view (Custom CDS Views). 1. Create a custom CDS view, Add I_BillingDocumentItemCube as the primary data source.

https://blogs.sap.com/?p=632

[former_member132363]

You create your CDS for the GL balances by using as a primary source a dimension or other CDS view and associate the "I_GLAcctBalanceCube" or "I_GLAcctBalance" as a secondary (associated). This will remove the obligation of the Key fields (e.g. line item JE....) and gives you freedom to only pick up the total amount field from the CDS view.

Second possibility is to use the aggregation functionalities on the CDS views so you can aggregate the information by GL.

I hope this answered your question! If it did, please mark it as the 'Best Answer' so I can continue learning and answering questions in the community.