Cumulative sum in ALV report

Former Member · ‎01-02-2014

HI , In Alv report need to how to sum all DMBTR for perticular EBELN from EKBE table.I dont know how to write a code for each line item summation. like: EBELN DMBTR 4000005 10000 4000005 5000 4000005 15000 here what i need i need to sum all values of DMBTR for EBELN value for each line item. Thanks and Regards Sankil

deependra_shekhawat3 · ‎01-02-2014

Hi Sankil,

You can use COLLECT stement after sorting records based on EBELN.

sort it_ekbe by ebeln.

**In loop

COLLECT wa_ekbe INTO it_ekbe_final.

**endloop.

Deependra

Former Member · ‎01-02-2014

HI, I want so sum all the value of DMBTR for perticular EBELN.

Former Member · ‎01-02-2014

in output i need like in ALV output, so for this i think i need to add all line item of DMBTR for EBELN. EBELN DMBTR 4000005 30,000

Former Member · ‎01-02-2014

Hi Sankil,

Please understand the requirement, What exactly you want to disply from EKBE ( goods recevied value/total PO value ) ??? . this table will show the complete material flow of the business in which case you required the information.

See example below, for one PO line item multiple entries with the different history

Former Member · ‎01-02-2014

Yes in the ALV i have one customized column ''Cumulatiove spend per IR' and in this column value will come summation of DMBTR for any perticular EBELN for any perticular vendor

Former Member · ‎01-02-2014

I need a total PO value for perticular EBELN

Former Member · ‎01-02-2014

Check this

TABLES ekbe.

TYPES: BEGIN OF ty_ekbe,
         ebeln TYPE ebeln,
         ebelp TYPE ebelp,
         dmbtr TYPE dmbtr,
         END OF ty_ekbe.

TYPES: BEGIN OF ty_final,
         ebeln TYPE ebeln,
         dmbtr TYPE dmbtr,
         END OF ty_final.

DATA: it_ekbe TYPE STANDARD TABLE OF ty_ekbe WITH HEADER LINE,
       it_final TYPE STANDARD TABLE OF ty_final WITH HEADER LINE..

SELECT ebeln ebelp dmbtr FROM ekbe INTO TABLE it_ekbe
           WHERE ebeln = '3000000049' AND
                 vgabe = '1'.
LOOP AT it_ekbe.

   MOVE: it_ekbe-ebeln TO it_final-ebeln,
         it_ekbe-dmbtr TO it_final-dmbtr.

   COLLECT it_final.
   CLEAR: it_ekbe,
         it_final.
ENDLOOP.

pass the it_final to the FM ALV

philipdavy · ‎01-02-2014

TYPES: BEGIN OF TY_EKBE,

EBELN TYPE EKBE-EBELN,

DMBTR TYPE EKBE-DMBTR,

SUM TYPE DMBTR,

END OF TY_EKBE.

DATA: IT_EKBE TYPE STANDARD TABLE OF TY_EKBE,

IT_FINAL TYPE STANDARD TABLE OF TY_EKBE,

WA_FINAL LIKE LINE OF IT_FINAL,

WA_EKBE LIKE LINE OF IT_EKBE,

SUM TYPE DMBTR,

TEMP TYPE DMBTR.

SELECT EBELN DMBTR FROM EKBE INTO TABLE IT_EKBE UP TO 10 ROWS.

SORT IT_EKBE BY EBELN.

LOOP AT IT_EKBE INTO WA_EKBE.

SUM = WA_EKBE-DMBTR + SUM.

AT END OF EBELN.

MOVE WA_EKBE-EBELN TO WA_FINAL-EBELN.

MOVE SUM TO WA_FINAL-SUM.

APPEND WA_FINAL TO IT_FINAL.

CLEAR: SUM,WA_FINAL.

ENDAT.

ENDLOOP.

LOOP AT IT_FINAL INTO WA_FINAL.

WRITE: / WA_FINAL-EBELN, WA_FINAL-SUM.

ENDLOOP.

You can also try using SUM statement as well as collect statement.

Regards,

Philip

Former Member · ‎01-02-2014

