SAP Business One Sales by Rep Query
SQL queries to get sales by rep from SAP B1 — with returns deducted and tax excluded for accurate numbers.
These are sample queries you can run on Manager Boost. Use them as-is, or edit and customize on our platform. Go custom or use our pre-built queries — you're in control.
Key Concepts
How Sales Reps Link to Invoices
Each invoice has a SlpCode field pointing to the sales employee in OSLP. This is set when the invoice is created, usually inherited from the customer's default sales rep or manually selected.
Why Exclude Tax?
DocTotal includes VAT/tax. For sales performance, you want product revenue — not tax pass-through. Always subtract VatSum to get net revenue.
Why Include Credit Notes?
ORIN contains A/R credit memos (returns, refunds). If a rep sells $100K but $30K gets returned, their real number is $70K. Always UNION ORIN with negative amounts to get accurate sales.
The CANCELED Field
SAP doesn't delete cancelled documents — it marks them with CANCELED = 'Y'. Always filter CANCELED = 'N' to exclude cancelled invoices from reports.
Available Fields Reference
OINV / ORIN (Documents)
DocEntry DocNum DocDate CardCode CardName DocTotal VatSum SlpCode CANCELED
OSLP (Sales Employees)
SlpCode SlpName Memo Commission GroupCode Active Email Mobil
Basic Query (Without Returns)
Net sales by rep for the current year. This is simple but ignores credit notes — use it only for quick checks, not accurate reporting.
SELECT
T1.SlpName,
COUNT(T0.DocNum) AS InvoiceCount,
SUM(T0.DocTotal - T0.VatSum) AS NetSales
FROM OINV T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CANCELED = 'N'
AND YEAR(T0.DocDate) = YEAR(GETDATE())
GROUP BY T1.SlpName
ORDER BY NetSales DESC| SlpName | InvoiceCount | NetSales |
|---|---|---|
| John Smith | 45 | 125000.00 |
Limitation: A rep with high credit notes will look better than they should. Always use the returns-deducted version for accurate reporting.
Accurate Query (With Returns)
UNION invoices and credit notes into one dataset. Credits are negated so they subtract from the total. This is the correct way to calculate sales rep performance.
SELECT
SlpName,
SUM(CASE WHEN DocType = 'INV' THEN 1 ELSE 0 END) AS Invoices,
SUM(CASE WHEN DocType = 'CRN' THEN 1 ELSE 0 END) AS CreditNotes,
SUM(NetAmount) AS NetSales
FROM (
-- Invoices (positive)
SELECT T1.SlpName, 'INV' AS DocType, (T0.DocTotal - T0.VatSum) AS NetAmount
FROM OINV T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CANCELED = 'N'
AND YEAR(T0.DocDate) = YEAR(GETDATE())
UNION ALL
-- Credit notes (negative)
SELECT T1.SlpName, 'CRN' AS DocType, -(T0.DocTotal - T0.VatSum) AS NetAmount
FROM ORIN T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CANCELED = 'N'
AND YEAR(T0.DocDate) = YEAR(GETDATE())
) Combined
GROUP BY SlpName
ORDER BY NetSales DESC| SlpName | Invoices | CreditNotes | NetSales |
|---|---|---|---|
| John Smith | 45 | 3 | 118500.00 |
This Month vs Last Month
Compare performance across periods using CASE statements. The DATEADD/DATEDIFF pattern gives you the first day of current and previous months without hardcoding dates.
SELECT
SlpName,
SUM(CASE WHEN DocDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
THEN NetAmount ELSE 0 END) AS ThisMonth,
SUM(CASE WHEN DocDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
AND DocDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
THEN NetAmount ELSE 0 END) AS LastMonth
FROM (
SELECT T1.SlpName, T0.DocDate, (T0.DocTotal - T0.VatSum) AS NetAmount
FROM OINV T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CANCELED = 'N'
UNION ALL
SELECT T1.SlpName, T0.DocDate, -(T0.DocTotal - T0.VatSum)
FROM ORIN T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CANCELED = 'N'
) Combined
GROUP BY SlpName
ORDER BY ThisMonth DESC| SlpName | ThisMonth | LastMonth |
|---|---|---|
| John Smith | 28500.00 | 24200.00 |
Tip: Add a computed column ThisMonth - LastMonth AS Change and ROUND((ThisMonth - LastMonth) * 100.0 / NULLIF(LastMonth, 0), 1) AS ChangePct to show growth.