Start a project

Item sales by warehouse

Quantity and net sales per item and warehouse from A/R invoices in a date range.

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

Tables: OINV, INV1Use: Branch comparison, stock placement and transfer planning.Parameters: [%0] = from date, [%1] = to date

Tool

SELECT T1.WhsCode, T1.ItemCode, T1.Dscription AS ItemName,
  SUM(T1.Quantity) AS QtySold, SUM(T1.LineTotal * (1 - T0.DiscPrcnt / 100.0)) AS NetSales
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 T1.WhsCode, T1.ItemCode, T1.Dscription
ORDER BY T1.WhsCode, NetSales DESC
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

What this query answers

Operations wants to know which items sell from which warehouse, to decide what to stock in each branch and what to transfer. This returns the quantity sold and the net sales per warehouse and item from A/R invoices in a date range, after the header discount, with the best sellers of each warehouse first.

Sample output

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

WhsCodeItemCodeItemNameQtySoldNetSales
01A00001Laser Printer 14208625800
01A00003Toner Cartridge Color41012300
02A00001Laser Printer 1420319300
02A00002Inkjet Printer 600223960

SQL Server vs SAP HANA

Only quoting changes: T1."WhsCode", T1."Dscription" and the aliases go in double quotes. GROUP BY and ORDER BY with an alias of an aggregate (NetSales) work the same on HANA.

Common errors and fixes

  • Credit memos are not deducted: This reads invoices only. Add a UNION ALL over ORIN and RIN1 with negative quantity and value, as in the sales by item group query, if returns are frequent.
  • Warehouse is empty on some rows: Service invoices and non-inventory lines can have no warehouse. Add T1.WhsCode IS NOT NULL to drop them.

Need this report built into SAP Business One?

A saved query answers the question once. We do custom 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

Stock by warehouseOn hand, committed, ordered and available quantity per item and warehouse.Top 20 selling itemsBest-selling items by sales value, with quantity sold, for a date range.Sales by item groupNet sales and quantity per item group (invoices minus credit memos, after header discount) for a date range.Inventory value by warehouseStock quantity multiplied by the item average cost, totalled per warehouse.Sales by customer for a date rangeNet sales per customer (invoices minus credit notes) between two dates, highest first.Sales by sales employeeInvoice count and net sales per sales employee for a period.

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 item sales by warehouse 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