uploading excel sheet and using control break statement.

[pritam_baboo]

I need to upload this excel file into sap and get the output in the below format using control break statement. but while using sum with 'at end of' event the negative values are getting summed up. For each doc item only one record and it should get added or substracted according to debit or credit.

TYPE-POOLS: truxs.

TYPES: BEGIN OF str_exceldata,
col_1 TYPE vbeln_va,
col_2 TYPE i,
col_3 TYPE c,
col_4 TYPE i,
END OF str_exceldata.

DATA: it_excel TYPE TABLE OF str_exceldata,
wa_excel TYPE str_exceldata,
wa_temp TYPE str_exceldata,
wa_temp2 TYPE str_exceldata,
it_data TYPE truxs_t_text_data.


PARAMETERS: p_file TYPE rlgrap-filename.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
* PROGRAM_NAME = SYST-CPROG
* DYNPRO_NUMBER = SYST-DYNNR
field_name = 'P_FILE'
IMPORTING
file_name = p_file.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_data
i_filename = 'C:\Users\admin\Desktop\ControlBreak.xlsx.'
TABLES
i_tab_converted_data = it_excel
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
SORT it_excel BY col_1 col_2.

LOOP AT it_excel INTO wa_excel.
IF wa_excel-col_3 = 'D'.
wa_excel-col_4 = wa_excel-col_4 * -1.
ENDIF.
wa_temp = wa_excel.
AT FIRST.
WRITE: /'Balance Sheet'.
ENDAT.
AT NEW col_1.
WRITE: / 'Document Number Doc No. Balance'.
ENDAT.
LOOP at it_excel INTO wa_temp2.
at END OF col_2.
SUM.
wa_temp = wa_temp2.
ENDAT.
endloop.
WRITE: / wa_temp-col_1, wa_temp-col_2, wa_temp-col_4.

AT END OF col_1.
SUM.
WRITE: / 'Subtotal', wa_excel-col_4.
ENDAT.

AT LAST.
SUM.
WRITE:/ 'Grand Total', wa_excel-col_4.
ENDAT.

ENDLOOP.

[pritam_baboo]

TYPE-POOLS: truxs.

TYPES: BEGIN OF str_exceldata,
col_1 TYPE vbeln_va,
col_2 TYPE i,
col_3 TYPE c,
col_4 TYPE i,
END OF str_exceldata.

DATA: it_excel TYPE TABLE OF str_exceldata,
wa_excel TYPE str_exceldata,
wa_temp TYPE str_exceldata,
wa_temp2 TYPE str_exceldata,
it_data TYPE truxs_t_text_data.


PARAMETERS: p_file TYPE rlgrap-filename.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
* PROGRAM_NAME = SYST-CPROG
* DYNPRO_NUMBER = SYST-DYNNR
field_name = 'P_FILE'
IMPORTING
file_name = p_file.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
* I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_data
i_filename = 'C:\Users\admin\Desktop\ControlBreak.xlsx.'
TABLES
i_tab_converted_data = it_excel
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
SORT it_excel BY col_1 col_2.

LOOP AT it_excel INTO wa_excel.
IF wa_excel-col_3 = 'D'.
wa_excel-col_4 = wa_excel-col_4 * -1.
ENDIF.
wa_temp = wa_excel.
AT FIRST.
WRITE: /'Balance Sheet'.
ENDAT.
AT NEW col_1.
WRITE: / 'Document Number Doc No. Balance'.
ENDAT.
LOOP at it_excel INTO wa_temp2.
at END OF col_2.
SUM.
wa_temp = wa_temp2.
ENDAT.
endloop.
WRITE: / wa_temp-col_1, wa_temp-col_2, wa_temp-col_4.

AT END OF col_1.
SUM.
WRITE: / 'Subtotal', wa_excel-col_4.
ENDAT.

AT LAST.
SUM.
WRITE:/ 'Grand Total', wa_excel-col_4.
ENDAT.

ENDLOOP.

[Sandra_Rossi]

Please merge your code into your question, and please select your code and press the button [CODE], which makes the code appear colored/indented, it will be easier for people to look at it. Thank you!

NB: SUM is still not deprecated, but I would recommend not using it because it's more obvious to anyone what means "subtotal = subtotal + amount", and "total = total + subtotal", and less prone to errors and questions...

[pritam_baboo]

But again the 2nd(item No.) column should also get sorted and and summed up.

for every item number there should be one entry.

[Sandra_Rossi]

Sorry I can't read your code (missing indentation and color). I suggest that you write easy and understandable code like:

LOOP AT input_lines ASSIGNING FIELD-SYMBOL(<input_line>).
  ASSIGN aggregated_lines[ doc_number = <input_line>-doc_number
                           item_no    = <input_line>-item_no ]
        TO FIELD-SYMBOL(<aggregated_line>).
  IF sy-subrc <> 0. " not found
    INSERT VALUE #( doc_number = <input_line>-doc_number
                    item_no    = <input_line>-item_no )
          INTO TABLE aggregated_lines
          ASSIGNING <aggregated_line>.
  ENDIF.
  <aggregated_line>-amount = <aggregated_line>-amount + <input_line>-...
ENDLOOP.

Then calculate the sub-totals and grand total.

Then generate the Excel file.

It will make your life easier to have simple code, organized in several sections, and using meaningful names.