Technical Articles
Single Row to Multiple Rows(Transpose) in CDS views
Introduction:
This Blog post provide information on Transpose(Column to Rows) which we are already using in BW routines and ABAP Coding. While working on ABAP CDS views I came across the similar requirement for my report on CDS views. While I have achieved the goals for my report in CDS views I also wanted to share this article with everyone.
Overview:
We have a requirement of creating Multiple Records from one single record in CDS View. We have many SAP tables where one Record has Key-figures corresponding to different months, For Example :
Table MVER : Material Consumption
This table shows Total Consumption of Material per Plant on a periodic basis. GSV001 (Total Consumption) for period 1,GSV002 (Total Consumption) for period 2, and so on.
Fig 1
For reporting purpose we need to avail these Total Consumption in different rows as per the Period Values and when there is no value for certain period it should not create a different record.
This blog will help how to achieve such analytical requirements in ABAP CDS views.
Version: S4 HANA 1909
Problem Statement:
When we hear Transpose in CDS Views or generating multiple records from single records, We might think it will very difficult to achieve and we often move towards BW solutions or table functions/AMDP in CDS views .Below Solution provides an easy way to achieve this.
Solution:
For these type of scenario generally we move to BW logic, But we can achieve this easily through CDS view by using UNION option. A different set of union on same table with different condition will create different projection of same table.
Step 1:
Create CDS view basic Template :
Fig 2:
Step 2:
Write the code as below , use the table and its fields as per your requirement , here I am using mver table.
To create different record zzmonth field is useful as it will not allow data to aggregate as there will different value for zzmonth as we are using concat of ( gjahr with 01) in first Union, ( gjahr with 02) in Second Union and so on.
Also put where clause to get record with Values only.
Fig 3 :
Result :
Below screen shot shows we are getting result as expected, Comparing with Fig 1(MVER table) we can see we got just five records made from one record ( Column to Rows )as only GSV002, GSV003, GSV004, GSV005 and GSV006 had values.
Fig 4:
Conclusion:
We can achieve Complex Logic of Transpose of Column to Rows (Single row to multiple rows) through ABAP CDS views in very simple way. These Views we can use both as a source to BW Data Sources or as a CDS View based reporting.
Please let me know in in comment section your doubts and questions.
Thank You
Mayank Kumar
Hi Mayank,
A simple way of solution. Thanks
Good work Mayank!!!
Nice Information
Good Work!! Helpful Blog!
SAP S/4HANA provides operational reporting for current data from a single SAP application. SAP BW/4HANA delivers a modern data-warehousing environment that allows organizations to report and plan on current, historical, and external data sources from many SAP and non-SAP applications.
Nice blog with simple solution, very useful.
Very useful blog.
thanks for the post.
Can you please throw some idea.
we are moving our BW operational reports to S/4 Analytics.
we need to generate multiple records based on source data i.e. from source data we will get datefrom and dateto columns along with other columns infor.
we need to split this records by comparing datefrom<=dateto
generate a record..
increase the datefrom=datefrom+1
until the condition satisfies
if the date difference is 10 days we need to generate 10 records,if the difference is some 40 them system automatically generate 40 records ...(it is in while loop in the existing bap code)
your help is appreciated.
Thanks
Sirisha
hi Mayank,
good example.
have you tried in reverse example ?
I have multiple rows but i wanted into a single row in CDS view.
Best regards,
Ramjee Korada
Try this one.. https://blogs.sap.com/2019/07/16/transpose-or-merge-multiple-rows-in-one-row-cds-views/
Can you please guide me for below example?
hello, a query when making multiple unions does not worsen the performance?
And how could it be done using amdp?