Thanks for your response philip, Here in ur example EBELN value(400000000) has 3 DMBTR value what i need like, 400000000 8149(Cummation of all 40000000) 400000001 7921 in the final output of alv.also sending my code. here in Cumulatiove spend per IR i need this value in front of that ebeln. *&---------------------------------------------------------------------* *& Report ZPOREPORT *& *&---------------------------------------------------------------------* *& *& *&---------------------------------------------------------------------* REPORT ZPOREPORT. TABLES: LFA1,LFB1,EKKO,EKPO. TYPE-POOLs: SLIS. *For Input Screen: SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-002 . PARAMETERS: P_BUKRS LIKE LFB1-BUKRS OBLIGATORY DEFAULT '0219'. SELECT-OPTIONS: S_LIFNR FOR LFA1-LIFNR, S_ZZEXP FOR LFB1-ZZEXPDT, s_PERIOD FOR SY-DATUM. SELECTION-SCREEN END OF BLOCK B1. *internal teble TYPES: Begin of ty_bee, Lifnr type lfa1-lifnr, Name1 type lfa1-name1, Ebeln type ekbe-ebeln, Dmbtr type ekbe-dmbtr, Cum_po(40) type C, Cum_ir(40) type C, Zbee type lfb1-zzbee, * date type sy-datum, Zexpdt type lfb1-zzexpdt, Zbeetyp type lfb1-zzbeetyp, Zblack type lfb1-zzblack, Zbfem type lfb1-zzbfem, zvadd type lfb1-zzvadd, zbfemo type lfb1-zzbfemo, zentben type lfb1-zzentben, End of ty_bee. DATA: T_bee TYPE STANDARD TABLE OF ty_bee WITH HEADER LINE, WA_bee TYPE Ty_bee. DATA: FIELDCATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE, GD_LAYOUT TYPE SLIS_LAYOUT_ALV, GD_REPID LIKE SY-REPID, G_SAVE TYPE C VALUE 'X', G_VARIANT TYPE DISVARIANT, GX_VARIANT TYPE DISVARIANT, G_EXIT TYPE C. **PERFORM DECLARATIONS START-OF-SELECTION. * *SELECT LIFNR NAME1 FROM LFA1 INTO CORRESPONDING FIELDS OF TABLE T_BEE * WHERE LIFNR IN S_LIFNR. * *SELECT LIFNR ZZBEE ZZEXPDT ZZBEETYP ZZBLACK ZZBFEM ZZVADD * ZZBFEMO ZZENTBEN FROM LFB1 INTO CORRESPONDING FIELDS OF TABLE T_BEE * WHERE ZZEXPDT IN S_ZZEXP * AND LIFNR IN S_LIFNR. SELECT a~LIFNR a~NAME1 b~ZZBEE b~ZZEXPDT b~ZZBEETYP b~ZZBLACK b~ZZBFEM b~ZZVADD b~ZZBFEMO b~ZZENTBEN FROM LFB1 AS b INNER JOIN LFA1 AS a ON b~LIFNR EQ a~LIFNR INTO CORRESPONDING FIELDS OF TABLE T_BEE WHERE a~LIFNR IN S_LIFNR AND b~ZZEXPDT IN S_ZZEXP. SELECT a~LIFNR a~BUKRS a~EBELN b~DMBTR FROM EKBE AS b INNER JOIN EKKO AS a ON b~EBELN EQ a~EBELN INTO CORRESPONDING FIELDS OF TABLE T_BEE WHERE a~LIFNR IN S_LIFNR AND a~BUKRS EQ P_BUKRS. END-OF-SELECTION. * PERFORM DATA_RETRIVEL. PERFORM BUILD_FIELDCATALOG. PERFORM DISPLAY_ALV_REPORT. PERFORM TOP-OF-PAGE. FORM BUILD_FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'LIFNR'. FIELDCATALOG-SELTEXT_M = 'Vendor Number'. FIELDCATALOG-COL_POS = 1. FIELDCATALOG-OUTPUTLEN = 15. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'NAME1'. FIELDCATALOG-SELTEXT_M = 'Vendor Name'. FIELDCATALOG-COL_POS = 2. FIELDCATALOG-OUTPUTLEN = 15. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'CUM_PO'. FIELDCATALOG-SELTEXT_L = 'Cumulative Spend per PO Commitments'. FIELDCATALOG-COL_POS = 3. FIELDCATALOG-OUTPUTLEN = 55. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'CUM_IR'. FIELDCATALOG-SELTEXT_L = 'Cumulative Spend per IR'. FIELDCATALOG-COL_POS = 4. FIELDCATALOG-OUTPUTLEN = 50. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'ZZBEE'. FIELDCATALOG-SELTEXT_M = 'Bee Level'. FIELDCATALOG-COL_POS = 5. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'ZZEXPDT'. FIELDCATALOG-SELTEXT_M = 'Expiry Date'. FIELDCATALOG-COL_POS = 6. FIELDCATALOG-OUTPUTLEN = 15. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'ZZBEETYP'. FIELDCATALOG-SELTEXT_M = 'Bee Type'. FIELDCATALOG-COL_POS = 7. FIELDCATALOG-OUTPUTLEN = 10. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'ZZBLACK'. FIELDCATALOG-SELTEXT_L = 'Black Ownership(>50%)'. FIELDCATALOG-COL_POS = 8. FIELDCATALOG-OUTPUTLEN = 25. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'ZZBFEM'. FIELDCATALOG-SELTEXT_L = 'Black Female(>50%)'. FIELDCATALOG-COL_POS = 9. FIELDCATALOG-OUTPUTLEN = 30. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'ZZVADD'. FIELDCATALOG-SELTEXT_L = 'Value Adding Enterprise'. FIELDCATALOG-COL_POS = 10. FIELDCATALOG-OUTPUTLEN = 25. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'ZZBFEMO'. FIELDCATALOG-SELTEXT_L = 'Black Women Owned(>30%)'. FIELDCATALOG-COL_POS = 11. FIELDCATALOG-OUTPUTLEN = 30. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. FIELDCATALOG-FIELDNAME = 'ZZENTBEN'. FIELDCATALOG-SELTEXT_L = 'Enterprise Development Beneficiary'. FIELDCATALOG-COL_POS = 10. FIELDCATALOG-OUTPUTLEN = 45. APPEND FIELDCATALOG TO FIELDCATALOG. CLEAR FIELDCATALOG. ENDFORM. FORM DISPLAY_ALV_REPORT. GD_REPID = SY-REPID. CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY' EXPORTING * I_INTERFACE_CHECK = ' ' * I_BYPASSING_BUFFER = ' ' * I_BUFFER_ACTIVE = ' ' I_CALLBACK_PROGRAM = GD_REPID * I_CALLBACK_PF_STATUS_SET = ' ' * I_CALLBACK_USER_COMMAND = ' ' I_CALLBACK_TOP_OF_PAGE = 'TOP-OF-PAGE' * I_CALLBACK_HTML_TOP_OF_PAGE = ' ' * I_CALLBACK_HTML_END_OF_LIST = ' ' * I_STRUCTURE_NAME = * I_BACKGROUND_ID = ' ' * I_GRID_TITLE = * I_GRID_SETTINGS = * IS_LAYOUT = IT_FIELDCAT = FIELDCATALOG[] * IT_EXCLUDING = * IT_SPECIAL_GROUPS = * IT_SORT = * IT_FILTER = * IS_SEL_HIDE = * I_DEFAULT = 'X' I_SAVE = 'X' IS_VARIANT = G_VARIANT * IT_EVENTS = * IT_EVENT_EXIT = * IS_PRINT = * IS_REPREP_ID = * I_SCREEN_START_COLUMN = 0 * I_SCREEN_START_LINE = 0 * I_SCREEN_END_COLUMN = 0 * I_SCREEN_END_LINE = 0 * I_HTML_HEIGHT_TOP = 0 * I_HTML_HEIGHT_END = 0 * IT_ALV_GRAPHICS = * IT_HYPERLINK = * IT_ADD_FIELDCAT = * IT_EXCEPT_QINFO = * IR_SALV_FULLSCREEN_ADAPTER = * IMPORTING * E_EXIT_CAUSED_BY_CALLER = * ES_EXIT_CAUSED_BY_USER = TABLES T_OUTTAB = T_BEE * EXCEPTIONS * PROGRAM_ERROR = 1 * OTHERS = 2 . IF SY-SUBRC <> 0. * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4. ENDIF. ENDFORM. FORM TOP-OF-PAGE. DATA: T_HEADER TYPE SLIS_T_LISTHEADER, WA_HEADER TYPE SLIS_LISTHEADER, WA_FIELDNAME TYPE SLIS_FIELDNAME, T_LINE LIKE WA_HEADER-INFO, LD_LINES TYPE I, LD_LINESC(10) TYPE C. WA_HEADER-TYP = 'H'. WA_HEADER-INFO = 'Atos South Africa'. APPEND WA_HEADER TO T_HEADER. CLEAR WA_HEADER. WA_HEADER-TYP = 'S'. WA_HEADER-KEY = 'User Name'. WA_HEADER-INFO = SY-UNAME. APPEND WA_HEADER TO T_HEADER. CLEAR WA_HEADER. WA_HEADER-TYP = 'S'. WA_HEADER-KEY = 'Company Code'. WA_HEADER-INFO = P_BUKRS. APPEND WA_HEADER TO T_HEADER. CLEAR WA_HEADER. WA_HEADER-TYP = 'S'. WA_HEADER-KEY = 'Date of Execution'. WA_HEADER-INFO = SY-DATUM. CONCATENATE SY-DATUM+6(2) '.' SY-DATUM+4(2) '.' SY-DATUM(4) INTO WA_HEADER-INFO. APPEND WA_HEADER TO T_HEADER. CLEAR WA_HEADER. WA_HEADER-TYP = 'S'. WA_HEADER-KEY = 'Time'. WA_HEADER-INFO = SY-UZEIT. CONCATENATE SY-UZEIT+4(2) '.' SY-UZEIT+4(2) '.' SY-UZEIT(2) INTO WA_HEADER-INFO. APPEND WA_HEADER TO T_HEADER. CLEAR WA_HEADER. CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE' EXPORTING IT_LIST_COMMENTARY = T_HEADER. * I_LOGO = * I_END_OF_LIST_GRID = * I_ALV_FORM = . ENDFORM.

