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.
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.- SAP Managed Tags:
4 Comments
