cancel
Showing results for 
Search instead for 
Did you mean: 

Sales report by Sales Employee with BP code name & address

tats-olivo
Explorer
0 Kudos

Hi guys can I please have some help with a Query on SAP B1? I basically need the following Monthly Sales report by Sales employee

Containing the following: Date, Customer Number, Customer Name, Customer Address, shipping address, Total Sales for month

I got this far but for some reason i can't make it work with AR invoice data (OINV)

SELECT T1.[CardCode], T1.[CardName], T1.[Address], T1.[ZipCode], T1.[State1], T1.[MailAddres], T1.[MailZipCod], T1.[State2], T2.[DocNum], T2.[DocDate], T2.[DocTotal] FROM [dbo].[OSLP] T0 INNER JOIN [dbo].[OCRD] T1 ON T0.[SlpCode] = T1.[SlpCode] INNER JOIN OINV T2 ON T1.[CardCode] = T2.[CardCode] WHERE T0.[SlpName] =[%0]

Thank you so much in advanced !

Tatiana

Accepted Solutions (1)

Accepted Solutions (1)

former_member588507
Participant
0 Kudos

Hi Tatiana,

Try this query and enter parameter and use slpcode instead of slpname.

SELECT T1.[CardCode]
	  , T1.[CardName]
	  , T1.[Address]
	  , T1.[ZipCode]
	  , T1.[State1]
	  , T1.[MailAddres]
	  , T1.[MailZipCod]
	  , T1.[State2]
	  , T2.[DocNum]
	  , T2.[DocDate]
	  , T2.[DocTotal] 
 FROM [dbo].[OSLP] T0 
 INNER JOIN [dbo].[OCRD] T1 ON T0.[SlpCode] = T1.[SlpCode] 
 INNER JOIN OINV T2 ON T1.[CardCode] = T2.[CardCode] 
 WHERE 
 T0.[SlpCode] =[%0] and 
 T2.DocDate>=[%1]
  and T2.DocDate<=[%2]



Regards,

Satish Kumar

Answers (2)

Answers (2)

dku242
Explorer
0 Kudos

Hi Satish , query is great but could it be amended to give salesperson name instead of code and also to tally all orders from the same vendor into one row giving total number of sales.

So basically the columns are Customer code, Customer Name, Sales Person , A/R Invoice ( number of invoices), A/R invoice total( total amount by currency)

Hope you have an answer , the report i currently do takes to much time.

thx

Deepak Kumar

tats-olivo
Explorer
0 Kudos

Thank you so much ! It worked ! Hopefully this is what my sales manager needs