Accumulative sum using ABAP CDS (No AMDP function implementation)

Hi!

In this blog post, we will see how to calculate the accumulative sum using ABAP CDS views without using any AMDP function implementation.

First, let us see an example of cumulative amount figures by Calendar Year and Calendar Month.

Calendar Year Calendar Month Amount Cumulative Amount
2022 JAN 10000.00 10000.00
2022 FEB 12000.00 22000.00
2022 MAR 3000.00 25000.00
2022 APR 11000.00 36000.00
2022 MAY 10000.00 46000.00
2022 JUN 12000.00 58000.00
2022 JUL 15000.00 73000.00
2022 AUG 11000.00 84000.00
2022 SEP 12000.00 96000.00
2022 OCT 7000.00 103000.00
2022 NOV 6000.00 109000.00
2022 DEC 13000.00 122000.00

Calculation Method:

Create a special view using self-join of standard CDS view

Demo Scenario:

1. I am going to use the “I_Purchaserequisitionitem”  and the “I_PurchasingDocumentItem”  standard CDS view in S/4HANA for demonstration purposes. This is a basic view for Purchase Requisitions and Purchasing Document Line items in SAP.

@AbapCatalog.sqlViewName: 'BV_RTPR1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Basic View for running total Purchase Req 1'
define view B_RunTotPR1 as select from I_Purchaserequisitionitem PR inner join I_PurchasingDocumentItem as _PurchasingDocumentItem on PR.PurchasingDocument = _PurchasingDocumentItem.PurchasingDocument and PR.PurchasingDocumentItem = _PurchasingDocumentItem.PurchasingDocumentItem
inner join I_CalendarDate as _CalendarDate on PR.PurchaseOrderDate = _CalendarDate.CalendarDate { key PR.CompanyCode, key _CalendarDate.CalendarYear, key _CalendarDate.CalendarMonth, RequestedQuantity as RequestedQuantity, PR.BaseUnit, PurReqnItemCurrency, PurchaseRequisitionPrice as PurchaseRequisitionPrice, RequestedQuantity * PurchaseRequisitionPrice as NetPRAmount, NetAmount as NetAmount, OrderQuantity as OrderQuantity, PR._Currency, PR._UnitOfMeasure, }

Step 2:  Aggregating all the relevant data ( Amount/ quantity ) by Company Code, Calendar Year, and Month.

@AbapCatalog.sqlViewName: 'CTV_RTPR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Composite View for running total Purchase Req 1'
define view CT_RunTotPR as select from B_RunTotPR1 { key CompanyCode, key CalendarMonth, key CalendarYear , sum(RequestedQuantity) as RequestedQuantity , BaseUnit, PurReqnItemCurrency, sum(NetPRAmount) as NetPRAmount, sum(NetAmount) as NetAmount, sum(OrderQuantity) as OrderQuantity, /* Associations */ _Currency, _UnitOfMeasure
}
group by CompanyCode, CalendarYear, CalendarMonth, BaseUnit, PurReqnItemCurrency

3. Special view that we are going to join with the above view.

@AbapCatalog.sqlViewName: 'BV_PRPRT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Special View for Accumulative Sum'
define view /CGDC/B_PRPRT as select from I_YearMonth as t1 inner join I_YearMonth as t2
on t1.CalendarYear = t2.CalendarYear and t1.CalendarMonth <= t2.CalendarMonth { t1.CalendarMonth, t2.CalendarMonth as CumCalendarMonth, t1.CalendarYear, case when t1.CalendarYear = t2.CalendarYear and t1.CalendarMonth = t2.CalendarMonth then 1 else 0 end as RealRowFlag } 

Special%20View%20Output

Real row flag here can be used to calculate the actual amounts.

4. New view using the Special view and aggregated amount values.

@AbapCatalog.sqlViewName: 'BV_RTPR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CUBE View for running total Purchase Req' @Analytics.dataCategory:#CUBE
define view B_RunTotPR as select from CT_RunTotPR PR
inner join B_PRPRT as B on B.CalendarYear = PR.CalendarYear and B.CalendarMonth = PR.CalendarMonth association [1] to I_CalendarMonth as _CumCalendarMonth on _CumCalendarMonth.CalendarMonth = B.CumCalendarMonth { key PR.CalendarYear, key PR.CalendarMonth, @ObjectModel.foreignKey.association: '_CumCalendarMonth' B.CumCalendarMonth, B.RealRowFlag, @ObjectModel.foreignKey.association: '_UnitOfMeasure' @Semantics.unitOfMeasure: true BaseUnit, @Semantics.quantity.unitOfMeasure: 'BaseUnit' @DefaultAggregation: #SUM PR.RequestedQuantity as CumRequestedQuantity, @Semantics.quantity.unitOfMeasure: 'BaseUnit' @DefaultAggregation: #SUM PR.RequestedQuantity* B.RealRowFlag as PRQuantity, @ObjectModel.foreignKey.association: '_Currency' @Semantics.currencyCode: true PurReqnItemCurrency, @Semantics.amount.currencyCode: 'PurReqnItemCurrency' @DefaultAggregation: #SUM PR.NetPRAmount * B.RealRowFlag as PRAmount, @Semantics.amount.currencyCode: 'PurReqnItemCurrency' @DefaultAggregation: #SUM PR.NetPRAmount as CumPRAmount, @Semantics.amount.currencyCode: 'PurReqnItemCurrency' @DefaultAggregation: #SUM NetAmount as CumPOAmount, @Semantics.amount.currencyCode: 'PurReqnItemCurrency' @DefaultAggregation: #SUM NetAmount* B.RealRowFlag as POAmount, @Semantics.quantity.unitOfMeasure: 'BaseUnit' @DefaultAggregation: #SUM OrderQuantity as CumPOQuantity, @Semantics.quantity.unitOfMeasure: 'BaseUnit' @DefaultAggregation: #SUM OrderQuantity * B.RealRowFlag as POQuantity, _Currency, _UnitOfMeasure, _CumCalendarMonth }

Result :

Cumulative figures were obtained using ABAP CDS.

Bonus :

Visualization in SAP Analytics Cloud

Using this consumption view and connecting to Live BW query we can create interactive visualization in SAC.

Consumption view:

@AbapCatalog.sqlViewName: '/CGDC/CV_RTPR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Analytics.query: true
@EndUserText.label: 'Consumption View for PO Savings Dashboard in SAC'
define view C_RunTotPR as select from B_RunTotPR { @EndUserText.label: 'Calendar Year' key CalendarYear, @EndUserText.label: 'Calendar Month' key CalendarMonth, @EndUserText.label: 'Cummulative Month' CumCalendarMonth, @EndUserText.label: 'Flag' RealRowFlag, BaseUnit, @EndUserText.label: 'Cummulative PR Quantity' CumRequestedQuantity, @EndUserText.label: 'PR Quantity' PRQuantity, PurReqnItemCurrency, @EndUserText.label: 'PR Amount' PRAmount, @EndUserText.label: 'Cummulative PR Amount' CumPRAmount, @EndUserText.label: 'Cummulative PO Amount' CumPOAmount, @EndUserText.label: 'PO Amount' POAmount, @EndUserText.label: 'Cummulative PO Quantity' CumPOQuantity, @EndUserText.label: 'PO Quantity' POQuantity }

SAC Story :