philipdavy · ‎01-02-2014

Declare one more internal table. In the example I gave, it is populating the final internal table without duplicates. You can also use SUM statement which is more simple.

LOOP AT IT_EKBE INTO WA_EKBE.

AT END OF EBELN.

SUM.

WRITE: / WA_EKBE-EBELN, WA_EKBE-DMBTR.

ENDAT.

ENDLOOP.

PS: The code you shared is less readable.

Regards,

Philip.

former_member184569 · ‎01-02-2014

Hope ebeln is the first field in your output table and field catalog.

Just add a sort table to your ALV.

data : i_sort TYPE slis_t_sortinfo_alv,
wa_sort TYPE slis_sortinfo_alv.

wa_sort-spos = '01' .
   wa_sort-fieldname = 'EBELN'.   " Field by which you need the totals
   wa_sort-tabname = 'IT_ITAB'. " ALV table.
    wa_sort-up = 'X'.
   wa_sort-subtot = 'X'.
   APPEND wa_sort TO i_sort .

Make sure that do sum is set for the amount field in field catalog.

lv_pos = lv_pos + 1.
   CLEAR gw_alv_fieldcat.
   gw_alv_fieldcat-fieldname = 'DMBTR'.
   gw_alv_fieldcat-tabname   = 'IT_TAB'.
   gw_alv_fieldcat-seltext_l = 'Amount'.
   gw_alv_fieldcat-col_pos   = lv_pos.
   gw_alv_fieldcat-outputlen = 10.
