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