Skip to Content
0
Jun 21, 2020 at 01:26 PM

Sales Employee Team Wise Item Group Sale

35 Views

Hello SAP Gents,

I need the same report could you fix Sales Employee Team Wise Item Group Sale instead of Customer BP Name

  1. Sales Employee Name
  2. Subtotal Gross Sale from All Group Sales
  3. % GP Over All Sales from All Group
  4. Individual Group Sale
  5. % GP of Individual Group sale

Declare @FromDate Datetime Declare @ToDate Datetime Set @FromDate = (select min(T0.DocDate) from OINV T0 Where T0.Docdate >= [%0]) Set @ToDate = (Select max(T0.Docdate) from OINV T0 Where T0.DocDate <=[%1])

SELECT T0.CARDNAME,

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '100') as 'Hygienic & Personal',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '101') as 'Commodities',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '102') as 'Bevrages',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '103') as 'Spices',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '104') as 'Grocery & Staples',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '105') as 'Cooking Oil',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '106') as 'Biscuits Snacks',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '107') as 'Breakfast & Pasta',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '108') as 'Smallware',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '109') as 'Baby Cereals',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '110') as 'Confectionery',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '111')as 'Dairy Coffee Tea',

(select sum(linetotal)from oinv inner join inv1 on oinv.docentry=inv1.docentry inner join oitm on oitm.itemcode=inv1.itemcode where oinv.cardname=T0.CardName and oinv.docdate between @FromDate and @ToDate and oitm.itmsgrpcod = '112') as 'Misc'

FROM

OINV T0 where T0.docdate between @FromDate and @ToDate

GROUP BY

T0.CARDNAME

Regards,

Mohammd Firoz