Sales Analysis Report by Customer broken down by Sales Rep

[former_member422880]

Is there a way to take the sales analysis report by customer and further break it down by the Sales Rep that handles the account OR is there the code used for the sales analysis report by customer somewhere so that I do not have to recreate that portion of the report.

[zal_parchem2]

Hello Lora...

What SAP Package are you using???

If it is SAP Business One (SAP B1), I already have some SQL which might fit your needs.

Just let us know.

Regards - Zal

[former_member422880]

I'm using SAP B1 version 9.2 (starter pack, not professional). If you have an SQL you are willing to share that would be great!

[zal_parchem2]

Here you go Lora - you might want to try this and, change it a bit to fit your your needs.

Good Luck and let us know how it works...

Regards, Zal

--D-SL Sales Analysis Report by Customer and Sales Person Ver 1 ZP 2017 12 01 RECON

--DESCRIPTION:  SQL lists out information to duplicate the Customer and Sales Employees Tabs on the Sales Analysis found under Sales Reports in the Sales AR Module.  SQL results are used as part of reconciling the GL Account to the Sales Analysis Report.  Can also be reconciled by filtering only on Item Type documents.  Then it reconciles to the Sales Analysis Report by Items Tab.

--AUTHOR(s):
--Version 1 Zal Parchem 01 December 2017


SELECT DISTINCT 

'SQL' AS 'Source',
T0.[DocDate] AS 'Posting Date',
T0.[DocNum] AS 'Origin No', 

CASE
WHEN T2.ObjType = 203 THEN 'AR Down Pay'
WHEN T0.CANCELED = 'C' THEN 'AR Invoice - CANCELLED'
WHEN T0.DocType = 'S' THEN 'AR Invoice - Serv Type'
ELSE 'AR Invoice' 
END AS 'Details',

--RECON WITH SALES ANALYSIS BY CUSTOMER AND SALES EMPLOYEES
CASE 
WHEN T2.ObjType = 203 THEN (T2.DrawnSum - T0.TotalExpns)
WHEN T0.CANCELED = 'C' THEN CAST(CONVERT(DECIMAL(18,2), (T0.DocTotal - T0.TotalExpns) * -1) AS NVARCHAR)
ELSE CAST(CONVERT(DECIMAL(18,2), (T0.DocTotal - T0.TotalExpns)) AS NVARCHAR)
END AS 'C/D (SC)',

'1' AS 'Count',

--RECON WITH SALES ANALYSIS BY CUSTOMER AND SALES EMPLOYEE TABS
CASE
WHEN T2.ObjType = 203 THEN T0.GrosProfSy
WHEN T0.CANCELED = 'C' THEN T0.GrosProfSy * -1
ELSE T0.GrosProfSy
END AS 'Gross Profit (USD)',

--CANNOT BE RECONCILED
CASE 
WHEN T2.ObjType = 203 THEN T2.DrawnSum
WHEN T0.CANCELED = 'C' THEN CAST(CONVERT(DECIMAL(18,2), (T0.DocTotal) * -1) AS NVARCHAR)
ELSE CAST(CONVERT(DECIMAL(18,2), (T0.DocTotal)) AS NVARCHAR)
END AS 'Document Total with Freight and Fees',

CASE
WHEN T2.ObjType = 203 THEN T0.TotalExpns
WHEN T0.CANCELED = 'C' THEN (T0.TotalExpns * -1)
ELSE T0.TotalExpns
END AS 'Fees',

CASE 
WHEN T2.ObjType = 203 THEN (T2.DrawnSum)
WHEN T0.CANCELED = 'C' THEN CAST(CONVERT(DECIMAL(18,2), (T3.LineTotal) * -1) AS NVARCHAR)
ELSE CAST(CONVERT(DECIMAL(18,2), (T3.LineTotal)) AS NVARCHAR)
END AS 'Freight',

T0.VatSum AS 'Taxes',

