Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Need help with extracting sales data

Hi all,

We want to get a list of net sales (invoice minus credit notes) per customer by Item Group. This would look like:

Customer Code | Customer Name | Item GroupA | Item GroupB | Item GroupC etc..

The data appears to be in the Sales Analysis Report if we select the Items tab and use "Group Display" and "Total By Customer". However the format of that is different to the above.

Can anyone suggest either:

- how to do the same thing via query

- how to best convert the standard output into the above format (perhaps some Excel trickery?)

Thanks in advance,

Raj

Former Member
Former Member replied

Hi Raj,

Try this query. You change the tables as per your needs.

select OCRD.CardName ,

case when OITB.ItmsGrpCod = 103 then SUM(doctotal) else 0 end as 'Amplifier (AMP)',

CASE when OITB.ItmsGrpCod = 104 then SUM(doctotal) else 0 end as 'Audio Tech (AUT)'

from ocrd inner join oinv on ocrd.cardcode = oinv.cardcode

inner join nnm1 on oinv.series = nnm1.series

inner join inv1 on oinv.docentry = inv1.docentry

inner join OITM on OITM.ItemCode = INV1.ItemCode

inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod

where oinv.docdate >= CONVERT(DATETIME, '4/1/2011', 102)

and oinv.docdate <= convert(datetime,'3/31/2012',102)

and ocrd.cardcode = 'CA0163'

group by OCRD.CardName , OITB.ItmsGrpCod

Union

select OCRD.CardName ,

case when OITB.ItmsGrpCod = 103 then SUM(-doctotal) else 0 end as 'Amplifier (AMP)',

CASE when OITB.ItmsGrpCod = 104 then SUM(-doctotal) else 0 end as 'Audio Tech (AUT)'

from ocrd inner join orin on ocrd.cardcode = orin.cardcode

inner join nnm1 on orin.series = nnm1.series

inner join rin1 on orin.docentry = rin1.docentry

inner join OITM on OITM.ItemCode = RIN1.ItemCode

inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod

where orin.docdate >= CONVERT(DATETIME, '4/1/2011', 102)

and orin.docdate <= convert(datetime,'3/31/2012',102)

and ocrd.cardcode = 'CA0163'

group by ocrd.CardName , oitb.ItmsGrpCod

order by 1,2

Thanks

Malhaar

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question