SQL Queries to Analyze Production Costs in SAP Business One - featured image

Publications

SQL Queries to Analyze Production Costs in SAP Business One

Hector Pincheira

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

items

Do you really know what it costs to manufacture each product in your company? Because if you can't answer that question with precise data, you're making pricing and profitability decisions blind. Analyzing SAP Business One production costs with SQL queries lets you uncover variances, identify the components that drive up the cost of your orders and make evidence-based decisions. In this tutorial I'll also show you, step by step, how to build the most powerful queries to control your manufacturing costs.

SAP Business One production costs - industrial production line
Production cost control is the foundation of industrial profitability

Why You Need to Analyze Production Costs with SQL

SAP Business One records every inventory movement, every material consumption and every receipt of finished goods. However, the system's standard reports don't always give you the full picture you need. Building custom queries therefore lets you combine production order data, actual consumption and unit costs in a single analysis.

That way you can answer critical questions such as: what did this order actually cost me versus what was planned? Which component is generating the largest variances? What is my real unit cost per finished product? And these answers are the basis for negotiating with suppliers, adjusting sales prices and optimizing your bill of materials.

Prerequisites for Running These Queries

Before you start, first make sure you have the following configured in your SAP Business One environment:

  • Access to Query Manager or to SQL Server Management Studio / SAP HANA Studio, depending on your database.
  • Closed production orders with consumption and receipts recorded, so the actual cost data is complete.
  • A defined valuation method: these queries work with moving average cost and standard cost. That said, the results vary depending on the method you use.
  • Read permissions on the OWOR, WOR1, OITM, OITW, OIGN, IGN1 tables.

Key Tables of the Production Module in SAP Business One

To build effective queries you need to know the main tables. Below is the data map we'll use in this tutorial:

  • OWOR: Production order header. Contains the planned item, quantity, dates and status.
  • WOR1: Component lines of the order. This is where the planned and consumed materials sit, with their costs.
  • WOR4: Production stages and assigned resources.
  • OITM: Item master data, including average cost and standard cost.
  • OITW: Item data by warehouse, with location-specific costs.

For example, the main relationship is OWOR → WOR1, where each order has its components with planned quantities versus quantities actually consumed. As a result, the difference between the two is where cost variances are born.

Query 1: SAP Business One Production Costs — Planned vs Actual

This is the most important query for any operations manager. It shows you, first, how much you planned to spend on each production order and, second, how much you actually spent. That way you immediately identify the orders with cost overruns.

SQL Server

-- Planned vs actual cost by production order
SELECT
    T0.DocNum           AS 'Order No.',
    T0.ItemCode         AS 'Product Code',
    T0.ProdName         AS 'Description',
    T0.PlannedQty       AS 'Planned Qty',
    T0.CmpltQty         AS 'Completed Qty',
    ISNULL(SUM(T1.PlannedQty * T1.Price), 0)
                         AS 'Planned Cost',
    ISNULL(SUM(T1.IssuedQty * T1.Price), 0)
                         AS 'Actual Cost',
    ISNULL(SUM(T1.IssuedQty * T1.Price), 0)
    - ISNULL(SUM(T1.PlannedQty * T1.Price), 0)
                         AS 'Variance',
    CASE
        WHEN SUM(T1.PlannedQty * T1.Price) = 0 THEN 0
        ELSE ROUND(
            ((SUM(T1.IssuedQty * T1.Price) - SUM(T1.PlannedQty * T1.Price))
            / SUM(T1.PlannedQty * T1.Price)) * 100, 2)
    END                  AS '% Variance'
FROM OWOR T0 WITH (NOLOCK)
INNER JOIN WOR1 T1 WITH (NOLOCK) ON T0.DocEntry = T1.DocEntry
WHERE T0.Status = 'L'  -- Closed orders
  AND T0.PostDate >= DATEADD(MONTH, -3, GETDATE())
GROUP BY T0.DocNum, T0.ItemCode, T0.ProdName,
         T0.PlannedQty, T0.CmpltQty
