Basic ABAP Report Code Generation using MS Excel
This exercise was done when I was writing basic ABAP reports and checking out Excel formulas.
Formulas will be used to generate local TYPES, workarea, internal table declaration and display internal table contents.
Advantages of this method:
- Block selection and copy paste will auto update the output as all formulas are re-evaluated on every cell change.
- Large number of fields and their types can be pasted easily. Time spent for typing each line manually can be substantial for large dataset.
- Multiple internal tables can be created through a single worksheet.
Here is a simple code that reads some columns of SFLIGHT table and displays as list.
TYPES: BEGIN OF ty_sflight,
MANDT TYPE S_MANDT,
CARRID TYPE S_CARR_ID,
CONNID TYPE S_CONN_ID,
FLDATE TYPE S_DATE,
PRICE TYPE S_PRICE,
CURRENCY TYPE S_CURRCODE,
PLANETYPE TYPE S_PLANETYE,
SEATSMAX TYPE S_SEATSMAX,
SEATSOCC TYPE S_SEATSOCC,
END OF ty_sflight.
DATA: wa_sflight TYPE ty_sflight.
DATA: it_sflight TYPE STANDARD TABLE OF ty_sflight.
LOOP AT it_sflight INTO wa_sflight.
WRITE: wa_sflight–MANDT.
WRITE: wa_sflight–CARRID.
WRITE: wa_sflight–CONNID.
WRITE: wa_sflight–FLDATE.
WRITE: wa_sflight–PRICE.
WRITE: wa_sflight–CURRENCY.
WRITE: wa_sflight–PLANETYPE.
WRITE: wa_sflight–SEATSMAX.
WRITE: wa_sflight–SEATSOCC.
SKIP. ENDLOOP.
Formulas will be used in such a way that sections of above code gets generated by using below input data only.
| sflight | ||
| MANDT | S_MANDT | |
| CARRID | S_CARR_ID | |
| CONNID | S_CONN_ID | |
| FLDATE | S_DATE | |
| PRICE | S_PRICE | |
| CURRENCY | S_CURRCODE | |
| PLANETYPE | S_PLANETYE | |
| SEATSMAX | S_SEATSMAX | |
| SEATSOCC | S_SEATSOCC |
Data is copied from SE11 transaction using block selection (Ctrl+Y) on SFLIGHT table display.
Here is a screenshot from excel file after pasting input data.
An excel formula uses relative cell reference.
This means that if formula in cell F1 refers A1, same formula when copied to F5 will refer A5.
The formula for TYPES declaration is given below:
=IF(
ISTEXT(A13),
CONCATENATE(“TYPES: BEGIN OF ty_”,A13,”,”),
IF(
ISBLANK(B13),
IF(
ISTEXT(B12),
CONCATENATE(“END OF ty_”,E13,”.”),
“”
),
CONCATENATE(B13,” TYPE “,C13,”,”)
)
)
This formula can create 3 types of lines.
- TYPES: BEGIN OF ty_tablename,
- fieldname TYPE fieldtype,
- END OF ty_tablename.
NOTE: E column is hidden in excel file. It stores latest value of column A. The worksheet is protected without password to prevent accidental wiping of formula.
The pseudocode of above formula is as follows:
Case 1. If table name is populated, it is start of structure.
Case 2. If table name is blank, and field name is populated, field will be added to TYPES structure.
Case 3. If table and field name are blank and previous row has data, it is end of TYPES structure.
Case 4. If previous row is also empty, do nothing.
The formula for displaying internal table is same as TYPES declaration, only static strings change.
=IF(
ISTEXT(A13),
CONCATENATE(“LOOP AT it_”,A13,” INTO wa_”,A13,”.”),
IF(
ISBLANK(B13),
IF(
ISTEXT(B12),
“SKIP. ENDLOOP.”,
“”
),
CONCATENATE(“WRITE: wa_”,E13,”-“,B13,”.”)
)
)
The logic for workarea and internal table declaration is simple.
Case 1. If table name is populated, do declaration.
Case 2. If table name is blank, do nothing.
=IF(ISTEXT(A1),CONCATENATE(“DATA: wa_”,A1,” TYPE ty_”,A1,”.”),””)
=IF(ISTEXT(A1),CONCATENATE(“DATA: it_”,A1,” TYPE STANDARD TABLE OF ty_”,A1,”.”),””)
I could not upload the file in xlsx format because of limitations of SCN platform.
Attached file is in XML Spreadsheet 2003 format and opens in Excel.
Data needs to be entered only in columns A,B,C.
Formulas mentioned above were once used to create 500 lines of declaration for a large internal table of ALV report.
Formulas can be used for repetitive tasks like removing hard-coded literals and replacing them by constants.
Please let me know your thoughts.