Summarization and Optimization

[Former Member]

Hi Gurus,

Please help me to find way of optimizing my program and extract data...i have to extract data coming from VBRK and KONV which having a large number of data so even i use SA38 to schedule and execute my program in background it doesnt work or continue and my job always cancelled....now i filter it out and divide it into monthly and come up of summing up the value according to condition type to lessen the space and data volume but my codes doesnt work in summing up those value so i ask for some codes or tips to sum-up please...im using also ALV format...i used also COLLECT and MODIFY ITAB TRANSPORTING KWERT but it doesnt work out..help me plz guys

Thanks,

nips

[andreas_mann3]

hi,

make a test run with some data on-line

-> create a layout with your sums and save

-> make layout to your standard layout with layout manager

.> start your report with all data in batch run

A.

Message was edited by:

Andreas Mann

[Former Member]

It would help if we could see the code. Would you post it please?

Rob

[Former Member]

Hi,

Here's my code:

&----

---

*& Report ZSALES_BY_SALESGRP

*&

&----

---

*&

*&

&----

---

REPORT ZSALES_BY_SALESGRP no standard page heading.

type-pools: slis.

tables: vbrk,

konv,

knvv,

vbrp.

types: begin of t_doc,

fkart like vbrk-fkart,

knumv like vbrk-knumv,

rfbsk like vbrk-rfbsk,

VKGRP like knvv-VKGRP,

end of t_doc,

begin of t_con,

kschl like konv-kschl,

sakn1 like konv-sakn1,

kwert like konv-kwert,

end of t_con,

begin of t_itab,

rfbsk like vbrk-rfbsk,

fkart like vbrk-fkart,

VKGRP like knvv-VKGRP,

kschl like konv-kschl,

sakn1 like konv-sakn1,

kwert like konv-kwert,

gross like konv-kwert,

rud like konv-kwert,

tradeinv like konv-kwert,

trdiscret like konv-kwert,

tr_ret like konv-kwert,

frgoods like konv-kwert,

sp_invoice like konv-kwert,

sp_discret like konv-kwert,

cm_disc_dist like konv-kwert,

cm_discmark like konv-kwert,

cmex_dist like konv-kwert,

cmex_mark like konv-kwert,

cm_tax like konv-kwert,

end of t_itab.

data: doc type standard table of t_doc,

wa_doc like line of doc,

con type standard table of t_con,

wa_con like line of con,

itab type standard table of t_itab with header line,

list type slis_fieldcat_alv,

t_list type slis_t_fieldcat_alv,

sort_field type slis_sortinfo_alv,

sort_t_field type slis_t_sortinfo_alv,

mess1(30).

selection-screen begin of block blk1 with frame title title1.

select-options: VKGRP for knvv-VKGRP.

selection-screen end of block blk1.

selection-screen begin of block blk2 with frame title title2.

select-options: fkdat for vbrk-fkdat obligatory default '20070901' to '20070930',

erdat for vbrk-erdat.

selection-screen end of block blk2.

perform get_and_display.

initialization.

title1 = 'Make an Entry'.

title2 = 'Date Selection'.

list-fieldname = 'VKGRP'.

list-just = 'C'.

list-seltext_l = 'Sales Group'.

append list to t_list.

list-fieldname = 'FKART'.

list-just = 'C'.

list-seltext_l = 'Billing Type'.

append list to t_list.

list-fieldname = 'KSCHL'.

list-just = 'C'.

list-seltext_l = 'Condition Type'.

append list to t_list.

list-fieldname = 'SAKN1'.

list-seltext_l = 'GL Account'.

append list to t_list.

list-fieldname = 'KWERT'.

list-seltext_l = 'Condition Value'.

list-do_sum = 'X'.

list-outputlen = 20.

append list to t_list.

list-fieldname = 'GROSS'.

list-seltext_l = 'Gross Sales'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'RUD'.

list-seltext_l = 'RUD'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'TR_RET'.

list-seltext_l = 'Trade Returns'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'FRGOODS'.

list-seltext_l = 'Free Goods'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'TRADEINV'.

list-seltext_l = 'Trade Disc Invoice'.

list-just = 'C'.

list-outputlen = 20.

append list to t_list.

list-fieldname = 'TRDISCRET'.

list-seltext_l = 'Trade Disc Returns'.

list-just = 'C'.

list-outputlen = 20.

append list to t_list.

list-fieldname = 'SP_INVOICE'.

list-seltext_l = 'Special Disc Invoice'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'SP_DISCRET'.

list-seltext_l = 'Special Disc Returns'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'CM_DISC_DIST'.

list-seltext_l = 'CM Disc Dist'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'CM_DISCMARK'.

list-seltext_l = 'CM Disc Mktg'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'CMEX_DIST'.

list-seltext_l = 'CM Exp Dist'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'CMEX_MARK'.

list-seltext_l = 'CM Exp Mktg'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

list-fieldname = 'CM_TAX'.

list-seltext_l = 'CM Tax'.

list-just = 'C'.

list-outputlen = 18.

append list to t_list.

*CLEAR sort_field.

• sort_field-fieldname = 'KSCHL'.

• sort_field-spos = 1.

• sort_field-up = 'X'.

• sort_field-subtot = 'X'.

• APPEND sort_field TO sort_t_field.

form get_and_display.

select a_{fkart a}knumv a_{rfbsk b}VKGRP from vbrk as a

inner join knvv as b on a_{kunag = b}kunnr into table doc

where rfbsk = 'C'

and a~fkdat in fkdat

and a~erdat in erdat

and a~fkart in ('ZUF2','ZUIN','ZUVF','ZUS2','ZC23','ZC24','ZC26','ZUL2','ZUG2')

and b~VKGRP in VKGRP.

if sy-subrc = 0.

select kschl sakn1 kwert from konv into table con

for all entries in doc

where knumv = doc-knumv

and kinak = ' '

and kschl in ('ZU01','R100','ZC25','ZDTR','ZDTL','ZDTP','ZDDA','ZRET','ZD06','ZDMI','ZDNP','ZDNE','ZDTD',

'ZC20','ZC01','ZC02','ZC03','ZC04','ZC05','ZC06','ZC07','ZC10','ZC14','ZC18','ZM09','ZC19',

'ZC27').

endif.

loop at doc into wa_doc.

loop at con into wa_con.

sort itab ascending by fkart kschl.

• delete adjacent duplicates from itab comparing fkart kschl.

move: wa_doc-vkgrp to itab-vkgrp,

wa_doc-fkart to itab-fkart,

wa_doc-rfbsk to itab-rfbsk,

wa_con-kschl to itab-kschl,

wa_con-sakn1 to itab-sakn1,

<b> wa_con-kwert to itab-kwert.</b> " <<----

---

I have to sum-up this

append itab.

endloop.

endloop.

if sy-subrc = 0.

loop at itab.

if ( ( itab-fkart = 'ZUF2' or itab-fkart = 'ZUIN' or itab-fkart = 'ZUVF' or itab-fkart = 'ZUS2' ) )

and itab-kschl = 'ZU01' and itab-sakn1 <> ' '.

move: itab-kwert to itab-gross.

modify itab.

elseif itab-kschl = 'ZU01' and itab-fkart = 'ZUS2' and itab-sakn1 = ' '.

move: itab-kwert to itab-rud.

modify itab.

elseif itab-kschl = 'ZU01' and ( ( itab-fkart = 'ZURE' or itab-fkart = 'ZUS2'

or itab-fkart = 'ZC23' or itab-fkart = 'ZC24' or itab-fkart = 'ZC26' ) ) and itab-sakn1 = ' '.

move: itab-kwert to itab-tr_ret.

modify itab.

elseif ( ( itab-fkart = 'ZUF2' or itab-fkart = 'ZUG2' or itab-fkart = 'ZUL2'

or itab-fkart = 'ZURE' or itab-fkart = 'ZUVF') )

and ( ( itab-kschl = 'R100' or itab-kschl = 'ZC25' ) ) and itab-sakn1 <> ' '.

move: itab-kwert to itab-frgoods.

modify itab.

