The customer who sells the most isn't always the one who leaves you the most money
In most companies that use SAP Business One, the sales team celebrates customers for their billing volume. However, when you dig into the numbers, you discover something uncomfortable: some of your «best» customers are the least profitable. Aggressive discounts, frequent returns, low-margin products, and extended payment conditions can turn a star account into a disguised financial drag.
The problem is that SAP Business One does not include a native report that cross-references sales, costs, margins, and payment behavior by customer in a consolidated way. Consequently, you need to build SQL queries for customer profitability that they unify all that information to give you a real value map. Below, I show you how to do it step by step with versions for SQL Server and SAP HANA.
What does customer profitability actually measure in SAP Business One?
Before running any query, it is important to understand what variables make up the actual profitability. Therefore, you must consider these five key indicators:
Gross margin: The difference between what you invoice and what the product costs you. It is the starting point, but it is not enough on its own.
Gross profit percentage: A customer who buys $100,000 with a 10% margin is not the same as one who buys $30,000 with a 40% margin. The percentage reveals the quality of the sale.
Purchase frequency: A customer who buys once a year is more costly to maintain than one who buys monthly. Furthermore, recurrence indicates stability.
Payment behavior: If a customer takes 90 days to pay when the terms are 30 days, you are financing their operations with your working capital.
Credit notes and returns: A customer with a high volume of returns destroys operational profitability. As a result, what looks like a successful sale turns into a loss.
The tables involved in SAP Business One
To build these profitability queries, you need to master the following tables:
OCRD (Business Partners): Business partner master with name, group, assigned sales representative, and payment terms. OINV / INV1 (Invoices): Sales invoices where INV1 has the line-by-line detail with prices, costs, and the GrssProfit field. ORIN / RIN1 (Credit Notes): Credit notes representing post-sale returns or discounts. ORCT / RCT2 (Receipts): Received payments that allow calculating the average actual payment days. OSLP (Sales Persons): Assigned sales representatives. OCTG (Payment Terms): Agreed payment terms.
Query 1: Customer ranking by profitability in SAP Business One
This query consolidates net sales, cost, gross margin, and margin percentage by customer over a given period. It is the starting point for any profitability analysis.
SQL Server version
DECLARE @StartDate DATE = '2025-01-01'
DECLARE @EndDate DATE = '2026-03-31'
SELECT
T0.CardCode AS 'Customer Code',
T0.CardName AS 'Customer Name',
T3.SlpName AS 'Salesperson',
COUNT(DISTINCT T1.DocNum) AS 'Number of Invoices',
CAST(SUM(T2.LineTotal) AS DECIMAL(15,2)) AS 'Net Sales',
CAST(SUM(T2.StockValue) AS DECIMAL(15,2)) AS 'Total Cost',
CAST(SUM(T2.GrssProfit) AS DECIMAL(15,2)) AS 'Gross Profit',
CAST(CASE WHEN SUM(T2.LineTotal) = 0 THEN 0
ELSE SUM(T2.GrssProfit) * 100.0 / SUM(T2.LineTotal)
END AS DECIMAL(5,2)) AS '% Margin',
CAST(SUM(T2.LineTotal) / COUNT(DISTINCT T1.DocNum) AS DECIMAL(15,2)) AS 'Average Receipt'
FROM OCRD T0
INNER JOIN OINV T1 ON T1.CardCode = T0.CardCode
INNER JOIN INV1 T2 ON T2.DocEntry = T1.DocEntry
LEFT JOIN OSLP T3 ON T3.SlpCode = T0.SlpCode
WHERE T1.DocDate BETWEEN @StartDate AND @EndDate
AND T1.CANCELED = 'N'
GROUP BY T0.CardCode, T0.CardName, T3.SlpName
ORDER BY SUM(T2.GrssProfit) DESCSAP HANA version
DO BEGIN
DECLARE StartDate DATE = '2025-01-01';
DECLARE EndDate DATE = '2026-03-31';
SELECT
T0."CardCode" AS "Customer Code",
T0."CardName" AS "Customer Name",
T3."SlpName" AS "Salesperson",
COUNT(DISTINCT T1."DocNum") AS "Number of Invoices",
CAST(SUM(T2."LineTotal") AS DECIMAL(15,2)) AS "Net Sales",
CAST(SUM(T2."StockValue") AS DECIMAL(15,2)) AS "Total Cost",
CAST(SUM(T2."GrssProfit") AS DECIMAL(15,2)) AS "Gross Profit",
CAST(CASE WHEN SUM(T2."LineTotal") = 0 THEN 0
ELSE SUM(T2."GrossProfit") * 100.0 / SUM(T2."LineTotal")
END AS DECIMAL(5,2)) AS "% Margin",
CAST(SUM(T2."LineTotal") / COUNT(DISTINCT T1."DocNum") AS DECIMAL(15,2)) AS "Average Ticket"
FROM "OCRD" T0
INNER JOIN "OINV" T1 ON T1."CardCode" = T0."CardCode"
INNER JOIN "INV1" T2 ON T2."DocEntry" = T1."DocEntry"
LEFT JOIN "OSLP" T3 ON T3."SlpCode" = T0."SlpCode"
WHERE T1."DocDate" BETWEEN :StartDate AND :EndDate
AND T1."CANCELED" = 'N'
GROUP BY T0."CardCode", T0."CardName", T3."SlpName"
ORDER BY SUM(T2."GrssProfit") DESC;
END;What does it reveal? Order all your customers by actual gross profit, not by volume. The GrssProfit field in INV1 automatically calculates the difference between the selling price and the item cost. Therefore, you can quickly identify who generates value and who only generates movement.
Query 2: Impact of credit notes by customer
Credit notes are a silent indicator of problems. This query cross-references sales against returns to reveal which customers are eroding your margin.
SQL Server version
DECLARE @StartDate DATE = '2025-01-01'
DECLARE @EndDate DATE = '2026-03-31'
SELECT
Sales.CardCode, Sales.CardName,
Sales.NetSales,
ISNULL(NC.TotalNC, 0) AS 'Credit Memos',
Sales.NetSales - ISNULL(NC.TotalNC, 0) AS 'Actual Sales',
CAST(CASE WHEN Sales.NetSales = 0 THEN 0
ELSE ISNULL(NC.TotalNC, 0) * 100.0 / Sales.NetSales
END AS DECIMAL(5,2)) AS '% Return'
FROM (
SELECT T1.CardCode, T0.CardName,
SUM(T2.LineTotal) AS NetSales
FROM OCRD T0
INNER JOIN OINV T1 ON T1.CardCode = T0.CardCode
INNER JOIN INV1 T2 ON T2.DocEntry = T1.DocEntry
WHERE T1.DocDate BETWEEN @StartDate AND @EndDate
AND T1.CANCELED = 'N'
GROUP BY T1.CardCode, T0.CardName
) Sales
LEFT JOIN (
SELECT T1.CardCode, SUM(T2.LineTotal) AS TotalNC
FROM ORIN T1
INNER JOIN RIN1 T2 ON T2.DocEntry = T1.DocEntry
WHERE T1.DocDate BETWEEN @StartDate AND @EndDate
AND T1.CANCELED = 'N'
GROUP BY T1.CardCode
) NC ON NC.CardCode = Sales.CardCode
ORDER BY ISNULL(NC.TotalNC, 0) DESCSAP HANA version
DO BEGIN
DECLARE StartDate DATE = '2025-01-01';
DECLARE EndDate DATE = '2026-03-31';
SELECT
"Sales"."CardCode", "Sales"."CardName",
"Sales"."NetSales",
IFNULL("NC"."TotalNC", 0) AS "Credit Memo",
"Sales"."NetSales" - IFNULL("NC"."TotalNC", 0) AS "Actual Sales",
CAST(CASE WHEN "Sales"."NetSales" = 0 THEN 0
ELSE IFNULL("NC"."TotalNC", 0) * 100.0 / "Sales"."NetSales"
END AS DECIMAL(5,2)) AS "% Return"
FROM (
SELECT T1."CardCode", T0."CardName",
SUM(T2."LineTotal") AS "Net Sales"
FROM "OCRD" T0
INNER JOIN "OINV" T1 ON T1."CardCode" = T0."CardCode"
INNER JOIN "INV1" T2 ON T2."DocEntry" = T1."DocEntry"
WHERE T1."DocDate" BETWEEN :StartDate AND :EndDate
AND T1."CANCELED" = 'N'
GROUP BY T1."CardCode", T0."CardName"
) "Sales"
LEFT JOIN (
SELECT T1."CardCode", SUM(T2."LineTotal") AS "TotalNC"
FROM "ORIN" T1
INNER JOIN "RIN1" T2 ON T2."DocEntry" = T1."DocEntry"
WHERE T1."DocDate" BETWEEN :StartDate AND :EndDate
AND T1."CANCELED" = 'N'
GROUP BY T1."CardCode"
) "NC" ON "NC"."CardCode" = "Sales"."CardCode"
ORDER BY IFNULL("NC"."TotalNC", 0) DESC;
END;If a customer has a return rate higher than 10%, you need to investigate. Is it a quality issue, a logistics issue, or is it a strategy the customer is using to obtain hidden discounts? However, without this query, you would never know because credit memos are processed in isolation.
Query 3: Actual payment behavior vs. agreed-upon condition
This query compares actual payment days against the agreed commercial term. It is essential for detecting customers who finance their operations with your working capital.
SQL Server version
SELECT
T0.CardCode AS 'Customer Code',
T0.CardName AS 'Customer Name',
T3.PymntGroup AS 'Payment Terms',
T3.ExtraDays AS 'Agreed Days',
COUNT(T1.DocNum) AS 'Paid Invoices',
CAST(AVG(DATEDIFF(DAY, T1.DocDate, T2.DocDate)) AS DECIMAL(5,1))
AS 'Average Days to Pay',
CAST(AVG(DATEDIFF(DAY, T1.DocDate, T2.DocDate)) - T3.ExtraDays
AS DECIMAL(5,1)) AS 'Average Days Past Due'
FROM OCRD T0
INNER JOIN OINV T1 ON T1.CardCode = T0.CardCode
INNER JOIN ORCT T2 ON T2.DocNum IN (
SELECT U0.DocNum FROM RCT2 U0
WHERE U0.InvType = 13 AND U0.DocEntry = T1.DocEntry)
INNER JOIN OCTG T3 ON T3.GroupNum = T0.GroupNum
WHERE T1.CANCELED = 'N'
AND T1.PaidToDate >= T1.DocTotal
AND T1.DocDate >= DATEADD(YEAR, -1, GETDATE())
GROUP BY T0.CardCode, T0.CardName, T3.PymntGroup, T3.ExtraDays
HAVING COUNT(T1.DocNum) >= 3
ORDER BY AVG(DATEDIFF(DAY, T1.DocDate, T2.DocDate)) - T3.ExtraDays DESCSAP HANA version
SELECT
T0."CardCode" AS "Customer Code",
T0."CardName" AS "Customer Name",
T3."PymntGroup" AS "Payment Terms",
T3."ExtraDays" AS "Agreed Days",
COUNT(T1."DocNum") AS "Paid Invoices",
CAST(AVG(DAYS_BETWEEN(T1."DocDate", T2."DocDate")) AS DECIMAL(5,1))
AS "Average Payment Days",
CAST(AVG(DAYS_BETWEEN(T1."DocDate", T2."DocDate")) - T3."ExtraDays"
AS DECIMAL(5,1)) AS "Average Days Past Due"
FROM "OCRD" T0
INNER JOIN "OINV" T1 ON T1."CardCode" = T0."CardCode"
INNER JOIN "ORCT" T2 ON T2."DocNum" IN (
SELECT U0."DocNum" FROM "RCT2" U0
WHERE U0."InvType" = 13 AND U0."DocEntry" = T1."DocEntry")
INNER JOIN "OCTG" T3 ON T3."GroupNum" = T0."GroupNum"
WHERE T1."CANCELED" = 'N'
AND T1."PaidToDate" >= T1."DocTotal"
AND T1."DocDate" >= ADD_YEARS(CURRENT_DATE, -1)
GROUP BY T0."CardCode", T0."CardName", T3."PymntGroup", T3."ExtraDays"
HAVING COUNT(T1."DocNum") >= 3
ORDER BY AVG(DAYS_BETWEEN(T1."DocDate", T2."DocDate")) - T3."ExtraDays" DESCThe «Average Days Overdue» field is the key indicator. A high positive value indicates a customer who systematically finances their operation with your money. Furthermore, it is important to note that in SAP HANA, it is used DAYS_BETWEEN instead of DATEDIFF and ADD_YEARS instead of DATEADD.
Query 4: Customer profitability balanced scorecard
Finally, this query automatically classifies each customer into an A-B-C-D category based on their actual margin. It is the ultimate tool for segmenting your portfolio.
SQL Server version
SELECT
T0.CardCode, T0.CardName,
CAST(SUM(T2.GrssProfit) AS DECIMAL(15,2)) AS 'Gross Profit',
CAST(CASE WHEN SUM(T2.LineTotal) = 0 THEN 0
ELSE SUM(T2.GrssProfit) * 100.0 / SUM(T2.LineTotal)
END AS DECIMAL(5,2)) AS '% Margin',
COUNT(DISTINCT T1.DocNum) AS 'Invoices',
CASE
WHEN SUM(T2.GrossProfit) * 100.0 / NULLIF(SUM(T2.LineTotal), 0) >= 25 THEN 'A - Premium'
WHEN SUM(T2.GrossProfit) * 100.0 / NULLIF(SUM(T2.LineTotal), 0) >= 15 THEN 'B - Profitable'
WHEN SUM(T2.GrssProfit) * 100.0 / NULLIF(SUM(T2.LineTotal), 0) >= 5 THEN 'C - Marginal'
ELSE 'D - Unprofitable'
END AS 'Classification'
FROM OCRD T0
INNER JOIN OINV T1 ON T1.CardCode = T0.CardCode
INNER JOIN INV1 T2 ON T2.DocEntry = T1.DocEntry
WHERE T1.CANCELED = 'N'
AND T1.DocDate >= DATEADD(YEAR, -1, GETDATE())
GROUP BY T0.CardCode, T0.CardName
ORDER BY SUM(T2.GrssProfit) DESCSAP HANA version
SELECT
T0."CardCode", T0."CardName",
CAST(SUM(T2."GrssProfit") AS DECIMAL(15,2)) AS "Gross Profit",
CAST(CASE WHEN SUM(T2."LineTotal") = 0 THEN 0
ELSE SUM(T2."GrssProfit") * 100.0 / SUM(T2."LineTotal")
END AS DECIMAL(5,2)) AS "% Margin",
COUNT(DISTINCT T1."DocNum") AS "Invoices",
CASE
WHEN SUM(T2."GrossProfit") * 100.0 / NULLIF(SUM(T2."LineTotal"), 0) >= 25 THEN 'A - Premium'
WHEN SUM(T2."GrossProfit") * 100.0 / NULLIF(SUM(T2."LineTotal"), 0) >= 15 THEN 'B - Profitable'
WHEN SUM(T2."GrssProfit") * 100.0 / NULLIF(SUM(T2."LineTotal"), 0) >= 5 THEN 'C - Marginal'
ELSE 'D - Unprofitable'
END AS "Classification"
FROM "OCRD" T0
INNER JOIN "OINV" T1 ON T1."CardCode" = T0."CardCode"
INNER JOIN "INV1" T2 ON T2."DocEntry" = T1."DocEntry"
WHERE T1."CANCELED" = 'N'
AND T1."DocDate" >= ADD_YEARS(CURRENT_DATE, -1)
GROUP BY T0."CardCode", T0."CardName"
ORDER BY SUM(T2."GrssProfit") DESCThis A-B-C-D classification allows you to segment your portfolio by actual profitability. Consequently, you can protect the Premium ones, develop the Profitable ones, renegotiate with the Marginal ones, and evaluate whether it is worth keeping the Unprofitable ones. Furthermore, by having versions for SQL Server and HANA, you can run this analysis regardless of the version of SAP Business One you use.
Conclusion: profitability is not assumed, it is measured
Having these queries in your Query Manager transforms your company's sales conversation. It is no longer about «who sells the most» but about «who generates the most value.» A sales team that understands the customer profitability in SAP Business One make completely different decisions: negotiate prices better, prioritize the right clients, and stop subsidizing resource-draining accounts.
Furthermore, with SQL Server and SAP HANA versions included, you can implement this analysis regardless of your database infrastructure. Therefore, there are no excuses for continuing to operate blindly.
When was the last time you reviewed the actual profitability of your client portfolio? If the answer is «never,» these queries are your starting point.
