Accumulative sum using ABAP CDS (No AMDP function implementation)

[amandwivedi]

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  

}

SAC Story :