03-23-2010 11:48 PM
Hi, am using the following tables:
TABLES: ekko,ekpo.
with Inner Join
select ekkobukrs ekkoebeln ekkoaedat ekkobsart ekkoekgrp ekkolifnr ekkowaers ekkowkurs ekkoernam ekpobrtwr
into CORRESPONDING FIELDS OF TABLE itab from ekko
inner join EKPO on ekPOEBELN = ekkoebeln.
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 ekkobukrs ekkoebeln ekkoaedat ekkobsart ekkoekgrp ekkolifnr ekkowaers ekkowkurs ekkoernam sum( ekpobrtwr )
into CORRESPONDING FIELDS OF TABLE itab from ekko
inner join EKPO on ekPOEBELN = ekkoebeln
WHERE ekkobukrs IN S_bukrs AND ekkobsart IN s_bsart
Group By ekkoebeln ekkobukrs ekkoaedat ekkobsart ekkoekgrp ekkolifnr ekkowaers ekkowkurs 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
03-24-2010 12:12 PM
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 .
03-24-2010 1:02 AM
03-24-2010 8:33 AM
actually am trying the follwoing select statment but it is not working:
select ekkobukrs ekkoebeln ekkoaedat ekkobsart ekkoekgrp ekkolifnr ekkowaers ekkowkurs ekko~ernam
sum( ekpo~brtwr )
into CORRESPONDING FIELDS OF TABLE itab from ekko inner join EKPO on ekPOEBELN = ekkoebeln
WHERE ekkobukrs IN S_bukrs AND ekkobsart IN s_bsart
Group By ekkoebeln ekkobukrs ekkoaedat ekkobsart ekkoekgrp ekkolifnr ekkowaers ekkowkurs ekko~ernam.
03-24-2010 8:38 AM
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
03-24-2010 8:51 AM
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".
03-24-2010 8:59 AM
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
03-24-2010 1:12 AM
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
03-24-2010 8:26 AM
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
03-24-2010 9:11 AM
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
03-24-2010 8:38 AM
" 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 ekkobukrs ekkoebeln ekkoaedat ekkobsart ekkoekgrp ekkolifnr ekkowaers ekkowkurs ekkoernam ekpobrtwr
into CORRESPONDING FIELDS OF TABLE itab from ekko
inner join EKPO on ekPOEBELN = ekkoebeln.
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
03-24-2010 9:22 AM
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.
03-24-2010 10:25 AM
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
03-24-2010 12:06 PM
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)
03-24-2010 12:12 PM
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 .
03-24-2010 8:05 PM
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 .