Technical Articles
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
}
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: '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
}
This is a bit like the SUM feature of ABAP SQL queries that we have had for the last 25 years.
Hi Paul,
Thanks for your response.
Unlike SQL Server we can not use Function Over Partion By to calculate running sum total using CDS as it is not supported yet.
Hence, this gives an alternative to a beginner to calculate cummulative sums without using AMDP function implementation.