Skip to Content
Author's profile photo Maksim Alyapyshev

Methods of Running Total calculation in ABAP CDS views

Hi!

The post is dedicated a of important aspect of reporting – calculating of Running Total or Cumulative Key Figures.

There is an example of cumulative amount key figure by fiscal year periods:

Calculation Methods

I could propose 3 methods of realization the calculation behavior in ABAP CDS views:

  1. Use SQL windows functions in ABAP CDS views based on Table Function.You could find technical step-by-step procedure in blog. Idea of running total calculation is similar to described in blog within HANA Calculation Views.
  2. Use basic view with parameters and union of many select statements in composite view. If we have 12 time periods there will be union of 12 select statements.
  3. Use join with special view that will multiply posting rows correctly for cumulative key figure calculation. The last, but I hope not the least, calculation method this post is about.

Demo Scenario

1.I am going to use “Universal Journal Entry” (ACDOCA) table in S/4HANA for demonstration purpose. This is basic view for financial postings selections:

2. Special view that we are going to join with:

I use standard view with company code field in key part just for demo purpose. Different company codes could have different fiscal variants and different number of periods (as I know). Hope it doesn’t make example to complicated. Total number of rows generated for one company code and 16 periods is 136 (sum of arithmetic progression).

3. New join in basic view was created:

4. Create other view with filtration and aggregation for demo purpose. In real life solution we could create cube view and needed analytical query views.

Data preview by only one document  position. It was posted in 007 period of 2017 year.

Data preview by only 1 position of all documents.

Cumulative amount key figure is working correctly!

Result

Main methods of running total calculation were discussed.

One method “with join” was considered in detail.

P.S.

If more difficult calculations are needed, then RealRowFlag could help to distinguish between cumulative and normal keys figures and combine them accordingly.

 

Thank you for attention!

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Great Blog, thank you!

      Author's profile photo Timothy Hughes
      Timothy Hughes

      Trying to replicate this for Suite on Hana.  Do you know of table(s) to replicate your logic for  ZFI_I_RTPERIO ?

       

      I came up with the below, which will miss periods with no posting data.   I suppose I could create a ZTABLE, but would prefer not to.

       

      Thanks,

       

      Tim

      define view ZDDL_FI_B_PERS as select distinct from bkpf
      
      {
      
      bukrs as bukrs,
      gjahr as gjahr,
      monat as monat
      
      }
      Author's profile photo Maksim Alyapyshev
      Maksim Alyapyshev
      Blog Post Author

      Hi, Tim!

      It is based on standard CDS views but in S/4. At the bottom there are tables:

      • T001 for Company codes
      • FINSC_PERIOD Fiscal Periods

      they are joined together on fiscal varial (e.i. K4).

      I think that it's not good idea to to use select distinct due to performance reasons.

       

      BR, Maksim

       

      Author's profile photo Leigh Mason
      Leigh Mason

      Hey Maksim,

      Finally had this problem to solve recently.  This is an old thread but to add to the conversation, I recently had to do this in a Table Function and access via CDS View so I could show a stacked column chart in Fiori Elements.  I figured there had to be a better way to do this surely 🙂  Your solution was good, but you can now use the OVER function in open SQL.  Here is the code snippet from the implementation class for the table function:

       

      class ZCL_TF_SOME_CLASS definition
        public
        final
        create public .
      
      public section.
       "Include interface
       INTERFACES if_amdp_marker_hdb.
      
       CLASS-METHODS GET_CUMULATIVE_AMOUNT
                        FOR TABLE FUNCTION ZTF_SOME_TABLE_FUNCTION.
      
      protected section.
      private section.
      ENDCLASS.
      
      
      
      CLASS ZCL_TF_SOME_CLASS IMPLEMENTATION.
      
      METHOD GET_CUMULATIVE_AMOUNT
               BY DATABASE FUNCTION FOR HDB
               LANGUAGE SQLSCRIPT
               OPTIONS READ-ONLY
               USING <<SOME TABLE NAME>>.
      
      return
      
      SELECT DISTINCT mandt as client, partner_id as Partner, year(credit_date) as Credit_Year, month(credit_date) as Credit_Month,
          sum(income_credit)
              over( PARTITION by year( credit_date), month( credit_date)
                    ORDER by year( credit_date), month( credit_Date )) as Month_Income,
          sum(income_credit)
              over ( PARTITION by year( credit_date )
                    ORDER by month( credit_date)) as Cumulative_Income,
          credit_currency as currency
         from <<SOME_TABLE_NAME>> where credit_currency = 'AUD' and partner_id = p_partner order by year(credit_Date), month( credit_date);
      
      
        ENDMETHOD.
      ENDCLASS.

       

      The key bit is being able to use the OVER / PARTITION operator and using order by you can have your cake and eat it too 🙂

       

      Admit that it's not solely my work, you can find the example HERE.

       

      Hope this helps anyone else doing a cumulative total, at least there is another option 😀

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat

      Hi Maksim,

      FYI, the same can be archived easier on front-end side when consuming ABAP CDS View in Analysis for Office

      Regards, Uladzislau

       

      Author's profile photo Ranjeet Srivastav
      Ranjeet Srivastav

      Hi Maksim Alyapyshev,

      Thanks for this post, one thing is confusing me for created second cds view 2. ZFI_I_RTPERIO, you have used same cds view I_FiscalYearPeriodForCmpnyCode in inner join, why?

      You have already created first cds view  as 1.ZFI_I_ACDOCA then again you have created third cds view 3.ZFI_I_ACDOCA as same name of first cds view.

      I have created cds view which you have explained, but I am not getting expecting result.

      My requirement is same as like your example, please clear my doubt.

      Regards,

      Ranjeet

      Author's profile photo M. Ahmad
      M. Ahmad

      Another amazing blog by Maksim