ORDER BY 'Variance' DESC

SAP HANA

-- Planned vs actual cost by production order
SELECT
    T0."DocNum"           AS "Order No.",
    T0."ItemCode"         AS "Product Code",
    T0."ProdName"         AS "Description",
    T0."PlannedQty"       AS "Planned Qty",
    T0."CmpltQty"         AS "Completed Qty",
    IFNULL(SUM(T1."PlannedQty" * T1."Price"), 0)
                         AS "Planned Cost",
    IFNULL(SUM(T1."IssuedQty" * T1."Price"), 0)
                         AS "Actual Cost",
    IFNULL(SUM(T1."IssuedQty" * T1."Price"), 0)
    - IFNULL(SUM(T1."PlannedQty" * T1."Price"), 0)
                         AS "Variance",
    CASE
        WHEN SUM(T1."PlannedQty" * T1."Price") = 0 THEN 0
        ELSE ROUND(
            ((SUM(T1."IssuedQty" * T1."Price") - SUM(T1."PlannedQty" * T1."Price"))
            / SUM(T1."PlannedQty" * T1."Price")) * 100, 2)
    END                  AS "% Variance"
FROM "OWOR" T0
INNER JOIN "WOR1" T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."Status" = 'L'
  AND T0."PostDate" >= ADD_MONTHS(CURRENT_DATE, -3)
GROUP BY T0."DocNum", T0."ItemCode", T0."ProdName",
         T0."PlannedQty", T0."CmpltQty"
ORDER BY "Variance" DESC

This query therefore compares the planned quantity of each component multiplied by its price against the quantity actually issued. The variance percentage tells you how far off plan you are. And if an order shows more than 10% variance, that's a red flag you need to investigate immediately.

Query 2: Cost Breakdown by Component

When you spot a variance on an order, you need to dig deeper. This query shows you the detail component by component, revealing exactly which material caused the overrun. It also includes the difference between planned and consumed on each line.

SAP Business One production costs - data analysis and reporting
Detailed component-level analysis reveals where cost overruns hide

SQL Server

-- Cost breakdown by component in a production order
SELECT
    T0.DocNum            AS 'Order No.',
    T0.ItemCode          AS 'Finished Product',
    T1.ItemCode          AS 'Component Code',
    T2.ItemName          AS 'Component Name',
    T1.PlannedQty        AS 'Planned Qty',
    T1.IssuedQty         AS 'Consumed Qty',
    T1.Price              AS 'Unit Price',
    ROUND(T1.PlannedQty * T1.Price, 2)
                          AS 'Planned Cost',
    ROUND(T1.IssuedQty * T1.Price, 2)
                          AS 'Actual Cost',
    ROUND((T1.IssuedQty - T1.PlannedQty) * T1.Price, 2)
                          AS 'Difference'
FROM OWOR T0 WITH (NOLOCK)
INNER JOIN WOR1 T1 WITH (NOLOCK) ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 WITH (NOLOCK) ON T1.ItemCode = T2.ItemCode
WHERE T0.DocNum = [%0]  -- Parameter: order number
ORDER BY 'Difference' DESC

SAP HANA

-- Cost breakdown by component in a production order
SELECT
    T0."DocNum"            AS "Order No.",
    T0."ItemCode"          AS "Finished Product",
    T1."ItemCode"          AS "Component Code",
    T2."ItemName"          AS "Component Name",
    T1."PlannedQty"        AS "Planned Qty",
    T1."IssuedQty"         AS "Consumed Qty",
    T1."Price"              AS "Unit Price",
    ROUND(T1."PlannedQty" * T1."Price", 2)
                          AS "Planned Cost",
    ROUND(T1."IssuedQty" * T1."Price", 2)
                          AS "Actual Cost",
    ROUND((T1."IssuedQty" - T1."PlannedQty") * T1."Price", 2)
                          AS "Difference"
FROM "OWOR" T0
INNER JOIN "WOR1" T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN "OITM" T2 ON T1."ItemCode" = T2."ItemCode"
WHERE T0."DocNum" = '[%0]'
ORDER BY "Difference" DESC

