Why your SAP Business One sales report doesn't match: the header discount in INV1

The symptom

You build a sales report, a dashboard or a Power BI model from SAP Business One. The total is close to the SAP figure, but not equal. Accounting does not trust the dashboard, and they are right not to.

We hit this while testing the Kodenova SAP B1 dashboard on a live company. The first version summed invoice lines and returned 49,085.44. SAP’s own figure for the same period was 49,058.00. The 27.44 difference came from one thing: a discount typed in the invoice footer.

Why it happens

An A/R invoice stores values at two levels:

• INV1 (lines): LineTotal, GrssProfit, Quantity. LineTotal is after the row discount.
• OINV (header): DiscPrcnt and DiscSum for the document discount, plus DocTotal, VatSum and TotalExpns.

The footer discount is applied to the whole document, not written back into each line. So when you SUM(T1."LineTotal"), you add up values that are still before that discount. Most invoices have no footer discount, which is why the error is small and easy to miss.

The fix in SQL

Bring the header discount into the line query and apply it per document:

SELECT T0."DocEntry", T0."DocDate", T0."CardCode", T0."DiscPrcnt",
       T1."ItemCode",
       SUM(T1."LineTotal")  AS "Net",
       SUM(T1."GrssProfit") AS "GP"
FROM OINV T0
INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."CANCELED" = 'N' AND T0."DocDate" >= '20250101'
GROUP BY T0."DocEntry", T0."DocDate", T0."CardCode", T0."DiscPrcnt", T1."ItemCode"

Then, for each row:

• net after discount = Net − Net × DiscPrcnt / 100
• gross profit after discount = GP − Net × DiscPrcnt / 100

Run the same query on ORIN and RIN1 for credit notes and treat those values as negative.

We apply the percentage in code rather than in SQL because some environments, such as Service Layer SQLQueries with a restricted allow-list, reject expressions in the select list. If your environment allows arithmetic, you can do it directly in SQL.

How to check your number against SAP

Before you trust any report, reconcile it once with a header-level query. Net sales excluding VAT and freight is:

SELECT
 (SELECT SUM(T0."DocTotal" - T0."VatSum" - T0."TotalExpns")
    FROM OINV T0 WHERE T0."CANCELED" = 'N' AND T0."DocDate" >= '20250101')
 -
 (SELECT ISNULL(SUM(T0."DocTotal" - T0."VatSum" - T0."TotalExpns"), 0)
    FROM ORIN T0 WHERE T0."CANCELED" = 'N' AND T0."DocDate" >= '20250101')

On SAP HANA, use IFNULL instead of ISNULL. In our test this returned 49,058.00, exactly what the corrected line query gave.

Small differences can still appear if documents use rounding or down payments, because those also live on the header. If you see a gap, look at those fields next.

Checklist for any SAP B1 sales report

1. Filter CANCELED = 'N'.
2. Subtract credit notes (ORIN/RIN1).
3. Apply the header discount to both net sales and gross profit.
4. Use one currency field consistently (LineTotal for local currency).
5. Reconcile once against DocTotal − VatSum − TotalExpns before going live.

Where this matters most

Any tool that reads lines instead of headers is exposed: Excel exports, Crystal Reports, Power BI models and custom dashboards. The Kodenova SAP B1 dashboard applies the header discount automatically through its local connector, so its totals match the SAP figures above.

If your reports and SAP disagree and you cannot find why, send us the query. This kind of mismatch is usually found in one session.

Sources

• SAP Learning: Managing SQL Queries Using Service Layer API
• SAP Learning: Creating Queries (SAP Business One)

Related services

• SAP Business One development
• AI agents for ERP