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.
Step 2: Add data sources
Click on Primary Data Source and select
Now select the Associated Data Source
Step 3: Add join condition
Select Cardinality and click on Add to choose the field for association
In the value select on help and add the same field from the primary data source . In our case it is the purchase order.
Similarly click on the join condition for third data source
Step 4: Go to Tab Elements to select the field you require
Click on Add Elements to Add the fields
The 3 data sources appear .
Select on the field you want from the 3 data sources
Below are the selected field
Step 5 : Click on the next tab element properties
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.
Click no operation
Now you can preview to check
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.
Step 2: Click on Field selection
Select all the fields you want in your query.
Step 3: Click on save draft and select the Preview button in the bottom
Step 4: Go to navigation panel and select the fields whether you want them in Row axis or column axis.
This is how the final query looks like
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 .