With this level of detail you can identify whether the overrun comes from a specific component you consumed in greater quantity or whether the material's price changed. Next, I'll show you how to calculate the real unit cost of each finished product.

Query 3: Real Unit Cost by Finished Product

This query is essential for your pricing strategy. It calculates the real unit cost of each manufactured product by dividing the total component cost by the quantity actually produced. That way you can compare your actual cost against your sales price and know your true margin.

SQL Server

-- Real unit cost by finished product
SELECT
    T0.ItemCode          AS 'Product',
    T0.ProdName          AS 'Description',
    COUNT(DISTINCT T0.DocEntry)
                          AS 'No. of Orders',
    SUM(T0.CmpltQty)      AS 'Total Produced',
    ROUND(SUM(T1.IssuedQty * T1.Price), 2)
                          AS 'Total Material Cost',
    CASE
        WHEN SUM(T0.CmpltQty) = 0 THEN 0
        ELSE ROUND(SUM(T1.IssuedQty * T1.Price)
             / SUM(T0.CmpltQty), 2)
    END                   AS 'Real Unit Cost',
    T3.AvgPrice          AS 'System Average Cost'
FROM OWOR T0 WITH (NOLOCK)
INNER JOIN WOR1 T1 WITH (NOLOCK) ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T3 WITH (NOLOCK) ON T0.ItemCode = T3.ItemCode
WHERE T0.Status = 'L'
  AND T0.PostDate >= DATEADD(MONTH, -6, GETDATE())
GROUP BY T0.ItemCode, T0.ProdName, T3.AvgPrice
HAVING SUM(T0.CmpltQty) > 0
ORDER BY 'Real Unit Cost' DESC

SAP HANA

-- Real unit cost by finished product
SELECT
    T0."ItemCode"          AS "Product",
    T0."ProdName"          AS "Description",
    COUNT(DISTINCT T0."DocEntry")
                          AS "No. of Orders",
    SUM(T0."CmpltQty")      AS "Total Produced",
    ROUND(SUM(T1."IssuedQty" * T1."Price"), 2)
                          AS "Total Material Cost",
    CASE
        WHEN SUM(T0."CmpltQty") = 0 THEN 0
        ELSE ROUND(SUM(T1."IssuedQty" * T1."Price")
             / SUM(T0."CmpltQty"), 2)
    END                   AS "Real Unit Cost",
    T3."AvgPrice"          AS "System Average Cost"
FROM "OWOR" T0
INNER JOIN "WOR1" T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN "OITM" T3 ON T0."ItemCode" = T3."ItemCode"
WHERE T0."Status" = 'L'
  AND T0."PostDate" >= ADD_MONTHS(CURRENT_DATE, -6)
GROUP BY T0."ItemCode", T0."ProdName", T3."AvgPrice"
HAVING SUM(T0."CmpltQty") > 0
ORDER BY "Real Unit Cost" DESC

Notice how we compare the unit cost calculated from the orders against the average cost the system keeps in OITM. If there is a significant difference between the two values, you need to check whether inventory movements are distorting the system average.

Query 4: Top 10 Components with the Largest Cost Variance

Not all components weigh the same in your cost structure. This query reveals the 10 materials hitting your production margins hardest. It also lets you focus your supplier negotiations on the inputs that actually move the needle.

SAP Business One production costs - cost analysis dashboard
Identify the components with the biggest impact on your manufacturing costs

SQL Server

-- Top 10 componentes con mayor desviación acumulada
SELECT TOP 10
    T1.ItemCode          AS 'Componente',
    T2.ItemName          AS 'Descripción',
    COUNT(DISTINCT T0.DocEntry)
                          AS 'Órdenes Afectadas',
    ROUND(SUM((T1.IssuedQty - T1.PlannedQty) * T1.Price), 2)
                          AS 'Desviación Total $',
    ROUND(AVG(
        CASE WHEN T1.PlannedQty * T1.Price > 0
        THEN ((T1.IssuedQty - T1.PlannedQty) * T1.Price)
             / (T1.PlannedQty * T1.Price) * 100
        ELSE 0 END), 2)
                          AS '% Desv. Promedio'
