How many SKUs does your company manage in SAP Business One today? If you manage more than 500 products, you already know the problem without anyone having to explain it to you: not all of them carry the same weight in your business. A select few generate the majority of your revenue. Others take up space, working capital, and attention while returning almost nothing to you. And yet, you manage them all with the exact same intensity. You buy the same way, review the same way, and worry the same way.
The ABC inventory analysis SAP Business One it is the tool that breaks that tie. First, it separates the few products that drive your company from the many that barely sustain it. Furthermore, it allows you to redistribute scarce resources —time, capital, and managerial focus— to where it truly matters. And the best part: you don't need additional software, external consulting, or an expensive dashboard. SAP Business One, a well-built query, and the discipline to make different decisions for different groups are all you need.
In this tutorial you will learn, step by step, how to implement the ABC inventory analysis SAP Business One with ready-to-execute SQL queries for both SQL Server and SAP HANA. At the end, you will have a report that automatically classifies each product into category A, B, or C, and a practical framework to use that information in real purchasing, storage, and customer service decisions.

The problem: managing all products as if they were the same
In most industrial and distribution companies, inventory grows without a clear prioritization criterion. A buyer reviews 2,000 items with the same level of scrutiny. The operations manager chases stockouts of products that almost nobody buys. The finance department sees capital trapped in the warehouse without understanding which SKU is consuming it. Consequently, operations become sluggish, decisions slow down, and profitability erodes without anyone being able to point out exactly where.
However, there is a pattern that repeats itself in almost every company: approximately 20% of products generate 80% of value. This is the Pareto principle applied to inventory, and it forms the theoretical basis of ABC analysis. Therefore, if you can identify that 20%, you can free up enormous amounts of management time without losing control over what really matters.
What is ABC analysis and why does it change your management?
ABC analysis classifies your inventory into three categories based on their contribution to the total value:
- Category A: The 20% of products account for between 70% and 80% of the value consumed or sold. These are the items that require the most control, frequent cycle counts, and weekly inventory reviews.
- Category B: The 30% of products accounts for between 15% and 25% of the value. They require moderate monitoring, monthly reviews, and standard restocking policies.
- Category C: The remaining 50%, which accounts for less than 5% of the value. Simplified policies can be applied here: ample safety stock, annual counts, and bulk purchases.
This way, instead of treating 2,000 products with the same intensity, you give the team a clear focus: 400 SKUs that deserve daily attention, 600 that are reviewed periodically, and 1,000 that are managed by exception.
When to apply ABC inventory analysis SAP Business One
This report is especially useful when your company faces any of these scenarios. First, when the product catalog grows and it is no longer manageable to review everything manually. Second, when there are recurring stockouts in key products but an excess in others. In addition, when inventory auditing consumes too much time and you need to prioritize where to focus. Finally, when you are defining differentiated customer service policies, margins, or commercial conditions.
Technical prerequisites
Before executing the queries, make sure you comply with the following:
- Query Manager permissions in SAP Business One (General Authorizations > Reports > Query Manager).
- Read access to the OITM, OINV, INV1, and OITW tables.
- At least 12 months of recorded sales history in the system.
- Find out if your installation uses SQL Server or SAP HANA (both versions are included below).
Step 1: Define the classification criterion
Before writing SQL, there is a managerial decision: by what criteria to classify? There are three main options, and each tells a different story.
By consumption value (recommended): sums the total invoiced amount of each product over the last 12 months. It is the most widely used criterion because it directly reflects the economic contribution to the business. For example, a low-priced but high-turnover product may appear as A, while an expensive, low-turnover product may fall to B or C.
By quantity consumed: sort by units moved. Useful for companies where handling cost matters more than monetary value, such as mass distribution or intensive logistics.
By contributed margin: use the actual profitability (revenue minus cost). It is the most strategic, but requires you to have clean costs loaded in SAP Business One.
Step 2: Main query for the SAP Business One inventory ABC analysis
Here is the complete query that classifies all items sold in the last 12 months by consumption value. The query uses a Common Table Expression with window functions to calculate the running total, and a CASE that automatically assigns category A, B, or C.
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
-- ABC inventory analysis by consumed value - SQL Server
WITH ItemConsumption AS (
SELECT
T0.ItemCode AS Code,
T0.ItemName AS Name,
SUM(T1.Quantity) AS QuantitySold,
SUM(T1.LineTotal) AS ValueSold
FROM OITM T0 WITH (NOLOCK)
INNER JOIN INV1 T1 WITH (NOLOCK) ON T0.ItemCode = T1.ItemCode
INNER JOIN OINV T2 WITH (NOLOCK) ON T1.DocEntry = T2.DocEntry
WHERE T2.DocDate >= DATEADD(MONTH, -12, GETDATE())
AND T0.InvntItem = 'Y'
GROUP BY T0.ItemCode, T0.ItemName
),
GrandTotal AS (
SELECT SUM(ValueSold) AS TotalValue FROM ItemConsumption
),
Accumulated AS (
SELECT
c.Code,
c.Name,
c.QuantitySold,
c.ValueSold,
SUM(c.ValueSold) OVER (ORDER BY c.ValueSold DESC
ROWS UNBOUNDED PRECEDING) AS AccumulatedValue,
(SELECT TotalValue FROM GrandTotal) AS GrandTotal
FROM ItemConsumption c
)
SELECT
Code,
Name,
QuantitySold,
ValueSold,
AccumulatedValue,
CAST(AccumulatedValue * 100.0 / GrandTotal AS DECIMAL(10,2)) AS AccumulatedPct,
CASE
WHEN (AccumulatedValue * 100.0 / GrandTotal) <= 80 THEN 'A'
WHEN (AccumulatedValue * 100.0 / GrandTotal) <= 95 THEN 'B'
ELSE 'C'
END AS Category
FROM Accumulated
ORDER BY ValueSold DESC;
SAP HANA
-- ABC inventory analysis by consumed value - SAP HANA
WITH "ItemConsumption" AS (
SELECT
T0."ItemCode" AS "Code",
T0."ItemName" AS "Name",
SUM(T1."Quantity") AS "QuantitySold",
SUM(T1."LineTotal") AS "SalesValue"
FROM OITM T0
INNER JOIN INV1 T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN OINV T2 ON T1."DocEntry" = T2."DocEntry"
WHERE T2."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
AND T0."InvntItem" = 'Y'
GROUP BY T0."ItemCode", T0."ItemName"
),
"GrandTotal" AS (
SELECT SUM("SalesValue") AS "TotalValue" FROM "ItemConsumption"
),
"Accumulated" AS (
SELECT
c."Code",
c."Name",
c."QuantitySold",
c."SalesValue",
SUM(c."SalesValue") OVER (ORDER BY c."SalesValue" DESC
ROWS UNBOUNDED PRECEDING) AS "AccumulatedValue",
(SELECT "TotalValue" FROM "GrandTotal") AS "GrandTotal"
FROM "ItemConsumption" c
)
SELECT
"Code",
"Name",
"QuantitySold",
"SalesValue",
"AccumulatedValue",
CAST("AccumulatedValue" * 100.0 / "GrandTotal" AS DECIMAL(10,2)) AS "AccumulatedPercentage",
CASE
WHEN ("AccumulatedValue" * 100.0 / "GrandTotal") <= 80 THEN 'A'
WHEN ("AccumulatedValue" * 100.0 / "GrandTotal") <= 95 THEN 'B'
ELSE 'C'
END AS "Category"
FROM "Accumulated"
ORDER BY "SalesValue" DESC;
Step 3: How to interpret the results
When you run the query for the first time, pay attention to three key indicators that will reveal the health of your product portfolio. First, note how many SKUs fall into Category A. If there are too many (more than 25% of the total), the Pareto curve in your business is likely less pronounced, and you’ll need to recalibrate the thresholds. On the other hand, if there are very few (less than 10%), you have a high concentration of risk: a small number of products account for nearly all of your revenue.
Secondly, identify the Class C products with high stock. These are the main candidates for generating idle capital. As a result, they are the first opportunity to free up cash without affecting operations. Likewise, look at the Class A products with frequent stockouts: that is where you are losing more money than you think.