elseif ( ( itab-fkart = 'ZUF2' or itab-fkart = 'ZUIN' or itab-fkart = 'ZUVF' ) )

and ( ( itab-kschl = 'ZDTR' or itab-kschl = 'ZDTL' or itab-kschl = 'ZDTP'

or itab-kschl = 'ZM01' or itab-kschl = 'ZDDA' ) ).

move: itab-kwert to itab-tradeinv.

modify itab.

elseif ( ( itab-fkart = 'ZURE' or itab-fkart = 'ZUS2' ) )

and ( ( itab-kschl = 'ZDTR' or itab-kschl = 'ZDTL' or itab-kschl = 'ZDTP' or itab-kschl = 'ZDDA'

or itab-kschl = 'ZRET' ) ).

move: itab-kwert to itab-trdiscret.

modify itab.

elseif ( ( itab-fkart = 'ZUF2' or itab-fkart = 'ZUIN'

or itab-fkart = 'ZUVF' or itab-fkart <> 'ZURE' or itab-fkart <> 'ZUS2' ) )

and ( ( itab-kschl = 'ZD06' or itab-kschl = 'ZDMI' or itab-kschl = 'ZDNP'

or itab-kschl = 'ZDNE' or itab-kschl = 'ZDTD') ).

move: itab-kwert to itab-sp_invoice.

modify itab.

elseif ( ( itab-fkart = 'ZURE' or itab-fkart = 'ZUS2' ) )

and ( ( itab-kschl = 'ZD06' or itab-kschl = 'ZDMI' or itab-kschl = 'ZDNP'

or itab-kschl = 'ZDNE' or itab-kschl = 'ZDTD' ) ).

move: itab-kwert to itab-sp_discret.

modify itab.

elseif itab-kschl = 'ZC20' or itab-kschl = 'ZC01' or itab-kschl = 'ZC02' or itab-kschl = 'ZC05'

or itab-kschl = 'ZC06' or itab-kschl = 'ZC07'.

move: itab-kwert to itab-cm_disc_dist.

modify itab.

elseif itab-kschl = 'ZC27' OR itab-kschl = 'ZC19' or itab-kschl = 'ZM09'.

move: itab-kwert to itab-cm_discmark.

modify itab.

elseif ( ( itab-fkart = 'ZUG2' or itab-fkart = 'ZUL2' ) )

and ( ( itab-kschl = 'ZC01' or itab-kschl = 'ZC02' or itab-kschl = 'ZC18' or itab-kschl = 'ZC10'

or itab-kschl = 'ZC11' ) ).

move: itab-kwert to itab-cmex_dist.

modify itab.

elseif ( ( itab-fkart = 'ZUG2' or itab-fkart = 'ZUS2' ) )

and ( ( itab-kschl = 'ZC09' or itab-kschl = 'ZC14' ) ).

move: itab-kwert to itab-cmex_mark.

modify itab.

elseif itab-kschl = 'ZC03' or itab-kschl = 'ZC04'.

move: itab-kwert to itab-cm_tax.

modify itab.

endif.

endloop.

endif.

call function 'REUSE_ALV_LIST_DISPLAY'

exporting

it_fieldcat = t_list

• it_sort = sort_t_field

tables

t_outtab = itab.

endform.

Thanks,

nips

[Former Member]

OK - the SELECT does use a secondary index (AND b~vkgrp IN vkgrp), so I doubt if that's the source of the problem. Depending on the size of the tables, the below code contains nested loops and that is probably where your problem is:

  LOOP AT doc INTO wa_doc.
    LOOP AT con INTO wa_con.
      SORT itab ASCENDING BY fkart kschl.
      MOVE: wa_doc-vkgrp TO itab-vkgrp,
      wa_doc-fkart TO itab-fkart,
      wa_doc-rfbsk TO itab-rfbsk,
      wa_con-kschl TO itab-kschl,
      wa_con-sakn1 TO itab-sakn1,
      wa_con-kwert TO itab-kwert. " <<-------- I have to sum-up this
      APPEND itab.
    ENDLOOP.
  ENDLOOP.

To fix this, please see:

<a href="/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops">The Performance of Nested Loops</a>

Rob