Finding Sum of grouped lines / Tables EKKO, EKPO

[Yasin]

Hi, am using the following tables:

TABLES: ekko,ekpo.

with Inner Join

select ekko_{bukrs ekko}ebeln ekko_{aedat ekko}bsart ekko_{ekgrp ekko}lifnr ekko_{waers ekko}wkurs ekko_{ernam ekpo}brtwr

into CORRESPONDING FIELDS OF TABLE itab from ekko

inner join EKPO on ekPO_{EBELN = ekko}ebeln.

end-OF-SELECTION.

Basically i want to group the result of this SQL statement by Purchasing Doc (ekko~ebeln) and display the

sum of Gross value (ekpo~brtwr) for the line items of each Purchasing Doc.

Note: i want the Purchasing Doc to appear one time only in the report

i have tried the following :

select ekko_{bukrs ekko}ebeln ekko_{aedat ekko}bsart ekko_{ekgrp ekko}lifnr ekko_{waers ekko}wkurs ekko_{ernam sum( ekpo}brtwr )

into CORRESPONDING FIELDS OF TABLE itab from ekko

inner join EKPO on ekPO_{EBELN = ekko}ebeln

WHERE ekko_{bukrs IN S_bukrs AND ekko}bsart IN s_bsart

Group By ekko_{ebeln ekko}bukrs ekko_{aedat ekko}bsart ekko_{ekgrp ekko}lifnr ekko_{waers ekko}wkurs ekko~ernam.

then loop through the ITAB:

LOOP at itab.

write:/ itab-bukrs under 'Company Code',

itab-ebeln UNDER 'PO-Number',

itab-aedat UNDER 'Date' ,

itab-bsart UNDER 'Doc-Type' ,

itab-ekgrp UNDER 'Purchase-Grp' ,

itab-lifnr UNDER 'Vendor' ,

itab-waers UNDER 'Currency',

itab-wkurs UNDER 'Rate' ,

itab-brtwr under 'Gross Value',

itab-ernam UNDER 'Created By'.

ENDLOOP.

but it is not working with me, am not able to show the result of sum( ekpo~brtwr ) am getting 0 in all lines

thanks a lot for your help

[Former Member]

Hi,

why cann't you collect the internal table.

[Yasin]

actually am trying the follwoing select statment but it is not working:

select ekko_{bukrs ekko}ebeln ekko_{aedat ekko}bsart ekko_{ekgrp ekko}lifnr ekko_{waers ekko}wkurs ekko~ernam

sum( ekpo~brtwr )

into CORRESPONDING FIELDS OF TABLE itab from ekko inner join EKPO on ekPO_{EBELN = ekko}ebeln

WHERE ekko_{bukrs IN S_bukrs AND ekko}bsart IN s_bsart

Group By ekko_{ebeln ekko}bukrs ekko_{aedat ekko}bsart ekko_{ekgrp ekko}lifnr ekko_{waers ekko}wkurs ekko~ernam.

[Former Member]

hi,

If you want the ekko-ebeln to appear only once then use only that in the GROUP BY clause

you've used the BUKRS and other fields too

try it !

cheers,

anoop

[Yasin]

Dear noob

if i put the "Group By ekko~ebeln." only the select statment giving an error about missing fields

The field "EKKO~ERNAM" from the SELECT list is is missing in the GROUP

BY clause. is missing in the GROUP BY clause. is missing in the GROUP

BY clause. is missing in the GROUP BY clause. is missing in the GROUP

BY clause. is "EKKO~EBELN".

[Former Member]

Hi Yasin,

sorry for overlooking the single field in the GROUP, ignore it please.

You' d need to put these fields into an internal table and use the COLLECT statement to sum up the BRTWR values.

something like this, create another internal table like your main internal table and then loop, collect and append

DATA:

itab2 TYPE itab1 OCCURS 0 with header line.

LOOP AT itab1 into itab2.

COLLECT itab2.

APPEND itab2.

ENDLOOP.

regds, Anoop

Edited by: Anoop Shankaran on Mar 24, 2010 2:30 PM

[alex_cook]

Howdy,

Perhaps the easiest way is to use two queries and then calculate the sum using ABAP, eg:

select ekko~bukrs ekko~ebeln ekko~aedat ekko~bsart ekko~ekgrp ekko~lifnr ekko~waers ekko~wkurs ekko~ernam
into CORRESPONDING FIELDS OF TABLE itab from ekko
inner join EKPO on ekPO~EBELN = ekko~ebeln
WHERE ekko~bukrs IN S_bukrs AND ekko~bsart IN s_bsart.

SELECT *
    FROM EKPO
    FOR ALL ENTRIES IN itab
    INTO TABLE itab_ekpo
    WHERE ebeln = itab-ebeln.

loop at itab assigning <itab>.
    loop at itab_ekpo assigning <ekpo> where ebeln = <itab>-ebeln.
        <itab>-brtwr = <itab>-brtwr + <ekpo>-brtwr.
    endloop.
endloop.

Cheers

Alex

[Yasin]

Dear Alex

the select stamtnet is giving the follwoin error:

"FOR ALL ENTRIES IN itab" is allowed only with where condetion.

Edited by: YasinN on Mar 24, 2010 9:30 AM

[alex_cook]

