on 11-27-2018 9:58 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you so much ! It worked ! Hopefully this is what my sales manager needs
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.