FROM OWOR T0
INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.Status = 'L'
  AND T0.PostDate >= DATEADD(MONTH, -6, GETDATE())
GROUP BY T1.ItemCode, T2.ItemName
HAVING SUM((T1.IssuedQty - T1.PlannedQty) * T1.Price) > 0
ORDER BY 'Desviación Total $' DESC

SAP HANA

-- Top 10 components with the largest accumulated variance
SELECT
    T1."ItemCode"          AS "Component",
    T2."ItemName"          AS "Description",
    COUNT(DISTINCT T0."DocEntry")
                          AS "Affected Orders",
    ROUND(SUM((T1."IssuedQty" - T1."PlannedQty") * T1."Price"), 2)
                          AS "Total Variance $",
    ROUND(AVG(
        CASE WHEN T1."PlannedQty" * T1."Price" > 0
        THEN ((T1."IssuedQty" - T1."PlannedQty") * T1."Price")
             / (T1."PlannedQty" * T1."Price") * 100
        ELSE 0 END), 2)
                          AS "% Avg. Variance"
FROM "OWOR" T0
INNER JOIN "WOR1" T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN "OITM" T2 ON T1."ItemCode" = T2."ItemCode"
WHERE T0."Status" = 'L'
  AND T0."PostDate" >= ADD_MONTHS(CURRENT_DATE, -6)
GROUP BY T1."ItemCode", T2."ItemName"
HAVING SUM((T1."IssuedQty" - T1."PlannedQty") * T1."Price") > 0
ORDER BY "Total Variance $" DESC
LIMIT 10

Notice the key differences between the two versions. In SQL Server we use SELECT TOP 10 at the beginning, while in SAP HANA we use LIMIT 10 at the end. On top of that, in HANA every column and table name goes in double quotes, and you don't use WITH (NOLOCK). As a result, both queries deliver exactly the same information, adapted to each database engine.

Variations and Advanced Use Cases

The queries above are the foundation, but you can adapt them to the specific needs of your operation. Here are some variations that add immediate value:

  • Filter by item group: add AND T0.ItemCode IN (SELECT ItemCode FROM OITM WHERE ItmsGrpCod = X) to analyze a single product family.
  • Analysis by warehouse: include the field T0.Warehouse in the SELECT and GROUP BY to see costs by plant or production center.
  • Monthly trend: use MONTH(T0.PostDate) and YEAR(T0.PostDate) to group and detect whether variances are increasing or decreasing over time.
  • Include resources (labor): join the WOR4 table to bring resource and production stage costs into the overall analysis.

Each variation therefore answers a different business question. That said, I recommend starting with the base queries and adding complexity as you go, depending on what you need to investigate.

Best Practices and Common Mistakes

After implementing these queries in several companies, I've identified the most common mistakes you should avoid. First, don't analyze orders that are still open or in process, because consumption isn't complete and will give you a distorted picture. Always filter with Status = 'L' (closed).

Second, be careful with partially completed orders. If you planned to produce 100 units but only completed 80, the cost variance may look negative when in fact you simply produced less. Here are the best practices:

  • Run the analysis monthly as part of your management close, not only when there are obvious problems.
  • Compare against the same period last year to rule out seasonality in material prices.
  • Cross-check with purchasing data to verify whether variances come from price changes or from excess consumption.
  • Document acceptable thresholds: define that anything above 5% variance triggers a review of the bill of materials.
  • Involve the production team in reviewing the results, because they know the operational causes behind the numbers.

Official SAP Resources

To go deeper into the production and costing module of SAP Business One, I recommend these official resources:

  • SAP Help Portal: official documentation on production cost calculation in SAP Business One.
  • SAP Community: an active forum where consultants share queries and solutions for manufacturing.
  • SAP Learning Hub: courses on production management and costing in SAP B1.

