Skip to Content

Hi all!

Sometimes we need to provide running total (RT) key figures in our BeX-reports (or BW-report created in BWMT-addon for HANA Studio). Usually it’s required to provide RT on monthly basis starting from 1st month of the year.

In the blog I’d like to summarise number of ways to solve the problem: providing RT keyfigure on monthly basis. Here and below I will use the term “report” for simplicity. HCPR stands for HANA Composite Provider in BW.

I count 5 ways to do it. Recently Maksim Alyapyshev in his blog provided one more way to do the same in CDS views.

1. In your report you create 12 restricted key figures. 1st is restricted by Jan, 2nd – by the interval Jan-Feb, 3rd – by the interval Jan – Mar and so on till 12 key figure which is restricted by the interval Jan-Dec.

2. In your report you drill down to calmonth (it doesn’t matter, drill down in rows or in columns) and set up property of key figure “Calculate single values As…” flag “Cumulated” should be set (see picture below).

3. Your report is based on HCPR with key figure dedicated for RT. You fill the values for RT key figure in the method COMPUTE_TABLE of ABAP-class in enhancement spot RSROA. Don’t forget to drill down to calmonth in the report definition. Please see details of using RSROA in the Internet.

4. Your report is based on HCPR with key figure dedicated for RT. One of partprovider is ADSO with corresponding RT key figures. You should fill the values for RT key figure in the transformation to the ADSO. ABAP code sample of computation RT will be provided in the end the blog.

5.Your report is based on HCPR which is based on HANA Calculation view, which is based SQL Script procedure which use SQL SELECT with Window function “SUM(<keyfigure>) OVER PARTITION BY <field1>, …, <fieldn> ORDER BY <field1>, …, <fieldn>.

 

Comparison.

(1) is the most strainghforward approach but it leads to complicated report model due to large number of keyfigures

(2) is the most easy approach but it doesn’t work in SAP Design Studio. If you connect report with RT to Design Studio application you won’t see RT values.

(3) is the most flexible approach (i.m.h.o) but bear in mind performance aspects. Especially if your create reports for dashboards where response time more than coulple of seconds is not sufficient.

(4). If every second of response time is matter, It’s better approach for reading performance aspects than previous. But produces large dataset which stored physically in database.

(5). Real “Code2Data” approach! It works only on HANA Database. Probably it produces the fastest reports but it required HANA SQL knowledge and additional authorisations to create and test HANA Caclulation view models.

That’s all! If you know other ways to create RT please dosn’t hesitate to share knowlege!

 

Appendix. ABAP code to calculate RT in transformation (4). More or less the same algorithm is used both in 3 and 4.

DATA: lt_rp2		TYPE _ty_t_tg_1,
      ls_rp2		TYPE _ty_s_tg_1,
      lv_cm2		TYPE /bi0/oicalmonth2,
      lv_n		TYPE /bi0/oicalmonth2,
      lv_cq1		TYPE /bi0/oicalquart1.
	
      DATA(lv_rec) = lines( RESULT_PACKAGE ) + 1. "need to fill record field in result_package internal table.
      LOOP AT RESULT_PACKAGE INTO ls_rp2.
        lv_cm2  = ls_rp2-calmonth2. " take calmonth2, e.g. 01 for Jan, 02 for Feb and so on
        lv_n = 12 - lv_cm2. " calc number of times the record should be multiplied in loop below
        DO lv_n TIMES.
          ls_rp2-calmonth2 = lv_cm2 + sy-index. " calc corresponding month number
          CONCATENATE ls_rp2-calyear ls_rp2-calmonth2 INTO ls_rp2-calmonth. " ... and calmonth
          lv_cq1 = ls_rp2-calmonth2 DIV 4 + 1. " calc corresponding quater number 
          ls_rp2-calquart1 = lv_cq1.
          CONCATENATE ls_rp2-calyear ls_rp2-calquart1 INTO ls_rp2-calquarter. "... adn calquarter
          ls_rp2-record = lv_rec.
          ls_rp2-/bic/zrt = 'X'. "set field to sepatare rows with RT
          APPEND ls_rp2 TO lt_rp2.
          lv_rec = lv_rec + 1.
        ENDDO.
      ENDLOOP.
      APPEND LINES OF lt_rp2[] TO RESULT_PACKAGE[]. "finally combine "in period"-dataset with RT-dataset.
