The Best way to find SUM of records

In this post I would like to show which is the fastest way to find sum for a number of records.
The three possible ways to calculate a sum of records are:
  1. Using COLLECT Statement.
  2. Using SUM statement within Control-break statement AT-ENDAT.
  3. Using a variable within a parallel cursor.
The conclusion is that the second option always takes less time compared to other three.The following graph explains it:



The following code is used to calculate it.Please test it yourself and give me a feedback.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
PROGRAM ztest_sum.

TYPES:
BEGIN OF x_sbook,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
fldate TYPE s_date,
luggweight TYPE s_lugweigh,
wunit TYPE s_weiunit,
END OF x_sbook.

DATA: i_sbook TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
i_final1 TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
i_final2 TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
i_final3 TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
wa_sbook TYPE x_sbook,
rtime1 TYPE i,
rtime2 TYPE i,
rtime3 TYPE i.

PARAMETERS: p_rec TYPE i. " No. of records

SELECT carrid
connid
fldate
luggweight
wunit
FROM sbook
UP TO p_rec ROWS
INTO TABLE i_sbook.

CHECK sy-subrc = 0.
APPEND LINES OF i_sbook TO i_sbook.

SORT i_sbook BY carrid connid fldate.

PERFORM collect.

PERFORM at_end_of_sum.

PERFORM sum_by_variable.

END-OF-SELECTION.

PERFORM display.


*&---------------------------------------------------------------------*
*& Form collect
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM collect .
DATA: time1 TYPE i,
time2 TYPE i.

GET RUN TIME FIELD time1.

LOOP AT i_sbook INTO wa_sbook.
COLLECT wa_sbook INTO i_final1.
ENDLOOP.

GET RUN TIME FIELD time2.

rtime1 = time2 - time1.
ENDFORM. " collect
*&---------------------------------------------------------------------*
*& Form at_end_of_sum
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM at_end_of_sum .
DATA: time1 TYPE i,
time2 TYPE i.

GET RUN TIME FIELD time1.
LOOP AT i_sbook INTO wa_sbook.
AT END OF fldate.
SUM.
APPEND wa_sbook TO i_final2.
ENDAT.
ENDLOOP.

GET RUN TIME FIELD time2.

rtime2 = time2 - time1.
ENDFORM. " at_end_of_sum
*&---------------------------------------------------------------------*
*& Form sum_by_variable
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM sum_by_variable .
DATA: time1 TYPE i,
time2 TYPE i,
i_temp TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
l_index TYPE i,
wa_temp TYPE x_sbook,
l_weight TYPE s_lugweigh,
wa_final TYPE x_sbook.

GET RUN TIME FIELD time1.

i_temp = i_sbook.

DELETE ADJACENT DUPLICATES FROM i_temp
COMPARING carrid connid fldate.

LOOP AT i_temp INTO wa_temp.
READ TABLE i_sbook
TRANSPORTING NO FIELDS
WITH KEY carrid = wa_temp-carrid
connid = wa_temp-connid
fldate = wa_temp-fldate
BINARY SEARCH.
IF sy-subrc = 0.
l_index = sy-tabix.
LOOP AT i_sbook INTO wa_sbook FROM l_index.
IF wa_sbook-carrid <> wa_temp-carrid OR
wa_sbook-connid <> wa_temp-connid OR
wa_sbook-fldate <> wa_temp-fldate.
EXIT.
ENDIF.
l_weight = wa_sbook-luggweight + l_weight.
ENDLOOP.

wa_final = wa_sbook.
wa_final-luggweight = l_weight.
APPEND wa_sbook TO i_final3.
CLEAR l_weight.
ENDIF.
ENDLOOP.

GET RUN TIME FIELD time2.
rtime3 = time2 - time1.
ENDFORM. " sum_by_variable
*&---------------------------------------------------------------------*
*& Form display
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM display .
DATA: wa_final TYPE x_sbook.

DATA: l_lines TYPE i.