--RECON WITH SALES ANALYSIS BY ITEM TAB BY FILTERING ON ITEM
CASE
WHEN T0.DocType = 'I' THEN 'Item'
WHEN T0.DocType = 'S' THEN 'Service'
ELSE '*** Research ***'
END AS 'Document Type',

T0.CardCode AS 'Customer Number', 
T0.CardName AS 'Customer/Distributor Name',
T1.SlpName AS 'Sales Employee Name' 
--T0.Canceled

FROM OINV T0 

LEFT OUTER JOIN OSLP T1 
ON T0.SlpCode = T1.SlpCode

LEFT OUTER JOIN INV9 T2 
ON T0.DocEntry = T2.DocEntry 
AND T2.ObjType = 203

LEFT OUTER JOIN INV3 T3 
ON T0.DocEntry = T3.DocEntry 
AND T3.ExpnsCode = 1

WHERE 

T0.DocDate >= '[%0]' 
AND T0.DocDate <= '[%1]'

UNION ALL

SELECT DISTINCT 

'SQL' AS 'Source',
T0.[DocDate] AS 'Posting Date', 
T0.[DocNum] AS 'Origin No.',

CASE
WHEN T0.CANCELED = 'C' THEN 'AR Credit Memo - CANCELLED'
WHEN T0.DocType = 'S' THEN 'AR Credt Memo - Serv Type'
ELSE 'AR Credit Memo' 
END AS 'Details', 

--RECON WITH SALES ANALYSIS BY CUSTOMER AND SALES EMPLOYEE TABS
CASE 
WHEN T0.CANCELED = 'C' THEN CAST(CONVERT(DECIMAL(18,2), (T0.DocTotal - T0.TotalExpns)) AS NVARCHAR)
ELSE CAST(CONVERT(DECIMAL(18,2), (T0.DocTotal - T0.TotalExpns) * -1) AS NVARCHAR)
END AS 'C/D (SC)',

'1' AS 'Count', 

--RECON WITH SALES ANALYSIS BY CUSTOMER AND SALES EMPLOYEE TABS
CASE
WHEN T0.CANCELED = 'C' THEN T0.GrosProfSy
ELSE (T0.GrosProfSy * -1)
END AS 'Gross Profit (USD)',

--CANNOT BE RECONCILED
CASE WHEN T0.CANCELED = 'C' THEN CAST(CONVERT(DECIMAL(18,2), (T0.DocTotal)) AS NVARCHAR)
ELSE CAST(CONVERT(DECIMAL(18,2), (T0.DocTotal) * -1) AS NVARCHAR)
END AS 'Sales with Fees and Freight',

CASE
WHEN T0.CANCELED = 'C' THEN (T0.TotalExpns)
ELSE (T0.TotalExpns * -1)
END AS 'All Fees',

CASE 
--WHEN T2.ObjType = 203 THEN (T2.DrawnSum)
WHEN T0.CANCELED = 'C' THEN CAST(CONVERT(DECIMAL(18,2), (T3.LineTotal)) AS NVARCHAR)
ELSE CAST(CONVERT(DECIMAL(18,2), (T3.LineTotal) * -1) AS NVARCHAR)
END AS 'Freight',

T0.VatSum * -1 AS 'Taxes',

--RECON WITH SALES ANALYSIS BY ITEM TAB BY FILTERING ON ITEM
CASE
WHEN T0.DocType = 'I' THEN 'Item'
WHEN T0.DocType = 'S' THEN 'Service'
ELSE '*** Research ***'
END AS 'Doc Type',

T0.CardCode, 
T0.CardName,
T1.SlpName AS 'Sales Employee Name' 
--T0.Canceled

FROM ORIN T0 

LEFT OUTER JOIN OSLP T1 
ON T0.SlpCode = T1.SlpCode

LEFT OUTER JOIN RIN3 T3 
ON T0.DocEntry = T3.DocEntry 
AND T3.ExpnsCode = 1

WHERE 

T0.DocDate >= '[%0]' 
AND T0.DocDate <= '[%1]'

ORDER BY

T0.DocDate,
T0.DocNum

Notice that it balances to the penny for 01 June 2018 to today: