A customer buys three boxes of nitrile gloves every month and has done so for two years. He has never bought hand sanitizer. Across the rest of the customer base, those two codes show up together in seven out of every ten invoices, yet nobody ever offered it to him. The data was sitting in INV1 from day one. Cross-selling analysis in SAP Business One exists precisely for that: surfacing the product pairs your customers already buy together, and spotting who is missing half of the pair.
This SAP Business One cross-selling tutorial builds that report from scratch, with four queries you can paste into Query Manager today. No add-on modules, no extra licenses, nothing exported to Excel.
Why You Need to Measure Cross-Selling in SAP Business One
Most of the companies I work with share the same blind spot: they know exactly how much of each product they sold, but they have no idea what it sold alongside. The standard sales-by-item report hands you a column of totals, and that column hides the most profitable information in the system, because the incremental margin on selling a second product to a customer who already buys from you carries no acquisition cost, requires no campaign, goes through no tender, and usually closes on the same call as the original order. In other words, a SAP Business One cross-selling report does not replace the traditional sales report: it completes it.
What is that information worth? The figures circulating in the industry are consistent with one another: McKinsey estimates a revenue lift of 10% to 15% when recommendations rest on real basket data, and the 2025 commercial statistics roundups put the increase in ticket size between 12% and 20% for companies that work cross-selling systematically. Moreover, the effect compounds: a customer buying across three product families is far less likely to leave than one buying a single family.

Now, what makes this interesting for anyone running an ERP is that SAP Business One already holds 100% of the data you need. No BI project required. What you need is a well-written query.
Prerequisites
- SAP Business One 9.x or 10.x, on Microsoft SQL Server or SAP HANA.
- Authorization of Query Manager (Tools -> Queries -> Query Manager).
- At least 12 months of posted sales invoices. With less history, the pairs you find will be noise.
- Knowing your item master: which codes are inventory items and which are freight, services or administrative charges.
- Desirable: direct read access to the database, so you can test in a sandbox before touching production.
We will use four tables: OINV (customer invoice header), INV1 (invoice lines), OITM (item master) and OCRD (business partners). For the analysis by salesperson, add OSLP. Therefore, the whole SAP Business One cross-selling exercise rests on tables you already know by heart.
SAP Business One Cross-Selling: The Step-by-Step Tutorial
The method has four movements. First we define what a “basket” is. Then we count which product pairs land in the same basket. Next we measure whether that co-occurrence is real or a statistical mirage. And finally we bring the finding down to a list of customers with first and last names.
Step 1: Define Your Basket Unit
A basket is a document, and in SAP Business One cross-selling that definition decides everything else. In practice, the sales invoice is almost always the right choice (OINV), because it is the document that actually got paid. However, if your operation delivers in partial shipments and invoices consolidated at month end, the invoice lies: in that case use the delivery (ODLN/DLN1) or the sales order (ORDER/RDR1). The query structure is identical; only the table names change.
Step 2: Clean Out the Lines That Contaminate
This is the detail you only learn by invoicing. If you do not filter, your SAP Business One cross-selling report will tell you with great confidence that the flagship product sells together with “FLETE-STGO” and “CARGO-ADM”. True, and useless. Therefore, exclude non-inventory codes with OITM.InvntItem = 'Y' and drop service invoices with OINV.DocType = 'I'.
One more thing worth removing: the bundles already defined as sales bills of materials. Those pairs are not a commercial finding; they are your own definition coming back through the side door.
Step 3: Build the Join and Measure
With the baskets cleaned up, the engine behind SAP Business One cross-selling is a self-join of the table against itself by document number. Moreover, the condition B.ItemCode > A.ItemCode does two things at once: it stops a product from pairing with itself, and it stops the A-B pair from showing up again as B-A.
The Master Query: Product Pairs That Travel Together
This first query answers the basic question of any SAP Business One cross-selling analysis, namely which combinations appear most often on the same invoice over the last twelve months. The Having with a threshold of 5 is deliberate: without it, you will get thousands of pairs that happened exactly once.
Dónde ejecutar este query. Úsalo en SQL Server Management Studio o en HANA Studio. El Generador de consultas de SAP Business One no siempre digiere las expresiones de tabla común (WITH), así que si necesitas el resultado dentro de B1, guarda la consulta como vista y llama a la vista desde el Generador.
SQL Server
-- Pares de productos comprados juntos (ultimos 12 meses)
WITH Cestas AS (
SELECT DISTINCT
T0.DocEntry,
T1.ItemCode
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode = T1.ItemCode
WHERE T0.DocDate >= DATEADD(MONTH, -12, GETDATE())
AND T0.CANCELED = 'N'
AND T0.DocType = 'I'
AND T2.InvntItem = 'Y'
)
SELECT TOP 100
A.ItemCode AS Item_A,
MAX(IA.ItemName) AS Nombre_A,
B.ItemCode AS Item_B,
MAX(IB.ItemName) AS Nombre_B,
COUNT(DISTINCT A.DocEntry) AS Facturas_Juntos
FROM Cestas A
INNER JOIN Cestas B
ON B.DocEntry = A.DocEntry
AND B.ItemCode > A.ItemCode
INNER JOIN OITM IA ON IA.ItemCode = A.ItemCode
INNER JOIN OITM IB ON IB.ItemCode = B.ItemCode
GROUP BY A.ItemCode, B.ItemCode
HAVING COUNT(DISTINCT A.DocEntry) >= 5
ORDER BY Facturas_Juntos DESC
SAP HANA
-- Pares de productos comprados juntos (ultimos 12 meses)
WITH "Cestas" AS (
SELECT DISTINCT
T0."DocEntry",
T1."ItemCode"
FROM OINV T0
INNER JOIN INV1 T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN OITM T2 ON T2."ItemCode" = T1."ItemCode"
WHERE T0."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
AND T0."CANCELED" = 'N'
AND T0."DocType" = 'I'
AND T2."InvntItem" = 'Y'
)
SELECT
A."ItemCode" AS "Item_A",
MAX(IA."ItemName") AS "Nombre_A",
B."ItemCode" AS "Item_B",
MAX(IB."ItemName") AS "Nombre_B",
COUNT(DISTINCT A."DocEntry") AS "Facturas_Juntos"
FROM "Cestas" A
INNER JOIN "Cestas" B
ON B."DocEntry" = A."DocEntry"
AND B."ItemCode" > A."ItemCode"
INNER JOIN OITM IA ON IA."ItemCode" = A."ItemCode"
INNER JOIN OITM IB ON IB."ItemCode" = B."ItemCode"
GROUP BY A."ItemCode", B."ItemCode"
HAVING COUNT(DISTINCT A."DocEntry") >= 5
ORDER BY "Facturas_Juntos" DESC
LIMIT 100
A warning with my name on it: the first time I ran a self-join like this one, I did it without the date filter, against an eleven-year database, on a Monday at 9:40 with the entire sales force invoicing. The query never finished, and the complaint arrived before the result did. Filter the period from your very first attempt.
Support, Confidence and Lift: The Three Metrics That Sort the Noise
Raw counting has a well-known problem: high-volume products end up at the top of everything. If you sell screws on 80% of your invoices, the screw will pair with half the catalog without that meaning anything at all. Consequently, a well-built SAP Business One cross-selling analysis rests on three indicators rather than on a count.
- Support: what percentage of all invoices contains the pair. It measures volume.
- Confidence: out of the invoices carrying A, what percentage also carries B. It measures the strength of the rule in one direction.
- Lift: how many times more likely the two are to appear together compared with what we would expect if they were independent. A lift of 1.0 means coincidence; above 1.5 there is already a relationship worth looking at.

Lift is what separates a real finding from a truism. It is also the only one of the three that penalizes ubiquitous products, which is why it is the right column to sort by.
SQL Server
-- Soporte, confianza y lift por par de productos
WITH Cestas AS (
SELECT DISTINCT T0.DocEntry, T1.ItemCode
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode = T1.ItemCode
WHERE T0.DocDate >= DATEADD(MONTH, -12, GETDATE())
AND T0.CANCELED = 'N'
AND T0.DocType = 'I'
AND T2.InvntItem = 'Y'
),
Total AS (
SELECT COUNT(DISTINCT DocEntry) AS N FROM Cestas
),
Frec AS (
SELECT ItemCode, COUNT(DISTINCT DocEntry) AS Apariciones
FROM Cestas GROUP BY ItemCode
),
Pares AS (
SELECT A.ItemCode AS Item_A, B.ItemCode AS Item_B,
COUNT(DISTINCT A.DocEntry) AS Juntos
FROM Cestas A
INNER JOIN Cestas B ON B.DocEntry = A.DocEntry
AND B.ItemCode > A.ItemCode
GROUP BY A.ItemCode, B.ItemCode
)
SELECT TOP 50
P.Item_A,
IA.ItemName AS Nombre_A,
P.Item_B,
IB.ItemName AS Nombre_B,
P.Juntos,
CAST(100.0 * P.Juntos / T.N AS DECIMAL(10,2)) AS Soporte_Pct,
CAST(100.0 * P.Juntos / FA.Apariciones AS DECIMAL(10,2)) AS Confianza_AaB_Pct,
CAST(100.0 * P.Juntos / FB.Apariciones AS DECIMAL(10,2)) AS Confianza_BaA_Pct,
CAST( (1.0 * P.Juntos / T.N) /
((1.0 * FA.Apariciones / T.N) * (1.0 * FB.Apariciones / T.N))
AS DECIMAL(10,2)) AS Lift
FROM Pares P
CROSS JOIN Total T
INNER JOIN Frec FA ON FA.ItemCode = P.Item_A
INNER JOIN Frec FB ON FB.ItemCode = P.Item_B
INNER JOIN OITM IA ON IA.ItemCode = P.Item_A
INNER JOIN OITM IB ON IB.ItemCode = P.Item_B
WHERE P.Juntos >= 5
ORDER BY Lift DESC, P.Juntos DESC
SAP HANA
-- Soporte, confianza y lift por par de productos
WITH "Cestas" AS (
SELECT DISTINCT T0."DocEntry", T1."ItemCode"
FROM OINV T0
INNER JOIN INV1 T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN OITM T2 ON T2."ItemCode" = T1."ItemCode"
WHERE T0."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
AND T0."CANCELED" = 'N'
AND T0."DocType" = 'I'
AND T2."InvntItem" = 'Y'
),
"Total" AS (
SELECT COUNT(DISTINCT "DocEntry") AS "N" FROM "Cestas"
),
"Frec" AS (
SELECT "ItemCode", COUNT(DISTINCT "DocEntry") AS "Apariciones"
FROM "Cestas" GROUP BY "ItemCode"
),
"Pares" AS (
SELECT A."ItemCode" AS "Item_A", B."ItemCode" AS "Item_B",
COUNT(DISTINCT A."DocEntry") AS "Juntos"
FROM "Cestas" A
INNER JOIN "Cestas" B ON B."DocEntry" = A."DocEntry"
AND B."ItemCode" > A."ItemCode"
GROUP BY A."ItemCode", B."ItemCode"
)
SELECT
P."Item_A",
IA."ItemName" AS "Nombre_A",
P."Item_B",
IB."ItemName" AS "Nombre_B",
P."Juntos",
TO_DECIMAL(100.0 * P."Juntos" / T."N", 10, 2) AS "Soporte_Pct",
TO_DECIMAL(100.0 * P."Juntos" / FA."Apariciones", 10, 2) AS "Confianza_AaB_Pct",
TO_DECIMAL(100.0 * P."Juntos" / FB."Apariciones", 10, 2) AS "Confianza_BaA_Pct",
TO_DECIMAL( (1.0 * P."Juntos" / T."N") /
((1.0 * FA."Apariciones" / T."N") * (1.0 * FB."Apariciones" / T."N")),
10, 2) AS "Lift"
FROM "Pares" P
CROSS JOIN "Total" T
INNER JOIN "Frec" FA ON FA."ItemCode" = P."Item_A"
INNER JOIN "Frec" FB ON FB."ItemCode" = P."Item_B"
INNER JOIN OITM IA ON IA."ItemCode" = P."Item_A"
INNER JOIN OITM IB ON IB."ItemCode" = P."Item_B"
WHERE P."Juntos" >= 5
ORDER BY "Lift" DESC, P."Juntos" DESC
LIMIT 50
Notice that I included confidence in both directions. The rule is not always symmetrical: 90% of the customers who buy the printer may take the toner, while only 12% of those who buy toner take a printer. That asymmetry tells you which of the two products is the hook and which is the companion, and it organizes the sales conversation.
SAP Business One Cross-Selling: Variations for Your Business
The pairs are the raw material. The value shows up when you bring the finding down to a concrete action, and for that there are two queries I always use.
Customers Who Buy A but Never B
This is the query that turns SAP Business One cross-selling into a call list. Take the pair with the best lift from the previous query, swap in the two codes, and you get exactly who is missing half of the combination. Sorted by revenue, it also tells you where to start.
SQL Server
-- Clientes que compran ITEM_A y nunca han comprado ITEM_B
SELECT
C.CardCode,
C.CardName,
ISNULL(S.SlpName, 'Sin asignar') AS Vendedor,
COUNT(DISTINCT T0.DocEntry) AS Facturas_con_A,
MAX(T0.DocDate) AS Ultima_Compra_A,
CAST(SUM(T1.LineTotal) AS DECIMAL(18,2)) AS Venta_A_12M
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN OCRD C ON C.CardCode = T0.CardCode
LEFT JOIN OSLP S ON S.SlpCode = C.SlpCode
WHERE T0.DocDate >= DATEADD(MONTH, -12, GETDATE())
AND T0.CANCELED = 'N'
AND T1.ItemCode = 'ITEM_A'
AND NOT EXISTS (
SELECT 1
FROM OINV X
INNER JOIN INV1 Y ON Y.DocEntry = X.DocEntry
WHERE X.CardCode = T0.CardCode
AND X.CANCELED = 'N'
AND X.DocDate >= DATEADD(MONTH, -12, GETDATE())
AND Y.ItemCode = 'ITEM_B'
)
GROUP BY C.CardCode, C.CardName, S.SlpName
ORDER BY Venta_A_12M DESC
SAP HANA
-- Clientes que compran ITEM_A y nunca han comprado ITEM_B
SELECT
C."CardCode",
C."CardName",
IFNULL(S."SlpName", 'Sin asignar') AS "Vendedor",
COUNT(DISTINCT T0."DocEntry") AS "Facturas_con_A",
MAX(T0."DocDate") AS "Ultima_Compra_A",
TO_DECIMAL(SUM(T1."LineTotal"), 18, 2) AS "Venta_A_12M"
FROM OINV T0
INNER JOIN INV1 T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN OCRD C ON C."CardCode" = T0."CardCode"
LEFT JOIN OSLP S ON S."SlpCode" = C."SlpCode"
WHERE T0."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
AND T0."CANCELED" = 'N'
AND T1."ItemCode" = 'ITEM_A'
AND NOT EXISTS (
SELECT 1
FROM OINV X
INNER JOIN INV1 Y ON Y."DocEntry" = X."DocEntry"
WHERE X."CardCode" = T0."CardCode"
AND X."CANCELED" = 'N'
AND X."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
AND Y."ItemCode" = 'ITEM_B'
)
GROUP BY C."CardCode", C."CardName", S."SlpName"
ORDER BY "Venta_A_12M" DESC
So you do not have to edit the code every time you change pairs, replace the literals with variables and turn it into a parameterized query; the article on parameterized queries in SAP Business One walks through the full procedure using the [%0].
Basket Size by Salesperson
The other side of SAP Business One cross-selling looks inward. If one salesperson averages 1.4 distinct items per invoice while the team averages 3.2, you already know where the gap is. It is an uncomfortable indicator, and useful for exactly that reason.
SQL Server
-- Articulos distintos y ticket promedio por vendedor
SELECT
Vendedor,
COUNT(*) AS Facturas,
CAST(AVG(1.0 * Articulos) AS DECIMAL(10,2)) AS Articulos_Promedio,
CAST(AVG(Monto) AS DECIMAL(18,2)) AS Ticket_Promedio
FROM (
SELECT
ISNULL(S.SlpName, 'Sin asignar') AS Vendedor,
T0.DocEntry,
COUNT(DISTINCT T1.ItemCode) AS Articulos,
SUM(T1.LineTotal) AS Monto
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN OSLP S ON S.SlpCode = T0.SlpCode
WHERE T0.DocDate >= DATEADD(MONTH, -12, GETDATE())
AND T0.CANCELED = 'N'
AND T0.DocType = 'I'
GROUP BY ISNULL(S.SlpName, 'Sin asignar'), T0.DocEntry
) D
GROUP BY Vendedor
ORDER BY Articulos_Promedio DESC
SAP HANA
-- Articulos distintos y ticket promedio por vendedor
SELECT
D."Vendedor",
COUNT(*) AS "Facturas",
TO_DECIMAL(AVG(1.0 * D."Articulos"), 10, 2) AS "Articulos_Promedio",
TO_DECIMAL(AVG(D."Monto"), 18, 2) AS "Ticket_Promedio"
FROM (
SELECT
IFNULL(S."SlpName", 'Sin asignar') AS "Vendedor",
T0."DocEntry",
COUNT(DISTINCT T1."ItemCode") AS "Articulos",
SUM(T1."LineTotal") AS "Monto"
FROM OINV T0
INNER JOIN INV1 T1 ON T1."DocEntry" = T0."DocEntry"
LEFT JOIN OSLP S ON S."SlpCode" = T0."SlpCode"
WHERE T0."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
AND T0."CANCELED" = 'N'
AND T0."DocType" = 'I'
GROUP BY IFNULL(S."SlpName", 'Sin asignar'), T0."DocEntry"
) D
GROUP BY D."Vendedor"
ORDER BY "Articulos_Promedio" DESC
How to Take the Finding to the Counter
A report nobody opens changes nothing. Therefore, it pays to decide in advance how the result lands in daily operations. To bring SAP Business One cross-selling down to the salesperson's desk there are, moreover, three reasonable paths.
- Formatted search on the sales document. A user field in the order header that shows, based on the first item keyed in, the three complements with the best lift. The salesperson sees it while typing.
- Scheduled alert. The “buys A but not B” query loaded as a weekly alert addressed to each salesperson with their own portfolio.
- SQL view as the basis for a dashboard. If you are going to consume the result from Crystal Reports, Excel or a BI tool, store the logic in a reusable SQL view instead of copying the query text into five different places.