l_lines = LINES( i_final1 ).
ULINE.
FORMAT COLOR 2 ON.
WRITE: /5 'Using COLLECT Statement',':', l_lines.
WRITE: /5 'Run Time:',rtime1,'microseconds'.
FORMAT COLOR 2 OFF.
ULINE.
l_lines = LINES( i_final2 ).
FORMAT COLOR 1 ON.
WRITE: /5 'Using Control Break statement AT-END-OF, SUM', ':',
l_lines.
WRITE: /5 'Run Time:',rtime2,'microseconds'.
FORMAT COLOR 1 OFF.
ULINE.
l_lines = LINES( i_final3 ).
FORMAT COLOR 4 ON.
WRITE: /5 'Using Parallel Cursor',':', l_lines.
WRITE: /5 'Run Time:',rtime3,'microseconds'.
FORMAT COLOR 4 OFF.
ULINE.
ENDFORM. " display

Traffic Light in ALV Reports

Many a times we have to display a traffic light in ALV report. We can do that easily using "Exceptions" parameter provided by SAP. But it has one drawback, we cannot change the column header to something meaningful. It always set as "Exception" in case of ALV Grid and an icon in case of ALV List.

Here I'll show a workaround to show the traffic lights with a meaningful column header.

The exception display:


















The workaround is to use a field of type CHAR4 and display it as Icon.
To display a field as icon, in ALV Field catalog we have to mark ICON field as 'X'. The icons for traffic lights are : icon_green_light, icon_red_light and icon_yellow_light.


















The code :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
*&---------------------------------------------------------------------*
*& Report ZTEST_SOURAV_TRAFFIC_LIGHTS
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ztest_sourav_traffic_lights.
TYPE-POOLS: slis,icon.
TYPES: BEGIN OF x_sflight,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
fldate TYPE s_date,
price TYPE s_price,
currency TYPE s_currcode,
seatsmax TYPE s_seatsmax,
seatsocc TYPE s_seatsocc,
END OF x_sflight.
TYPES:BEGIN OF x_final,
icon TYPE char4.
INCLUDE TYPE x_sflight.
TYPES: END OF x_final.

DATA: i_sflight TYPE STANDARD TABLE OF x_sflight INITIAL SIZE 0,
i_final TYPE STANDARD TABLE OF x_final INITIAL SIZE 0,
i_fieldcat TYPE slis_t_fieldcat_alv,
wa_layout TYPE slis_layout_alv.


CONSTANTS: program TYPE syrepid VALUE sy-repid.

START-OF-SELECTION.
SELECT carrid " Airline Code
connid " Flight Connection Number
fldate " Flight date
price " Airfare
currency " Local currency of airline
seatsmax " Maximum capacity in economy class
seatsocc " Occupied seats in economy class
FROM sflight " Flight
INTO TABLE i_sflight.
IF sy-subrc <> 0.
MESSAGE i001(00) WITH 'No data found!'.
LEAVE LIST-PROCESSING.
ENDIF.


END-OF-SELECTION.

PERFORM populate_records.

PERFORM display_records.


*&---------------------------------------------------------------------*
*& Form populate_records
*&---------------------------------------------------------------------*
* Populate final table
*----------------------------------------------------------------------*
FORM populate_records .

DATA:
wa_sflight TYPE x_sflight,
wa_final TYPE x_final,
temp TYPE p DECIMALS 2.

LOOP AT i_sflight INTO wa_sflight.
CLEAR temp.
*Calculate Percentage of booked flights
temp = ( wa_sflight-seatsocc / wa_sflight-seatsmax ) .

IF temp > '0.9'.
*If booked more than 90%
wa_final-icon = icon_green_light.
ELSEIF temp < '0.2'.
*If booked less than 20%
wa_final-icon = icon_red_light.
ELSE.
*If its between 20% to 90 %
wa_final-icon = icon_yellow_light.
ENDIF.

wa_final-carrid = wa_sflight-carrid.
wa_final-connid = wa_sflight-connid.
wa_final-fldate = wa_sflight-fldate.
wa_final-price = wa_sflight-price.
wa_final-currency = wa_sflight-currency.
wa_final-seatsmax = wa_sflight-seatsmax.
wa_final-seatsocc = wa_sflight-seatsocc.

APPEND wa_final TO i_final.
CLEAR wa_final.
ENDLOOP.
FREE i_sflight.
ENDFORM. " populate_records
*&---------------------------------------------------------------------*
*& Form display_records
*&---------------------------------------------------------------------*
* Subroutines for Display
*----------------------------------------------------------------------*
FORM display_records .

PERFORM populate_field_catalog.

PERFORM populate_layout.

PERFORM trigger_alv.

ENDFORM. " display_records
*&---------------------------------------------------------------------*
*& Form populate_field_catalog
*&---------------------------------------------------------------------*
* Popualte Field Catalog
*----------------------------------------------------------------------*
FORM populate_field_catalog .
DATA:
wa_fieldcat TYPE slis_fieldcat_alv.
DEFINE fcat_merge.

wa_fieldcat-col_pos = &1.
wa_fieldcat-fieldname = &2.
if wa_fieldcat-fieldname = 'ICON'.
wa_fieldcat-icon = 'X'. " Display the field as ICON
wa_fieldcat-seltext_m = 'Occupency Status'. " Column Header
wa_fieldcat-inttype = 'C'.
wa_fieldcat-outputlen = '4'.
endif.

wa_fieldcat-ref_fieldname = &3.
wa_fieldcat-ref_tabname = &4.

if wa_fieldcat-fieldname = 'PRICE'.
wa_fieldcat-cfieldname = 'CURRENCY'.
wa_fieldcat-ctabname = 'I_FINAL'.
endif.

append wa_fieldcat to i_fieldcat.
clear wa_fieldcat.
END-OF-DEFINITION.

fcat_merge: '1' 'ICON' '' '',
'2' 'CARRID' 'CARRID' 'SFLIGHT',
'3' 'CONNID' 'CONNID' 'SFLIGHT',
'4' 'FLDATE' 'FLDATE' 'SFLIGHT',
'5' 'PRICE' 'PRICE' 'SFLIGHT',
'6' 'CURRENCY' 'CURRENCY' 'SFLIGHT',
'7' 'SEATSMAX' 'SEATSMAX' 'SFLIGHT',
'8' 'SEATSOCC' 'SEATSOCC' 'SFLIGHT'.

ENDFORM. " populate_field_catalog

*&---------------------------------------------------------------------*
*& Form populate_layout
*&---------------------------------------------------------------------*
* Populate LAYOUT Structure for ALV
*----------------------------------------------------------------------*
FORM populate_layout.
wa_layout-zebra = 'X'.
wa_layout-colwidth_optimize = 'X'.
ENDFORM. "populate_layout
*&---------------------------------------------------------------------*
*& Form trigger_alv
*&---------------------------------------------------------------------*
* Trigger the ALV API
*----------------------------------------------------------------------*
FORM trigger_alv .

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
i_callback_program = program
is_layout = wa_layout
it_fieldcat = i_fieldcat
TABLES
t_outtab = i_final
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE i001(00) WITH 'Error while displaing ALV'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM. " trigger_alv

Helpful BASIS Transactions for ABAPers

The following are some transactions which may come in handy for ABAPers:



Transaction Codes Description
SCCLLOCAL CLIENT COPY WITHIN THE SAME SYSTEM
SCC1CLIENT COPY - SPECIAL SELECTIONS(COPY TRANSPORTS WITHIN CLIENTS)
SCC9REMOTE CLIENT COPY IN DIFFERENT SYSTEM
SCC5DELETING A CLIENT
SP01SHOW SPOOL REQUEST
SPADCONFIGURE A PRINTER IN SAP
ST01SYSTEM TRACE
ST02SAP BUFFER
ST03WORKLOAD MONITOR
ST04DATABASE MONITOR
ST05SQL TRACE
ST06OS MONITOR
ST22ABAP DUMP
SCUAFOR CREATING/DELETING CUA
SCUMWHICH DATA U WANT TO STORE LOCALLY AND WHICH GLOBALLY
SCULCUA LOG
PPOCECREATE A PLAN
PPOMEMAINTAIN A PLAN
PPOSEDISPLAY A PLAN
STMSTRANSPORT MANAGEMENT SYSTEM
SLG0CONFIGURE APPLICATION LOG
SLG1ANALYSE APPLICATION LOG
SLG2DELETE APPLICATION LOG
SCUGTRANSPORT OF USER
RZ01JOB SCHEDULING MONITOR
RZ02NETWORK GRAPHICS FOR SAP INSTANCES
RZ03PRESENTATION CONTROL SAP INSTANCE
RZ04MAINTAIN SAP INSTANCE
RZ06ALERT THRESHOLD MAINTAINCE
RZ08SAP ALERT MONITOR
RZ10PROFILE PARAMETER CHECKING AND MAINTAINENCE
RZ11DISPLAY PROFILE PARAMETER
RZ20CCMS MONITOR
RZ04SHIFTING OF OPERATION
BD54CREATING A LOGICAL SYSTEM
BD64DISPLAY OF DISTRIBUTION MODEL
SM01LOCK/UNLOCK A TRANSTACTION
SM02SEND SYSTEM MESSAGE
SM04WHO ARE THE USER CURRENTLY LOGGED ON NOW
SM12TO VIEW LOCK AND ALSO DELETE LOG
SM13CHECK UPDATE
SM30TABLE MAINTAINCE
SM36SCHEDULING A BACKGROUND JOB
SM37MONITORING A BACKGROUND JOB
SM49EXECUTE A EXTERNAL COMMAND
SM59CREATE A RFC CONNECTION
SM69CREATING A COMMAND
SALE/BD54LOGICAL NAME
SUPCTO GENERATE PROFILE
DB01ANALYZE EXCUSIVE LOCK WAITS
BD02ANALYZE TABLES AND INDEX
DB03PARAMETER CHANGE IN DB
DB11EARLY WATCH PROFILE MAINTAINCE
DB12OVERVIEW OF BACKUP LOGS
DB13DATABASE ADMINISTRATOR CALENDER
DB14SHOW DBA ACTION LOGS

How to print apostrophe ( ' ) using the WRITE statement

Printing apostrophes ( ' ) in ABAP is really tricky as in ABAP coding we have to give values in quotes like 'Ricky World'. So how to use WRITE: 'Ricky's World' ? If you try to type this it will give syntax error!

The trick is use three apostrophes instead of one.

1
2
3
DATA: LINE(20) TYPE C.
CONCATENATE 'You''' 'll be there.' INTO LINE.
WRITE:/ LINE.


or simply :

1
WRITE:/ 'You''ll be there.'.

Determine whether a file is open or not in Application Server

To determine whether a file is opened or not in application server we can use the class 'CX_SY_FILE_OPEN'. By using this class we can determine whether a file is opened or not in application server as SY-SUBRC <> 0 may not provide the file's open information while we are interacting with the file. SY-SUBRC <> 0 may happens for other reasons also.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DATA:
* First declare an object of type CX_ROOT.
OREF TYPE REF TO CX_ROOT,

* Then declare a variable of type string.
TEXT TYPE STRING.

*Now open a dataset in a TRY-CATCH block
TRY.
OPEN DATASET '/USR/SAP/TRANS/ECC1/ETC/B12008.TXT' FOR OUTPUT
IN TEXT MODE ENCODING DEFAULT

* Then in the catch block instantiate the Object.
CATCH CX_SY_FILE_OPEN INTO OREF.
* Now in 'CATCH' block use the TEXT variable to get the TEXT from the Object.
TEXT = OREF->GET_TEXT( ).

*Now if the TEXT is not initial that means the file is already opened
*else it is not opened and we have to open it
IF TEXT IS NOT INITIAL.

* If open then we can close the file
CLOSE DATASET '/USR/SAP/TRANS/ECC1/ETC/B120080627112.TXT'.

ENDIF.

ENDTRY.