gw_alv_fieldcat-do_sum = 'X'.
   APPEND gw_alv_fieldcat TO gt_alv_fieldcat.

Also sort the ALV data table by ebeln before calling the FM.

sort it_itab by ebeln.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
   EXPORTING
     i_callback_program       = sy-repid
     it_fieldcat              = gt_alv_fieldcat
*      i_callback_pf_status_set = 'SET_PF_STATUS'
*      i_callback_user_command = 'USER_COMMAND'
     i_default                = 'X'
     i_save                   = 'S'
     is_layout                = gd_layout
     it_sort                  = i_sort
   TABLES
     t_outtab                 = it_itab.

You would get your required output.

Former Member · ‎01-02-2014

Thanks Philip Thank You very much for you help.

philipdavy · ‎01-02-2014

You are always welcome. Happy to know that your issue got solved. Please close the thread if answered.

Regards,

Philip.

raymond_giuseppi · ‎01-02-2014

The "standard" ALV tool for this is declaring DMBTR as summed and EBELN as a sort criteria with sub-total.

Else two solutions:

Easy one

Forbid user to change sort and filter criteria ( it_toolbar_excluding and similar tool)


APPEND grid->mc_fc_filter TO gt_toolbar_excluding.
APPEND grid->mc_fc_delete_filter TO gt_toolbar_excluding.
APPEND grid->mc_fc_sort TO gt_toolbar_excluding.
APPEND grid->mc_fc_subtot TO gt_toolbar_excluding.

Add a new column "cum. amount" and fill it in a simple LOOP (in field catalog prevent sum of this column NO_SUM)

LOOP AT itab ASSIGNING <fs>.
  AT NEW ebeln.
    CLEAR <fs>-acc_amnt.
  ENDAT.
  ADD <fs>-amnt TO <fs>-acc_amnt.
ENDLOOP.

a little more difficult

If you are actually ready to spend some (much) time for a true cumulative field and allow user to change sort and filter criteria

- add a new column field with initial value like previous case

- handle event AFTER_REFRESH of the ALV grid

In the handling method

prevent infinite loop (use a global data) as the refresh at the end will trigger once again this method
extract sort and filter criteria (do you want to add EBELN if not already a sort criteria ?)
update the special column like in previous case only using displayed record (get_filtered_entries) and adapting to other sort criteria if required (e.g. user sort by matnr then ebeln...)
refresh the alv display, using soft option if you didn't change sort criteria.

Regards,

Raymond

Former Member · ‎01-09-2014

if it_ekbe [] is not initial.

loop at it_ekbe into wa_ekbe.

move-corresponding wa_ekbe to it_final_ekbe.

collect it_final_ekbe.

clear : wa_ekbe.

endloop.

endif.