Skip to Content
Technical Articles
Author's profile photo Mayank Kumar

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

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo YUNUS KALDIRIM
      YUNUS KALDIRIM

      Hi Mayank,

      A simple way of solution. Thanks

      Author's profile photo Muruga MuthuKrishnan
      Muruga MuthuKrishnan

      Good work Mayank!!!

      Author's profile photo Abhinav chaturvedi
      Abhinav chaturvedi

      Nice Information

      Author's profile photo Kristen Waston
      Kristen Waston

      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.

      Author's profile photo vinod bokade
      vinod bokade

      Nice blog with simple solution, very useful.

      Author's profile photo Sirisha G
      Sirisha G

      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

       

      Author's profile photo Ramjee Korada
      Ramjee Korada

      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

      Author's profile photo Deepak Duhan
      Deepak Duhan

      Try this one.. https://blogs.sap.com/2019/07/16/transpose-or-merge-multiple-rows-in-one-row-cds-views/

      Author's profile photo Shrikant Patil
      Shrikant Patil

      Can you please guide me for below example?