The first path converts best, because it intervenes at the exact moment of decision. It is also the most expensive to maintain: every time you change the lift threshold, you have to review the formatted search.
Common Mistakes When Implementing SAP Business One Cross-Selling
These are the stumbles I see most often in SAP Business One cross-selling projects, and I made some of them first.
- Forgetting
CANCELED = 'N'. Cancelled invoices remain in OINV with all their lines. Without that filter, the pairs inflate and nobody notices until someone reconciles against the sales ledger. - Confusing lines with baskets. If an item appears on two lines of the same invoice (because of batches or different prices), the count doubles. The
SELECT DISTINCTin the CTE exists precisely for that. - Reading lift as causation. Two products appearing together does not mean one brings the other; both may be pulled by a third, or by seasonality. Validate the finding with the sales team before building a campaign.
- Windows that are too short. With three months of history and a long-cycle business, noise eats the signal. Twelve months is the decent minimum; twenty-four is better if the catalog is stable.
- Running the analysis during business hours. The self-join is expensive. Schedule it overnight, or build a materialized view if your volume justifies it.
- Not saving the query in Query Manager under a naming convention. SAP's query manager has no versioning and no comments, and in 9.3, if memory serves, it did not allow a simple export of the whole library either; organizing by categories from the start keeps anyone from wondering, two years later, which of the eleven versions is the good one.
Best Practices
Start with a single product family instead of the entire catalog. Validating twenty pairs with the sales manager is far easier than two hundred, and the conversation is much more concrete. That is why a SAP Business One cross-selling project holds up better when it starts small. Then you expand.
Also, cross the result with margin. A pair with a sky-high lift on two low-margin products is a pretty exercise that moves nothing; if you want to prioritize by profitability, the logic behind margin analysis by product plugs directly into these queries.
Finally, set a frequency and stick to it. A basket analysis that runs once, gets presented to a committee and then filed away is not a management system: it is an anecdote. Monthly works well for most distribution businesses.
Official SAP Resources
- SAP Help Portal — SAP Business One: functional documentation and administration guides.
- SAP Business One, version for SAP HANA: syntax differences and specifics of the columnar database.
- SAP Support Content for SAP Business One: technical notes and incident resolution.
Conclusion
SAP Business One cross-selling does not demand investment; it demands query discipline. All the information has been sitting in OINV and INV1 since day one of your implementation, waiting for someone to sit down and ask it the right question. Four queries and a monthly review convention are enough to turn a dead history into a list of opportunities with a name, a surname and an amount.
If your sales team is still working on intuition about what to offer alongside what, that job is costing margin every month. Start with the pairs query, validate twenty combinations with your sales manager, and measure basket size after ninety days. The result tends to surprise, and not always comfortably.
Want to go deeper? Inside the Círculo Estratégico I publish the full tutorials with queries ready to copy, the variants by industry, and the implementation detail that does not fit in a public article. Browse the rest of the SAP Business One series at hectorpincheira.com and tell me in the comments which product pair surprised you in your own database.




