Skip to Content
0
Jun 21, 2020 at 07:57 PM

Items Group Wise Sale Report ( Warehouse & Sales employee )

84 Views

Dear SAP Gents,

I want the same two report as per below query instead of BP Name please fix one report for warehouse Name wise and the second one for Sales Employee Name wise in same pattern. Can anyone help to me your assit will be highly apprecated.

This is the report pattern for your reference:.

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(ISNULL(GrssProfit,0))/SUM(ISNULL(LineTotal,1) )*100

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)

As "% GP",

(select sum(Gtotal)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) As"Gross Sale",

(select sum(Gtotal)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(Gtotal)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 Snack & Conf',

(select sum(Gtotal)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 & Dairy',

(select sum(Gtotal)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(Gtotal)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(Gtotal)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(Gtotal)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 'Cereals Cofee & Tea',

(select sum(Gtotal)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(Gtotal)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(Gtotal)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(Gtotal)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(Gtotal)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',

(select sum(Gtotal)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 'Rice Lentil & Spices'

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

GROUP BY T0.CARDNAME


Regards,

Mohammad Firoz