Conclusion: Take Control of Your Manufacturing Costs

The queries you've just learned are not simple technical reports. They are strategic decision tools that connect directly to your company's profitability. Every time you run a production cost analysis in SAP Business One, you're building a competitive advantage based on real data, not assumptions.

My recommendation is to start by implementing Query 1 this very week. Then schedule it to run monthly and share the results with your production and finance teams. That way you turn information into action and position IT as a strategic business partner, not just technical support.

Want access to more advanced SAP Business One tutorials, including exclusive queries and real implementation cases? Join the Círculo Estratégico and take your SAP B1 mastery to the next level.

, ROUND(AVG( CASE WHEN T1.PlannedQty * T1.Price > 0 THEN ((T1.IssuedQty - T1.PlannedQty) * T1.Price) / (T1.PlannedQty * T1.Price) * 100 ELSE 0 END), 2) AS '% Avg. Variance' FROM OWOR T0 WITH (NOLOCK) INNER JOIN WOR1 T1 WITH (NOLOCK) ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 WITH (NOLOCK) ON T1.ItemCode = T2.ItemCode WHERE T0.Status = 'L' AND T0.PostDate >= DATEADD(MONTH, -6, GETDATE()) GROUP BY T1.ItemCode, T2.ItemName HAVING SUM((T1.IssuedQty - T1.PlannedQty) * T1.Price) > 0 ORDER BY 'Total Variance

SAP HANA

Notice the key differences between the two versions. In SQL Server we use SELECT TOP 10 at the beginning, while in SAP HANA we use LIMIT 10 at the end. On top of that, in HANA every column and table name goes in double quotes, and you don't use WITH (NOLOCK). As a result, both queries deliver exactly the same information, adapted to each database engine.

Variations and Advanced Use Cases

The queries above are the foundation, but you can adapt them to the specific needs of your operation. Here are some variations that add immediate value:

  • Filter by item group: add AND T0.ItemCode IN (SELECT ItemCode FROM OITM WHERE ItmsGrpCod = X) to analyze a single product family.
  • Analysis by warehouse: include the field T0.Warehouse in the SELECT and GROUP BY to see costs by plant or production center.
  • Monthly trend: use MONTH(T0.PostDate) and YEAR(T0.PostDate) to group and detect whether variances are increasing or decreasing over time.
  • Include resources (labor): join the WOR4 table to bring resource and production stage costs into the overall analysis.

Each variation therefore answers a different business question. That said, I recommend starting with the base queries and adding complexity as you go, depending on what you need to investigate.

Best Practices and Common Mistakes

After implementing these queries in several companies, I've identified the most common mistakes you should avoid. First, don't analyze orders that are still open or in process, because consumption isn't complete and will give you a distorted picture. Always filter with Status = 'L' (closed).

Second, be careful with partially completed orders. If you planned to produce 100 units but only completed 80, the cost variance may look negative when in fact you simply produced less. Here are the best practices:

  • Run the analysis monthly as part of your management close, not only when there are obvious problems.
  • Compare against the same period last year to rule out seasonality in material prices.
  • Cross-check with purchasing data to verify whether variances come from price changes or from excess consumption.
  • Document acceptable thresholds: define that anything above 5% variance triggers a review of the bill of materials.
  • Involve the production team in reviewing the results, because they know the operational causes behind the numbers.

Official SAP Resources

To go deeper into the production and costing module of SAP Business One, I recommend these official resources:

  • SAP Help Portal: official documentation on production cost calculation in SAP Business One.
  • SAP Community: an active forum where consultants share queries and solutions for manufacturing.
  • SAP Learning Hub: courses on production management and costing in SAP B1.

Conclusion: Take Control of Your Manufacturing Costs

The queries you've just learned are not simple technical reports. They are strategic decision tools that connect directly to your company's profitability. Every time you run a production cost analysis in SAP Business One, you're building a competitive advantage based on real data, not assumptions.

