Skip to Content
Product Information
Author's profile photo Payel Somani

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

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Junaid ALAM
      Junaid ALAM

      Thanks Payel for the Blog. Very Useful information.

      Junaid

      Author's profile photo Payel Somani
      Payel Somani
      Blog Post Author

      Thanks a lot for the motivation to write the blog .

      Author's profile photo Shailesh Sontakke
      Shailesh Sontakke

      Thank you Payel for sharing knowledge

      Author's profile photo Payel Somani
      Payel Somani
      Blog Post Author

      Thank you Shailesh.

      Author's profile photo Dimpu Ajay
      Dimpu Ajay

      Hi Payel, the blog is very informative.

       

      Author's profile photo Payel Somani
      Payel Somani
      Blog Post Author

      Glad that you liked it.

      Author's profile photo Viswanathan Ramakrishnan
      Viswanathan Ramakrishnan

      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

      Author's profile photo Viswanathan Ramakrishnan
      Viswanathan Ramakrishnan

      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.

      1. Can we change the sequence of the fields in the standard reports?
      2. Can we add some new field ( which is there in the CDS view ) to the report?
      3. How to find reports based on on given CDS view or CDS views based on table, say, ACDOCA

      Thanks in advance

       

      regards,

      Viswanathan T R

      Author's profile photo Anudeep Appidi
      Anudeep Appidi

      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.

      Author's profile photo Pooja Mittal
      Pooja Mittal

      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%20missing

      panel missing

      option marked in yellow above is not visible in my system

       

       

      Thanks

      Pooja

      Author's profile photo Ivan Kostukov
      Ivan Kostukov

      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