Howdy,

Sorry I just wrote it off the top of my head, try swapping the FOR ALL ENTRIES and INTO clauses, ie:

SELECT *
    FROM EKPO
    INTO TABLE itab_ekpo
    FOR ALL ENTRIES IN itab
    WHERE ebeln = itab-ebeln.

THe reason I avoid COLLECT and SUM statements is it can cause overflow errors with other fields if youre not careful.

Cheers

Alex

[Yasin]

" My Full Code.

TABLES: ekko,ekpo,ekbe,rbkp.

data: BEGIN OF itab OCCURS 0, "EKKO TABLE"

inco1 like ekko-inco1,

inco2 like ekko-inco2,

bukrs like ekko-bukrs,

ebeln like ekko-ebeln,

aedat like ekko-aedat,

bsart like ekko-bsart,

ekgrp like ekko-ekgrp,

lifnr like ekko-lifnr,

waers like ekko-waers,

wkurs like ekko-wkurs,

ernam like ekko-ernam,

BRTWR LIKE ekpo-brtwr,

end of itab.

SELECT-OPTIONS : s_bukrs FOR itab-bukrs OBLIGATORY,

s_ebeln for itab-ebeln ,

s_aedat FOR itab-aedat ,

s_bsart for itab-bsart ,

s_ekgrp FOR itab-ekgrp ,

s_lifnr FOR itab-lifnr ,

s_budat FOR jtab-budat ,

s_bewtp for jtab-bewtp .

DATA ProceesITAB LIKE SORTED TABLE OF ITAB WITH UNIQUE KEY TABLE LINE.

AT SELECTION-SCREEN on s_bukrs.

START-OF-SELECTION.

select ekko_{bukrs ekko}ebeln ekko_{aedat ekko}bsart ekko_{ekgrp ekko}lifnr ekko_{waers ekko}wkurs ekko_{ernam ekpo}brtwr

into CORRESPONDING FIELDS OF TABLE itab from ekko

inner join EKPO on ekPO_{EBELN = ekko}ebeln.

end-OF-SELECTION.

LOOP at itab.

write:/ itab-bukrs under 'Company Code',

itab-ebeln UNDER 'PO-Number',

itab-aedat UNDER 'Date' ,

itab-bsart UNDER 'Doc-Type' ,

itab-ekgrp UNDER 'Purchase-Grp' ,

itab-lifnr UNDER 'Vendor' ,

itab-waers UNDER 'Currency',

itab-wkurs UNDER 'Rate' ,

itab-brtwr under 'Gross Value',

itab-ernam UNDER 'Created By'.

ENDLOOP.

TOP-OF-PAGE.

write:/70 'Company'.

uline.

skip.

write:/5 'Company Code',25 'PO-Number',40 'Date',55 'Doc-Type',70 'Purchase-Grp',85 'Vendor',100 'Currency',115 'Rate',125 'Created By',

140 'Gross Value'.

ULINE.

SKIP.

Edited by: YasinN on Mar 24, 2010 9:45 AM

[kesavadas_thekkillath]

Try this

select ebeln_i bukrs aedat bsart ekgrp lifnr waers wkurs ernam BRTWR_I
into table i_po from WB2_V_EKKO_EKPO2
where bukrs in s_bukrs
and bsart in s_bsart.

sort i_po by ebeln ascending.
loop at i_po into wa_po.
move-corresponsing wa_po to wa_po1.
at new ebeln.
sum.
wa_po1-brtwr_i = wa_po-brtwr_i.
write wa_po1.
clear wa_po1.
skip 1.
endat.
endloop.

[Yasin]

i will make it more clear coz nothing of your posts is working with me, but i realy appocieate your efforts.

let us take the follwoing output for Document No 4510000452 , as you can all see it contian 3 line items with thier Gross value:

Company Code |||||||||||||||||| PO-Number ||||||||||||||||| Gross Value

2000 ||||||||||||||||| 4510000452 |||||||||||||||| 30.380,00

2000 ||||||||||||||||| 4510000452 ||||||||||||||||| 86.920,00

2000 ||||||||||||||||| 4510000452 ||||||||||||||||| 20.380,00

i want to display following output:

Company Code |||||||||||||||||| PO-Number ||||||||||||||||| Gross Value

2000 ||||||||||||||||| 4510000452 ||||||||||||||||| 137.68,00

Edited by: YasinN on Mar 24, 2010 11:26 AM

[Yasin]

the following code solve my problem but am facing another problem

LOOP AT ITAB INTO LINE.

AT END OF ebeln.

SUM.

SKIP.

EndAt.

EndLoop .

the Sum statment is summing all Numeric Coloumns while i need to apply the sum on one field only (brtwr)

[kesavadas_thekkillath]

LOOP AT ITAB INTO LINE.

move-corresponding line to line1. "<-----

AT END OF ebeln.

SUM.

line1-field = your summed field."<------

SKIP.

EndAt.

now line1 will have the values you require

EndLoop .

[Yasin]

Dear Keshav.T

thanks a lot, your code solved my problem, and the final code is:

LOOP AT ITAB INTO LINE.

move-corresponding line to line1.

AT END OF ebeln.

Sum.

LINE1-ernam = LINE-ernam.

SKIP.

EndAt.

EndLoop .