My recommendation is to start by implementing Query 1 this very week. Then schedule it to run monthly and share the results with your production and finance teams. That way you turn information into action and position IT as a strategic business partner, not just technical support.

Want access to more advanced SAP Business One tutorials, including exclusive queries and real implementation cases? Join the Círculo Estratégico and take your SAP B1 mastery to the next level.

DESC

SAP HANA

Notice the key differences between the two versions. In SQL Server we use SELECT TOP 10 at the beginning, while in SAP HANA we use LIMIT 10 at the end. On top of that, in HANA every column and table name goes in double quotes, and you don't use WITH (NOLOCK). As a result, both queries deliver exactly the same information, adapted to each database engine.

Variations and Advanced Use Cases

The queries above are the foundation, but you can adapt them to the specific needs of your operation. Here are some variations that add immediate value:

  • Filter by item group: add AND T0.ItemCode IN (SELECT ItemCode FROM OITM WHERE ItmsGrpCod = X) to analyze a single product family.
  • Analysis by warehouse: include the field T0.Warehouse in the SELECT and GROUP BY to see costs by plant or production center.
  • Monthly trend: use MONTH(T0.PostDate) and YEAR(T0.PostDate) to group and detect whether variances are increasing or decreasing over time.
  • Include resources (labor): join the WOR4 table to bring resource and production stage costs into the overall analysis.

Each variation therefore answers a different business question. That said, I recommend starting with the base queries and adding complexity as you go, depending on what you need to investigate.

Best Practices and Common Mistakes

After implementing these queries in several companies, I've identified the most common mistakes you should avoid. First, don't analyze orders that are still open or in process, because consumption isn't complete and will give you a distorted picture. Always filter with Status = 'L' (closed).

Second, be careful with partially completed orders. If you planned to produce 100 units but only completed 80, the cost variance may look negative when in fact you simply produced less. Here are the best practices:

  • Run the analysis monthly as part of your management close, not only when there are obvious problems.
  • Compare against the same period last year to rule out seasonality in material prices.
  • Cross-check with purchasing data to verify whether variances come from price changes or from excess consumption.
  • Document acceptable thresholds: define that anything above 5% variance triggers a review of the bill of materials.
  • Involve the production team in reviewing the results, because they know the operational causes behind the numbers.

Official SAP Resources

To go deeper into the production and costing module of SAP Business One, I recommend these official resources:

  • SAP Help Portal: official documentation on production cost calculation in SAP Business One.
  • SAP Community: an active forum where consultants share queries and solutions for manufacturing.
  • SAP Learning Hub: courses on production management and costing in SAP B1.

Conclusion: Take Control of Your Manufacturing Costs

The queries you've just learned are not simple technical reports. They are strategic decision tools that connect directly to your company's profitability. Every time you run a production cost analysis in SAP Business One, you're building a competitive advantage based on real data, not assumptions.

My recommendation is to start by implementing Query 1 this very week. Then schedule it to run monthly and share the results with your production and finance teams. That way you turn information into action and position IT as a strategic business partner, not just technical support.

Want access to more advanced SAP Business One tutorials, including exclusive queries and real implementation cases? Join the Círculo Estratégico and take your SAP B1 mastery to the next level.

Most Read

QlikSense Hierarchy: parent-child chart of accounts expanded by levels
QlikSense Hierarchy: the Chart of Accounts That Doesn't Add Up
QlikSense Sum TOTAL: share of each family over the total
QlikSense Sum TOTAL: the Percentage That Changes When You Filter
SAP Business One Inventory Revaluation - featured image of the tutorial
SAP Business One Inventory Revaluation: Fix Wrongly Loaded Costs
Empty chamber with wooden benches: the counterweight that required being present to be used
The Veto You Had to Say in Person: Your Decisions With No Counterweight
SAP Business One customizations - featured image of the pre-upgrade audit tutorial
SAP Business One Customizations: Pre-Upgrade Audit
SAP Business One Customer Concentration - featured image of the tutorial
SAP Business One Customer Concentration: Measure Your Risk with SQL

RELATED ARTICLES