Concatenation of column values from multiple records and bring the data into single record in ABAP CDS views. ABAP CDS views built in function Concat will concatenate column values from same record which is the limitation of available built in functions. This blog will help how to achieve such analytical requirements in ABAP CDS views.
Version: S4 HANA 1909
With available built-in functions it is not possible to achieve the requirement to concatenate columns from multiple records and show it as single record.
Above said requirement can be achieved in typical ABAP coding or through HANA views which is straight forward and known fact. Here the circumstance is that we have to achieve this requirement in ABAP CDS views.
If it is not possible in ABAP CDS views, then such reports should move to BW which is not wiser decision. With available built-in functions, annotations it is not possible for us to achieve this requirement however through Table functions and AMDP approach we can achieve such requirements in ABAP CDS views.
Please follow the below steps
Create an ABAP CDS with template as Table function
For illustration purpose we are not using parameterized based CDS table function. If needed, we can use parameters in table function.
In table function definition we need to define the return parameters which are required to use in ABAP CDS views.
Activate the table function.
Create the ABAP Class in which required logic should be implemented for complex logic calculations/manipulations.
Give the ABAP class name
Default class template will appear in new window
Mention the highlighted interface so that the method will be executed in database level.
Implement the required logic in class implementation section.
Since the method is enabled for AMDP so data can be fetched into an internal table for processing. In the above snapshot data from VBAP table is inserted into IT_SOITM internal table. RETURN parameter in the method will transfer the processed data to table function.
Function STRING_AGG will concatenate the item from multiple records, to show the unique Sales order use GROUP BY in SELECT query. Since the records are grouped by SO and items are concatenated so unique SO will be the output which is the expected desired result.
Go to Table function and opt for data preview
Complex logics as above are not possible to achieve through ABAP CDS views model however through table functions complex logics can be achieved by leveraging the existing SQL functions.