Overcoming Limitations in Public Cloud: Solving SUM of Rows in Custom CDS Views Using Eclipse ADT

[sunilpadhy12]

In SAP Reports development, one challenge faced by developers working with CDS Views in public cloud is the inability to perform operation like SUM of rows in Custom CDS Views. 

While ‘Custom CDS Views’ application (key-user extensibility) in public cloud aims to simplify many aspects on creating a report, but some complex calculations such as summing values over multiple rows or complex calculated logical operations is not possible using ‘Custom CDS Views; app.

Problem Statement: SUM of Rows in Custom CDS Views 
In On Premise System, developers have more flexibility to create complex calculations, utilizing function like Sum(). But when working with Custom CDS Views in S/4Hana Public Cloud certain operations such as summing values across multiple rows are not supported. 
 
Solution: Using Eclipse ADT to Solve the Requirement 
Let's consider an example where multiple Material Documents (Material Docs) exist for the same material, and we need to sum up the quantities for that material.

Material Doc year	Material DOC	Material DOC Item	Material	Plant	Quantity	EntryUnit
2024	50000001	1	test	1001	10	EA
2024	50000002	1	test	1001	50	EA
2024	50000003	1	test	1001	5	EA
2024	50000004	1	test	1001	100	EA
2024	50000005	1	test	1001	65	EA
2024	50000006	1	test	1001	40	EA
2024	50000006	2	test	1001	25	EA

 
The requirement here is to sum the quantities for the same material (test) and plant (1001), regardless of the individual material document entries. 
we can solve the issue on ABAP Development Tools (ADT) in Eclipse.

Steps to Implement the Solution: 
  Create a Data Definition: In Eclipse, first, create a data definition for your custom CDS view and define view. 

Here is the example code:

define view TotalQuantity
as select from I_MaterialDocumentItem_2 as a 
{

 key a.Material,  key a.Plant, 

      a.EntryUnit as Meins, 

      a.QuantityInBaseUnit, 

      @Semantics.quantity.unitOfMeasure: 'Meins' 

      sum(a.QuantityInBaseUnit) as TotalQuantity 

}

group by 

  a.Material, 

  a.Plant, 

  a.EntryUnit 

•  I_MaterialDocumentItem_2 is the CDS view that contains all the data related to individual Material Documents and quantity with material. 
•  The sum(a.QuantityInBaseUnit) is the operation,summing the QuantityInBaseUnit across multiple rows based on the material and plant. 
•  group by is used to group the records by Material, Plant, and EntryUnit to perform the sum. 
 
After defining save and activate

The Result: 

Material	Plant	Total Quantity
test	1001	295

@Oleg_Vokh @HakanHaslaman @krishnanunni_sudhakaranpi @Andre_Fischer @yevgen_trukhin @julieplummer20 @kaitorsten_schipper 
SAP S/4HANA Cloud Public Edition 

[ujaleswar · Accepted solution]

Thanks, for the solution @sunilpadhy12 

[sunilpadhy12 · Accepted solution]

You are welcome