To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Maksim Alyapyshev

    Sergey, thank you for sharing this!

    I would like to add one more way. It is a variation of your 1 way, but allow to add a characteristics 0calyear and 0calmonth2 in BW Query (and only working if they are added in drill-down)

    Steps:

    1. Create 12 restricted key figures. RKF_1 is restricted by Jan, RKF_2 – by Feb, RKF_3 – by Mar and so on, till RKF_12 which is restricted by Dec.
    2. Create variable MVAR with replacement path on 0calmonth2 characteristic. It will transform characteristic key to number key figure. (SAP Documentation for replacement with characteristic value here).
    3. Create formula with this variable with logic, like this ( MVAR <= 1)*RKF_1 + (MVAR <== 2)*RKF_2 + (MVAR <= 3)*RKF_3 + … + (MVAR <= 12)*RKF_12

    So we will get RT KF in one KF based on the formula and 0calmonth2 in place.

    (1) 
    1. Sergey Shablykin Post author

      Hi, Frederic!

      We consider case (2).

      For example, in your BeX-report if you create calculated key figure ZCKF1 over key figure 0AMOUNT. And flag “Cumulated” is set for 0AMOUNT. The flag doesn’t matter for ZCKF. And it was the problem.

      Remark to the blog: situation above is valid for all reporting clients, not only for Design Studio.

      Regards, Sergey.

      (0) 
  2. Simon Kranig

    Sergey Shablykin :

     

    SQL Script push-down transformation alternative for BW/4H (making use of the time self-join approach … Maksim Alyapyshev )

    Just for getting the idea (Have shortened the field list. not sure if consistently, so does not work to just copy and paste. You will need to create the statement/field list according to your BW source object.)

     

     

     

    -- Getting Period 000 and Periods according to available fiscal year variants
    lt_tmp_all = SELECT * FROM
     (
     ( Select DISTINCT PERIV, '000' as "POPER" from "T009C" WhERE SPRAS = 'E' AND PERIV IN ( SELECT DISTINCT FISCVARNT AS PERIV FROM :INTAB ) )
     UNION ALL
     ( select distinct PERIV, POPER from "T009C" WhERE SPRAS = 'E' AND PERIV IN ( SELECT DISTINCT FISCVARNT AS PERIV FROM :INTAB ) )
     ORDER BY PERIV, POPER ) ;
    
    -- Self Join for included Periods
    lt_time =
    SELECT L.PERIV,
    LPAD(L.POPER,3,'0') as PFPER,
    LPAD(R.POPER,3,'0') as CFPER
    FROM :lt_tmp_all as "L"
    INNER JOIN :lt_tmp_all as "R"
    ON "L"."POPER" >= "R"."POPER" AND
    L.PERIV = R.PERIV;
    
    -- Result
    outtab = SELECT * FROM
    
    
     ( SELECT
           FISCYEAR,
           GL_ACCOUNT,
           VTYPE,
           FISCPER,
           FISCVARNT,
           RECORDMODE,
           CURRENCY,
           BALANCE,
           DEBIT,
           CREDIT,
           TURNOVER,
           QUANTITY,
           GN_R3_SSY,
           RECORD,
           SQL__PROCEDURE__SOURCE__RECORD FROM :INTAB )
           UNION ALL
    
    (
    
    SELECT
          FISCYEAR,
           GL_ACCOUNT,
           VTYPE,
           CURTYPE,
           CONCAT(FISCYEAR,PFPER) AS FISCPER,
           FISCVARNT,
           CURRENCY,
           BALANCE,
           DEBIT,
           CREDIT,
           TURNOVER,
           QUANTITY,
           GN_R3_SSY,
           RECORD,
           SQL__PROCEDURE__SOURCE__RECORD FROM (
    
    
    SELECT
           t1.FISCYEAR,
           t1.FISCPER3,
           t1.GL_ACCOUNT,
           t1.VTYPE,
           t1.CURTYPE,
           t1.FISCPER,
           t1.FISCVARNT,
           t1.CURRENCY,
           t1.BALANCE,
           0 AS DEBIT,
           0 AS CREDIT,
           0 AS TURNOVER,
           0 AS QUANTITY,
           t1.GN_R3_SSY,
           t1.RECORD,
           t1.SQL__PROCEDURE__SOURCE__RECORD,
           t2.PFPER
           FROM
    
     ( SELECT 
           FISCYEAR,
           SUBSTRING(FISCPER,5,3) AS FISCPER3,
           GL_ACCOUNT
           VTYPE,
           CURTYPE,
           FISCPER,
           FISCVARNT,
           CURRENCY,
           TURNOVER AS BALANCE,
           DEBIT,
           CREDIT,
           TURNOVER,
           QUANTITY,
           GN_R3_SSY,
           RECORD,
           SQL__PROCEDURE__SOURCE__RECORD FROM :INTAB )
           as t1 inner join :lt_time as t2
           on t1.FISCPER3 = t2.CFPER AND t1.FISCVARNT = t2.PERIV
           )
           )
           ;
    
    
    ERRORTAB = SELECT '' AS ERROR_TEXT, '' AS SQL__PROCEDURE__SOURCE__RECORD FROM DUMMY WHERE DUMMY <> 'X';
    

     

    (2) 

Leave a Reply