Friday, 6 October 2017

Methods of Running Total calculation in ABAP CDS views

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:

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

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.

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.

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

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:

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

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

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

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).

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

3. New join in basic view was created:

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

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.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.

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

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

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

Data preview by only 1 position of all documents.

SAP ABAP Tutorials and Materials, SAP ABAP Certifications

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.

1 comment: