alv reports

[Former Member]

hi friends,

i got one requirement to display certain fields of sale order in which the delivery status is partially completed. i have done using for all entries for each table.

my database retreival take 90% percent of time to execute. how can i reduce the data access time please help as soon as.

i attach the coding along with this

REPORT ZASSIGNMENT

MESSAGE-ID ZMSG

line-count 20

line-size 255.

----

---

• Tables

----

---

tables: vbak,

kna1,

vbap,

vbpa,

vbfa,

vbup,

lips.

type-pools: slis.

----

---

• Internal table declaration

----

---

data : begin of t_vbak occurs 0,

vbeln like vbak-vbeln,

netwr like vbak-netwr,

kunnr like vbak-kunnr,

end of t_vbak.

data : begin of t_vbpa occurs 0,

vbeln like vbpa-vbeln,

kunnr like vbpa-kunnr,

parvw like vbpa-parvw,

end of t_vbpa.

data: begin of t_kna1 occurs 0,

kunnr like kna1-kunnr,

name1 like kna1-name1,

end of t_kna1.

data: begin of t_vbap occurs 0,

vbeln like vbap-vbeln,

posnr like vbap-posnr,

matnr like vbap-matnr,

kwmeng like vbap-kwmeng,

netpr like vbap-netpr,

end of t_vbap.

data: begin of t_vbfa occurs 0,

vbelv like vbfa-vbelv,

posnv like vbfa-posnv,

vbeln like vbfa-vbeln,

vbtyp_n like vbfa-vbtyp_n,

end of t_vbfa.

data : begin of t_vbup occurs 0,

vbeln like vbup-vbeln,

posnr like vbup-posnr,

lfsta like vbup-lfsta,

lfgsa like vbup-lfgsa,

• wbsta like vbup-wbsta,

end of t_vbup.

data: begin of t_lips occurs 0,

vbeln like lips-vbeln,

posnr like lips-posnr,

lfimg like lips-lfimg,

end of t_lips.

data: begin of t_output occurs 0,

vbeln like t_vbap-vbeln,

netwr like t_vbak-netwr,

kunnr like t_vbak-kunnr,

kunnr1 like t_vbpa-kunnr,

name1 like t_kna1-name1,

name like t_kna1-name1,

posnr like t_vbap-posnr,

matnr like t_vbap-matnr,

kwmeng like t_vbap-kwmeng,

netpr like t_vbap-netpr,

vbeln1 like vbfa-vbeln,

lfimg like t_lips-lfimg,

lfsta like t_vbup-lfsta,

lfgsa like t_vbup-lfgsa,

• wbsta like t_vbup-wbsta,

end of t_output.

data : fcat type slis_fieldcat_alv,

gt_events type slis_t_event,

gd_repid like sy-repid,

fcat1 type slis_t_fieldcat_alv.

----

---

• Selection screen

----

---

selection-screen:begin of block a with frame title text-001.

select-options :s_vbeln for vbap-vbeln,

s_matnr for vbap-matnr,

s_kunnr for vbak-kunnr,

s_deliv for lips-vbeln.

selection-screen:end of block a.

At selection-screen on block a.

if s_vbeln is initial and

s_matnr is initial and

s_kunnr is initial and

s_deliv is initial.

message 'Fill atleast single field' type 'E'.

endif.

start-of-selection.

perform data_retrieval.

perform fieldcatalog.

perform gridfun.

&----

---

*& Form data_reterival

&----

---

• text

----

---

form data_retrieval.

select vbeln

netwr

kunnr

from vbak

into table t_vbak

where vbeln in s_vbeln and

kunnr in s_kunnr.

if not t_vbak[] is initial.

select vbeln

posnr

matnr

kwmeng

netpr

from vbap

into table t_vbap

for all entries in t_vbak

where vbeln = t_vbak-vbeln and

matnr in s_matnr.

endif.

if not t_vbap[] is initial.

select vbeln

kunnr

parvw

from vbpa

into table t_vbpa

for all entries in t_vbap

where vbeln = t_vbap-vbeln and

parvw = 'RE '.

endif.

if not t_vbpa[] is initial.

select kunnr

name1

from kna1

into table t_kna1

for all entries in t_vbpa

where kunnr = t_vbpa-kunnr.

endif.

if not t_kna1[] is initial.

select vbelv

posnv

vbeln

vbtyp_n

from vbfa

into table t_vbfa

for all entries in t_vbap

where vbelv = t_vbap-vbeln and

posnv = t_vbap-posnr and

vbtyp_n = 'J' and

vbeln in s_deliv.

endif.

if not t_vbfa[] is initial.

select vbeln

posnr

lfimg

from lips

into table t_lips

for all entries in t_vbfa

where vbeln = t_vbfa-vbeln and

posnr = t_vbfa-posnv.

endif.

if not t_lips[] is initial.

select vbeln

posnr

lfsta

lfgsa

from vbup

into table t_vbup

for all entries in t_vbap

where vbeln = t_vbap-vbeln and

posnr = t_vbap-posnr and

lfsta = 'B' and

lfgsa = 'B'.

endif.

loop at t_vbup where lfsta = 'B' and

lfgsa = 'B'.

t_output-vbeln = t_vbup-vbeln.

t_output-posnr = t_vbup-posnr.

t_output-lfsta = t_vbup-lfsta.

t_output-lfgsa = t_vbup-lfgsa.

clear t_vbak.

read table t_vbak with key vbeln = t_output-vbeln

binary search.

if sy-subrc = 0.

t_output-kunnr = t_vbak-kunnr.

t_output-netwr = t_vbak-netwr.

endif.

clear t_vbap.

read table t_vbap with key vbeln = t_output-vbeln

posnr = t_output-posnr binary search.

if sy-subrc = 0.

t_output-matnr = t_vbap-matnr.

t_output-kwmeng = t_vbap-kwmeng.

t_output-netpr = t_vbap-netpr.

endif.

clear t_vbfa.

read table t_vbfa with key vbelv = t_output-vbeln binary search.

if sy-subrc = 0.

t_output-vbeln1 = t_vbfa-vbeln.

endif.

clear t_lips.

read table t_lips with key vbeln = t_output-vbeln1 binary search.

if sy-subrc = 0.

t_output-lfimg = t_lips-lfimg.

endif.

read table t_vbpa with key vbeln = t_output-vbeln

parvw = 'RE' binary search.

if sy-subrc = 0.

t_output-kunnr1 = t_vbpa-kunnr.

endif.

clear t_kna1.

read table t_kna1 with key kunnr = t_output-kunnr

binary search.

if sy-subrc = 0.

t_output-name1 = t_kna1-name1.

endif.

clear t_vbpa.

read table t_vbpa with key vbeln = t_output-vbeln

parvw = 'RE' binary search.

if sy-subrc = 0.

t_output-kunnr1 = t_vbpa-kunnr.

endif.

clear t_kna1.

read table t_kna1 with key kunnr = t_output-kunnr1

binary search.

if sy-subrc = 0.

t_output-name = t_kna1-name1.

endif.

append t_output.

endloop.

endform. "data_reterival

"data_reterival

&----

---

*& Form fieldcatalog

&----

---

• text

----

---

form fieldcatalog.

fcat-col_pos = 1.

fcat-fieldname = 'VBELN'.

fcat-seltext_m = 'Sale Order'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 2.

fcat-fieldname = 'POSNR'.

fcat-seltext_m = 'Item'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 3.

fcat-fieldname = 'MATNR'.

fcat-seltext_m = 'Material'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 4.

fcat-fieldname = 'KUNNR'.

fcat-seltext_m = 'Sold-to-party'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 5.

fcat-fieldname = 'NAME1'.

fcat-seltext_m = 'Name'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 6.

fcat-fieldname = 'KUNNR1'.

fcat-seltext_m = 'Bill-to-party'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 7.

fcat-fieldname = 'NAME'.

fcat-seltext_m = 'Name'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 8.

fcat-fieldname = 'KWMENG'.

fcat-seltext_m = 'Order quantity'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 9.

fcat-fieldname = 'NETPR'.

fcat-seltext_m = 'Net price'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 10.

fcat-fieldname = 'NETWR'.

fcat-seltext_m = 'Net value'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 11.

fcat-fieldname = 'VBELN1'.

fcat-seltext_m = 'Delivery'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 12.

fcat-fieldname = 'LFIMG'.

fcat-seltext_m = 'Delivery quantity'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 13.

fcat-fieldname = 'LFSTA'.

fcat-seltext_m = 'Delivery status'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 14.

fcat-fieldname = 'LFGSA'.

fcat-seltext_m = 'Overall delivery status'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

endform. "fieldcatalog

&----

---

*& Form gridfun

&----

---

• text

----

---

form gridfun.

gd_repid = sy-repid.

call function 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = gd_repid

i_callback_user_command = 'USER_COMMAND'

it_fieldcat = fcat1

it_events = gt_events

i_save = 'X'

TABLES

t_outtab = t_output

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. "gridfun

&----

---

*& Form USER_COMMAND

&----

---

• text

----

---

• -->U_UCOMM text

• -->US_SELFIELDtext

----

---

FORM USER_COMMAND USING U_UCOMM TYPE SYUCOMM

US_SELFIELD TYPE SLIS_SELFIELD. "#EC CALLED

CASE U_UCOMM.

WHEN '&IC1'. " Double click

if

us_selfield-fieldname = 'MATNR'.

read table t_output index us_selfield-tabindex.

check sy-subrc eq 0.

set parameter id 'MAT' field t_output-matnr.

call transaction 'MM03' and skip first screen.

elseIF US_SELFIELD-FIELDNAME = 'VBELN'.

READ TABLE T_OUTPUT INDEX US_SELFIELD-TABINDEX.

CHECK SY-SUBRC EQ 0.

SET PARAMETER ID 'AUN' FIELD T_OUTPUT-VBELN.

CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN.

elseif

us_selfield-fieldname = 'KUNNR'.

read table t_output index us_selfield-tabindex.

check sy-subrc eq 0.

set parameter id 'KUN' field t_output-kunnr.

call transaction 'FD03' and skip first screen.

elseif

us_selfield-fieldname = 'KUNNR1'.

read table t_output index us_selfield-tabindex.

check sy-subrc eq 0.

set parameter id 'KUN' field t_output-kunnr.

call transaction 'FD03' and skip first screen.

endif.

endcase.

thanks in advance

[Former Member]

hi friends,

i got one requirement to display certain fields of sale order in which the delivery status is partially completed. i have done using for all entries for each table.

my database retreival take 90% percent of time to execute. how can i reduce the data access time please help as soon as.

i attach the coding along with this

REPORT ZASSIGNMENT

MESSAGE-ID ZMSG

line-count 20

line-size 255.

----

---

• Tables

----

---

tables: vbak,

kna1,

vbap,

vbpa,

vbfa,

vbup,

lips.

type-pools: slis.

----

---

• Internal table declaration

----

---

data : begin of t_vbak occurs 0,

vbeln like vbak-vbeln,

netwr like vbak-netwr,

kunnr like vbak-kunnr,

end of t_vbak.

data : begin of t_vbpa occurs 0,

vbeln like vbpa-vbeln,

kunnr like vbpa-kunnr,

parvw like vbpa-parvw,

end of t_vbpa.

data: begin of t_kna1 occurs 0,

kunnr like kna1-kunnr,

name1 like kna1-name1,

end of t_kna1.

data: begin of t_vbap occurs 0,

vbeln like vbap-vbeln,

posnr like vbap-posnr,

matnr like vbap-matnr,

kwmeng like vbap-kwmeng,

netpr like vbap-netpr,

end of t_vbap.

data: begin of t_vbfa occurs 0,

vbelv like vbfa-vbelv,

posnv like vbfa-posnv,

vbeln like vbfa-vbeln,

vbtyp_n like vbfa-vbtyp_n,

end of t_vbfa.

data : begin of t_vbup occurs 0,

vbeln like vbup-vbeln,

posnr like vbup-posnr,

lfsta like vbup-lfsta,

lfgsa like vbup-lfgsa,

• wbsta like vbup-wbsta,

end of t_vbup.

data: begin of t_lips occurs 0,

vbeln like lips-vbeln,

posnr like lips-posnr,

lfimg like lips-lfimg,

end of t_lips.

data: begin of t_output occurs 0,

vbeln like t_vbap-vbeln,

netwr like t_vbak-netwr,

kunnr like t_vbak-kunnr,

kunnr1 like t_vbpa-kunnr,

name1 like t_kna1-name1,

name like t_kna1-name1,

posnr like t_vbap-posnr,

matnr like t_vbap-matnr,

kwmeng like t_vbap-kwmeng,

netpr like t_vbap-netpr,

vbeln1 like vbfa-vbeln,

lfimg like t_lips-lfimg,

lfsta like t_vbup-lfsta,

lfgsa like t_vbup-lfgsa,

• wbsta like t_vbup-wbsta,

end of t_output.

data : fcat type slis_fieldcat_alv,

gt_events type slis_t_event,

gd_repid like sy-repid,

fcat1 type slis_t_fieldcat_alv.

----

---

• Selection screen

----

---

selection-screen:begin of block a with frame title text-001.

select-options :s_vbeln for vbap-vbeln,

s_matnr for vbap-matnr,

s_kunnr for vbak-kunnr,

s_deliv for lips-vbeln.

selection-screen:end of block a.

At selection-screen on block a.

if s_vbeln is initial and

s_matnr is initial and

s_kunnr is initial and

s_deliv is initial.

message 'Fill atleast single field' type 'E'.

endif.

start-of-selection.

perform data_retrieval.

perform fieldcatalog.

perform gridfun.

&----

---

*& Form data_reterival

&----

---

• text

----

---

form data_retrieval.

select vbeln

netwr

kunnr

from vbak

into table t_vbak

where vbeln in s_vbeln and

kunnr in s_kunnr.

if not t_vbak[] is initial.

select vbeln

posnr

matnr

kwmeng

netpr

from vbap

into table t_vbap

for all entries in t_vbak

where vbeln = t_vbak-vbeln and

matnr in s_matnr.

endif.

if not t_vbap[] is initial.

select vbeln

kunnr

parvw

from vbpa

into table t_vbpa

for all entries in t_vbap

where vbeln = t_vbap-vbeln and

parvw = 'RE '.

endif.

if not t_vbpa[] is initial.

select kunnr

name1

from kna1

into table t_kna1

for all entries in t_vbpa

where kunnr = t_vbpa-kunnr.

endif.

if not t_kna1[] is initial.

select vbelv

posnv

vbeln

vbtyp_n

from vbfa

into table t_vbfa

for all entries in t_vbap

where vbelv = t_vbap-vbeln and

posnv = t_vbap-posnr and

vbtyp_n = 'J' and

vbeln in s_deliv.

endif.

if not t_vbfa[] is initial.

select vbeln

posnr

lfimg

from lips

into table t_lips

for all entries in t_vbfa

where vbeln = t_vbfa-vbeln and

posnr = t_vbfa-posnv.

endif.

if not t_lips[] is initial.

select vbeln

posnr

lfsta

lfgsa

from vbup

into table t_vbup

for all entries in t_vbap

where vbeln = t_vbap-vbeln and

posnr = t_vbap-posnr and

lfsta = 'B' and

lfgsa = 'B'.

endif.

loop at t_vbup where lfsta = 'B' and

lfgsa = 'B'.

t_output-vbeln = t_vbup-vbeln.

t_output-posnr = t_vbup-posnr.

t_output-lfsta = t_vbup-lfsta.

t_output-lfgsa = t_vbup-lfgsa.

clear t_vbak.

read table t_vbak with key vbeln = t_output-vbeln

binary search.

if sy-subrc = 0.

t_output-kunnr = t_vbak-kunnr.

t_output-netwr = t_vbak-netwr.

endif.

clear t_vbap.

read table t_vbap with key vbeln = t_output-vbeln

posnr = t_output-posnr binary search.

if sy-subrc = 0.

t_output-matnr = t_vbap-matnr.

t_output-kwmeng = t_vbap-kwmeng.

t_output-netpr = t_vbap-netpr.

endif.

clear t_vbfa.

read table t_vbfa with key vbelv = t_output-vbeln binary search.

if sy-subrc = 0.

t_output-vbeln1 = t_vbfa-vbeln.

endif.

clear t_lips.

read table t_lips with key vbeln = t_output-vbeln1 binary search.

if sy-subrc = 0.

t_output-lfimg = t_lips-lfimg.

endif.

read table t_vbpa with key vbeln = t_output-vbeln

parvw = 'RE' binary search.

if sy-subrc = 0.

t_output-kunnr1 = t_vbpa-kunnr.

endif.

clear t_kna1.

read table t_kna1 with key kunnr = t_output-kunnr

binary search.

if sy-subrc = 0.

t_output-name1 = t_kna1-name1.

endif.

clear t_vbpa.

read table t_vbpa with key vbeln = t_output-vbeln

parvw = 'RE' binary search.

if sy-subrc = 0.

t_output-kunnr1 = t_vbpa-kunnr.

endif.

clear t_kna1.

read table t_kna1 with key kunnr = t_output-kunnr1

binary search.

if sy-subrc = 0.

t_output-name = t_kna1-name1.

endif.

append t_output.

endloop.

endform. "data_reterival

"data_reterival

&----

---

*& Form fieldcatalog

&----

---

• text

----

---

form fieldcatalog.

fcat-col_pos = 1.

fcat-fieldname = 'VBELN'.

fcat-seltext_m = 'Sale Order'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 2.

fcat-fieldname = 'POSNR'.

fcat-seltext_m = 'Item'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 3.

fcat-fieldname = 'MATNR'.

fcat-seltext_m = 'Material'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 4.

fcat-fieldname = 'KUNNR'.

fcat-seltext_m = 'Sold-to-party'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 5.

fcat-fieldname = 'NAME1'.

fcat-seltext_m = 'Name'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 6.

fcat-fieldname = 'KUNNR1'.

fcat-seltext_m = 'Bill-to-party'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 7.

fcat-fieldname = 'NAME'.

fcat-seltext_m = 'Name'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 8.

fcat-fieldname = 'KWMENG'.

fcat-seltext_m = 'Order quantity'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 9.

fcat-fieldname = 'NETPR'.

fcat-seltext_m = 'Net price'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 10.

fcat-fieldname = 'NETWR'.

fcat-seltext_m = 'Net value'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 11.

fcat-fieldname = 'VBELN1'.

fcat-seltext_m = 'Delivery'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 12.

fcat-fieldname = 'LFIMG'.

fcat-seltext_m = 'Delivery quantity'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 13.

fcat-fieldname = 'LFSTA'.

fcat-seltext_m = 'Delivery status'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

fcat-col_pos = 14.

fcat-fieldname = 'LFGSA'.

fcat-seltext_m = 'Overall delivery status'.

fcat-tabname = 'T_OUTPUT'.

append fcat to fcat1.

endform. "fieldcatalog

&----

---

*& Form gridfun

&----

---

• text

----

---

form gridfun.

gd_repid = sy-repid.

call function 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = gd_repid

i_callback_user_command = 'USER_COMMAND'

it_fieldcat = fcat1

it_events = gt_events

i_save = 'X'

TABLES

t_outtab = t_output

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. "gridfun

&----

---

*& Form USER_COMMAND

&----

---

• text

----

---

• -->U_UCOMM text

• -->US_SELFIELDtext

----

---

FORM USER_COMMAND USING U_UCOMM TYPE SYUCOMM

US_SELFIELD TYPE SLIS_SELFIELD. "#EC CALLED

CASE U_UCOMM.

WHEN '&IC1'. " Double click

if

us_selfield-fieldname = 'MATNR'.

read table t_output index us_selfield-tabindex.

check sy-subrc eq 0.

set parameter id 'MAT' field t_output-matnr.

call transaction 'MM03' and skip first screen.

elseIF US_SELFIELD-FIELDNAME = 'VBELN'.

READ TABLE T_OUTPUT INDEX US_SELFIELD-TABINDEX.

CHECK SY-SUBRC EQ 0.

SET PARAMETER ID 'AUN' FIELD T_OUTPUT-VBELN.

CALL TRANSACTION 'VA03' AND SKIP FIRST SCREEN.

elseif

us_selfield-fieldname = 'KUNNR'.

read table t_output index us_selfield-tabindex.

check sy-subrc eq 0.

set parameter id 'KUN' field t_output-kunnr.

call transaction 'FD03' and skip first screen.

elseif

us_selfield-fieldname = 'KUNNR1'.

read table t_output index us_selfield-tabindex.

check sy-subrc eq 0.

set parameter id 'KUN' field t_output-kunnr.

call transaction 'FD03' and skip first screen.

endif.

endcase.

thanks in advance

[Former Member]

hi,

1 first remove occurs 0,use types.

types : begin of t_vbpa ,

vbeln like vbpa-vbeln,

kunnr like vbpa-kunnr,

parvw like vbpa-parvw,

end of t_vbpa.

data : i_vbpa type standard table of t_vbpa with header line.

2.tables : vbap,vbak, lips.only selection screen fields' tables should be declared in tables option.

3.after each select statement check sy-subrc = 0.

sort with key field.

and delete adjacent duplcates.

if u use binary search u have to sort it first.

make changes ur performance will improve