Dear All ,
I am working on S4HANA Cloud Implementation Project for Procurement area . During my Implementation experience I came across the requirement to create a report that will have Purchase Order Header , Line item details as well as the Invoicing details. There were no standard reports to give me this details . Hence I used the custom CDS views and created Analytical Query to build the report .
This article will provide you the step by step guide how to create custom CDS Views and analytical Query in S4HANA CLOUD
Step 1: Go to the App Custom CDS view
Click on Create and fill in the below details .Note only when the Scenario is Analytical Cube you can use the CDS view to create an analytical Query from it.
![](/legacyfs/online/storage/blog_attachments/2021/07/1-123.png)
Step 2: Add data sources
![](/legacyfs/online/storage/blog_attachments/2021/07/2-36.png)
Click on Primary Data Source and select
I_PurchaseOrderAPI01
![](/legacyfs/online/storage/blog_attachments/2021/07/3-28.png)
Now select the Associated Data Source
![](/legacyfs/online/storage/blog_attachments/2021/07/4-23.png)
![](/legacyfs/online/storage/blog_attachments/2021/07/5-20.png)
Step 3: Add join condition
![](/legacyfs/online/storage/blog_attachments/2021/07/6-18.png)
Select Cardinality and click on Add to choose the field for association
![](/legacyfs/online/storage/blog_attachments/2021/07/7-16.png)
In the value select on help and add the same field from the primary data source . In our case it is the purchase order.
![](/legacyfs/online/storage/blog_attachments/2021/07/8-12.png)
![](/legacyfs/online/storage/blog_attachments/2021/07/9-13.png)
Similarly click on the join condition for third data source
I_SuplrInvcItemPurOrdRefAPI01
![](/legacyfs/online/storage/blog_attachments/2021/07/10-11.png)
![](/legacyfs/online/storage/blog_attachments/2021/07/11-13.png)
Step 4: Go to Tab Elements to select the field you require
![](/legacyfs/online/storage/blog_attachments/2021/07/12-10.png)
Click on Add Elements to Add the fields
![](/legacyfs/online/storage/blog_attachments/2021/07/13-12.png)
The 3 data sources appear .
![](/legacyfs/online/storage/blog_attachments/2021/07/14-10.png)
Select on the field you want from the 3 data sources
![](/legacyfs/online/storage/blog_attachments/2021/07/15-6.png)
Below are the selected field
![](/legacyfs/online/storage/blog_attachments/2021/07/16-6.png)
Step 5 : Click on the next tab element properties
![](/legacyfs/online/storage/blog_attachments/2021/07/17-6.png)
Scroll to the bottom , for the field Net Amount add an aggregation method. Please note for the fields with the type measure it is necessary to define the aggregation method.
![](/legacyfs/online/storage/blog_attachments/2021/07/18-5.png)
![](/legacyfs/online/storage/blog_attachments/2021/07/19-3.png)
Click no operation
Now you can preview to check
![](/legacyfs/online/storage/blog_attachments/2021/07/20-3.png)
You can then click on Publish to use this CDS View for query.
Creating a query using the CDS view.
Step 1: Go to the app custom analytical queries
Click on create and in Data source use the CDS view that we have created.
![](/legacyfs/online/storage/blog_attachments/2021/07/21-3.png)
Step 2: Click on Field selection
Select all the fields you want in your query.
![](/legacyfs/online/storage/blog_attachments/2021/07/22-3.png)
Step 3: Click on save draft and select the Preview button in the bottom
![](/legacyfs/online/storage/blog_attachments/2021/07/23-2.png)
Step 4: Go to navigation panel and select the fields whether you want them in Row axis or column axis.
![](/legacyfs/online/storage/blog_attachments/2021/07/24-2.png)
This is how the final query looks like
![](/legacyfs/online/storage/blog_attachments/2021/07/25-2.png)
To conclude , above steps were an example using the Procurement CDS Views , you can take any CDS view from your respective module and then try to create your own query out of it .
Thanks
Payel Lakhotia