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.

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  
}

SAC Story :