Product Information
Creating Custom CDS Views and Analytical Query – A step by step guide
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.
Step 2: Add data sources
Click on Primary Data Source and select
I_PurchaseOrderAPI01
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
I_SuplrInvcItemPurOrdRefAPI01
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 .
Thanks
Payel Lakhotia
Thanks Payel for the Blog. Very Useful information.
Junaid
Thanks a lot for the motivation to write the blog .
Thank you Payel for sharing knowledge
Thank you Shailesh.
Hi Payel, the blog is very informative.
Glad that you liked it.
Hello Payel,
Very informative.
I just started working on S4Hana Analytics and SAC Analytics.
I was looking for some step to find out the link between CDS views, the corresponding Data source, the basic S/4 Hana table and what are the reports/KPIs provided by SAP using one CDS view.
Taking your example, I_PurchaseOrderAPI01 , what are the reports, or other CDS views or queries using this CDS view. simply, where used list.
Also, please can you share the info on the difference or meaning of different CDS views
A_* , I_* , C_* E* and AP* etc.
regards
VTR
Payel Somani ,
Hello Payel,
Hope you have not seen my previous comment.
I am trying to see what are the standard reports/ CDS view and queries.
Thanks in advance
regards,
Viswanathan T R
Hey Vishwanathan,
For ACDOCA table, the Standard CDS View is I_GLAcctBalanceCube and the Standard Query is 2CCFITRIALBALQ0001.
The query is based on CDS view C_Trialbalanceq0001. You can extend the fields in the CDS view using eclipse.
If you want to extend the fields in the ACDOCA table, you can do it using "Custom Fields and Logic" Application on Fiori.
Thanks Payel, for the informative blog, but one question Navigation Panel/ Sorting/Filters etc button are not visible when i preview the query, any idea what can be the issue?
panel missing
option marked in yellow above is not visible in my system
Thanks
Pooja
Hi Payel Lakhotia,
first of all thank you for your sharing your knowledge.
I have a question for step 2 and step 3.
How do you find the right Data Sources you need for your case?
When I work with queries i only need the right table to find and identify my fields.
With CDS Views this is not so easy.. Do I really need to go through all results for my search option?
Or is there an easier way to find the right fields i want to display
I am pleased and thank you for your feedback
BR
Ivan