Step 4: ABC query enriched with current stock and coverage
ABC analysis becomes truly actionable when you cross-reference it with available stock today and projected coverage. For example, a class A product with only 5 days of stock is an immediate red flag for purchasing. Conversely, a class C product with 18 months of coverage is a clear sign of idle capital. Below is the expanded version of the query:
SQL Server
-- ABC Analysis with Current Inventory and Months of Coverage - SQL Server
WITH 12M_Consumption AS (
SELECT
T0.ItemCode,
T0.ItemName,
SUM(T1.LineTotal) AS SalesValue,
SUM(T1.Quantity) AS QuantitySold
FROM OITM T0 WITH (NOLOCK)
INNER JOIN INV1 T1 WITH (NOLOCK) ON T0.ItemCode = T1.ItemCode
INNER JOIN OINV T2 WITH (NOLOCK) ON T1.DocEntry = T2.DocEntry
WHERE T2.DocDate >= DATEADD(MONTH, -12, GETDATE())
AND T0.InvntItem = 'Y'
GROUP BY T0.ItemCode, T0.ItemName
),
Total AS (SELECT SUM(SalesValue) AS T FROM 12M_Sales),
Ranked AS (
SELECT
c.*,
SUM(c.SoldValue) OVER (ORDER BY c.SoldValue DESC
ROWS UNBOUNDED PRECEDING) AS Accum,
(SELECT T FROM Total) AS TG
FROM 12M_Sales c
)
SELECT
cl.ItemCode AS Code,
cl.ItemName AS Item,
cl.QuantitySold,
cl.SalesValue,
CAST(cl.Acum * 100.0 / cl.TG AS DECIMAL(10,2)) AS CumulativePercentage,
CASE
WHEN (cl.Acum * 100.0 / cl.TG) <= 80 THEN 'A'
WHEN (cl.Acum * 100.0 / cl.TG) <= 95 THEN 'B'
ELSE 'C'
END AS ClassABC,
ISNULL(oitm.OnHand, 0) AS ActualStock,
CAST(ISNULL(oitm.OnHand, 0) * ISNULL(oitm.AvgPrice, 0)
AS DECIMAL(15,2)) AS StockValue,
CASE
WHEN cl.QuantitySold > 0
THEN CAST(ISNULL(oitm.OnHand, 0) / (cl.QuantitySold / 12.0)
AS DECIMAL(10,1))
ELSE 999
END AS MonthsCoverage
FROM Classified cl
LEFT JOIN OITM oitm WITH (NOLOCK) ON cl.ItemCode = oitm.ItemCode
ORDER BY cl.SalesValue DESC;
SAP HANA
-- ABC Analysis with Current Inventory and Months of Coverage - SAP HANA
WITH "12M Consumption" AS (
SELECT
T0."ItemCode",
T0."ItemName",
SUM(T1."LineTotal") AS "SalesValue",
SUM(T1."Quantity") AS "QuantitySold"
FROM OITM T0
INNER JOIN INV1 T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN OINV T2 ON T1."DocEntry" = T2."DocEntry"
WHERE T2."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
AND T0."InvntItem" = 'Y'
GROUP BY T0."ItemCode", T0."ItemName"
),
"Total" AS (SELECT SUM("SalesValue") AS "T" FROM "12M_Sales"),
"Classified" AS (
SELECT
c.*,
SUM(c."SalesValue") OVER (ORDER BY c."SalesValue" DESC
ROWS UNBOUNDED PRECEDING) AS "Acum",
(SELECT "T" FROM "Total") AS "TG"
FROM "Consumo12M" c
)
SELECT
cl."ItemCode" AS "Code",
cl."ItemName" AS "Item",
cl."QuantitySold",
cl."SalesValue",
CAST(cl."Acum" * 100.0 / cl."TG" AS DECIMAL(10,2)) AS "PorcAcum",
CASE
WHEN (cl."Acum" * 100.0 / cl."TG") <= 80 THEN 'A'
WHEN (cl."Acum" * 100.0 / cl."TG") <= 95 THEN 'B'
ELSE 'C'
END AS "ClassABC",
IFNULL(oitm."OnHand", 0) AS "CurrentStock",
CAST(IFNULL(oitm."OnHand", 0) * IFNULL(oitm."AvgPrice", 0)
AS DECIMAL(15,2)) AS "StockValue",
CASE
WHEN cl."QuantitySold" > 0
THEN CAST(IFNULL(oitm."OnHand", 0) / (cl."QuantitySold" / 12.0)
AS DECIMAL(10,1))
ELSE 999
END AS "CoverageMonths"
FROM "Classified" cl
LEFT JOIN OITM oitm ON cl."ItemCode" = oitm."ItemCode"
ORDER BY cl."SalesValue" DESC;
Useful variations of ABC analysis
The base query is just the starting point. Below are some variations that can give you finer angles depending on the problem you want to solve.
ABC by warehouse: If your company operates with multiple warehouses, run the analysis for each one by replacing OITM.OnHand with the OITW table filtered by WhsCode. This way, a product can be A in one warehouse and C in another, and replenishment policies can adapt to that reality.
ABC by article group: Group the analysis by T0.ItmsGrpCod (JOIN with OITB) to identify entire families that require distinct strategic treatment. For example, an entire family may be a candidate for discontinuation if all of it falls into C with low turnover.
ABC by contribution margin: replace ValorVendido with (LineTotal – GrossBuyPr * Quantity). In this case, you will discover high-selling products with a low margin that deserve a price or supplier review.
ABC-XYZ Analysis: combine ABC (value) with XYZ (demand variability). Thus, an A-X is a critical and predictable product (focus on efficiency), while an A-Z is critical but erratic (focus on safety stock and forecasting). It is the next level of maturity in inventory management.
Best practices and common mistakes
Recalculate quarterly, not annually. Sales patterns change, new products rise quickly, and others lose traction. Therefore, a stagnant ABC classification becomes a fossil that no longer reflects operational reality.
Don't ignore the new C products. A product can fall into C because it is new, not because it is bad. As a result, always filter by creation date and handle a parallel analysis for SKUs launched in the last 6 months.
Filter internal sales and samples. Transfer invoices, free samples, or adjustments can distort the analysis. For example, add WHERE T2.DocType = ‘I’ and filter by specific series if your company uses separate numbering.
Do not mix product categories. Performing ABC analysis on a catalog that combines raw materials, finished products, and consumables creates noise. Consequently, segment first by type and then analyze each group independently.
Connect the ABC with concrete operational policies. Classifying without acting is an academic exercise. Therefore, define for each category: cycle count frequency, safety stock, purchasing review periodicity, and committed customer service level.
Official SAP resources
For further information, the following official resources are essential reading. First, the Official SAP Business One documentation contains the complete detail of the OITM, OINV, INV1, and OITW tables. In addition, the SAP Community portal offers active forums where functional engineers share query variations for specific scenarios. Likewise, the SAP Help Portal maintains the Query Manager documentation with commented examples.
Conclusion: From Data to Management Judgment
ABC analysis is not just another report in the SAP Business One menu. It is a different way of looking at your inventory, a tool that transforms a flat list of 2,000 products into three groups with differentiated treatment. And that, in practice, changes entire conversations within the company: the buyer stops reviewing everything and prioritizes the A items, the warehouse worker stops counting everything and focuses their time where the risk is highest, and the manager stops reviewing general reports and manages by exception.
The query you ran today can become a monthly Crystal Report, a Power BI dashboard based on the SAP database, or a direct input for your purchasing planning. However, its greatest power is not in the SQL, but in the decisions it enables: where to focus, what to stop reviewing, and how to redistribute scarce managerial resources.
The ABC inventory analysis SAP Business One It is one of those reports that separates companies that react from companies that anticipate. If your inventory is managed by intuition today, you have in these queries the starting point to start doing it by criteria.
Do you want to take your analysis to the next level? In the Strategic Circle Every week we publish ready-to-use queries for SAP Business One, KPI templates, and practical cases of data-driven business management. Subscribe to receive the complete repository and access to upcoming tutorials.




