Single Row to Multiple Rows(Transpose) in CDS views

[mayanksap]

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