Start a project

Gross profit by invoice

Sales, gross profit and margin percent per A/R invoice for a date range, from the gross profit SAP B1 stores on each row.

By Zain Abbas, Founder & Lead SAP Business One / AI Engineer · Last updated

Tables: OINV, INV1Use: Margin checks, pricing reviews and sales commission on profit.Parameters: [%0] = from date, [%1] = to date

Tool

SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName,
  SUM(T1.LineTotal) AS Sales, SUM(T1.GrssProfit) AS GrossProfit,
  CASE WHEN SUM(T1.LineTotal) = 0 THEN 0
    ELSE ROUND(SUM(T1.GrssProfit) * 100.0 / SUM(T1.LineTotal), 2) END AS MarginPct
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.CANCELED = 'N' AND T0.DocDate BETWEEN '[%0]' AND '[%1]'
GROUP BY T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName
ORDER BY T0.DocDate, T0.DocNum
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

What this query answers

Finance wants to see the profit on each customer invoice, not just the revenue. This returns the net sales, the gross profit and the margin percent per A/R invoice in a date range. The gross profit comes from the GrssProfit value that SAP Business One stores on every invoice row, so it follows the company's gross profit settings.

Sample output

Illustrative demo values. Your columns are the same; the numbers come from your company.

DocNumDocDateCardCodeCardNameSalesGrossProfitMarginPct
14502026-10-02C20000Acme Trading12400372030
14512026-10-02C30000Northwind Retail5300106020
14522026-10-03C40000Blue Harbor Foods860-40-4.65
14532026-10-04C20000Acme Trading215077436

SQL Server vs SAP HANA

CASE and ROUND work the same on HANA. Quote T1."GrssProfit" exactly: the column name has a double s and no o, while the header field on OINV is spelled GrosProfit.

Common errors and fixes

  • Margin differs from the gross profit shown on the document: Sales here is the sum of the row totals, which do not include the header discount. If the invoice has a header discount, compare with the header field OINV.GrosProfit instead.
  • Negative margin on a normal sale: Row gross profit depends on the item cost at posting time and on the base price your company uses for gross profit. Check the item cost before you change prices.
  • Division by zero: The CASE returns 0 when the invoice total is zero, for example a fully free invoice. Keep it if you add more ratio columns.

Need this report built into SAP Business One?

A saved query answers the question once. We do SAP Business One development: add-ons, integrations and reports, from the database to the screen. Or hire a remote SAP Business One developer for the whole job, fixed price per phase.

Sales

Related queries

Sales by customer for a date rangeNet sales per customer (invoices minus credit notes) between two dates, highest first.Sales by item groupNet sales and quantity per item group (invoices minus credit memos, after header discount) for a date range.Top 20 selling itemsBest-selling items by sales value, with quantity sold, for a date range.Last purchase price per itemThe most recent A/P invoice price, vendor and date for every item.Sales by sales employeeInvoice count and net sales per sales employee for a period.Monthly sales trendNet invoiced sales per month for the last 12 months.

Table reference: OINV table (A/R Invoice (header)) · INV1 table (A/R Invoice (lines))

How it works

Three steps, no signup

1

Copy the right version

Use the SQL Server tab for MS SQL databases and the HANA tab for SAP B1 on HANA.

2

Open Query Generator

In SAP Business One go to Tools → Queries → Query Generator, paste and click Execute.

3

Save and share

Click Save, pick a category and give the query a name. Your team can run it from Query Manager.

FAQ

Questions about this tool

How do I run the gross profit by invoice query in SAP Business One?

Copy the version that matches your database, open Tools → Queries → Query Generator, paste it in the SQL box and click Execute. Save it to reuse it later.

Which tables does this query use?

It reads OINV, INV1 from the standard SAP Business One schema. No user-defined fields are needed.

What should I enter when the selection window opens?

This query uses parameters: [%0] = from date, [%1] = to date. SAP B1 shows a selection window for each one when you run it.

Can this be turned into a dashboard or alert?

Yes. We turn queries into Crystal reports, Excel reports that refresh themselves, SAP B1 alerts or WhatsApp messages for managers.

More free tools

Built for SAP Business One teams

ERP selector: which ERP suits you?Answer 15 questions and compare ERPs on processes, size, budget, hosting and your country’s rules, with every claim sourced.SAP B1 dashboard builderDrag and drop SAP B1 dashboards for Sales, Purchasing, Inventory, Receivables and Finance with click-to-filter. Works offline, no developer needed.SQL to HANA query converterPaste a SQL Server query and get the SAP HANA version, with every change explained.SAP B1 table finderSearch any table: what it stores, its line tables and its key fields.

Need this inside SAP Business One?

We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.

Start a project Chat on WhatsApp