Start a project

Sales by item group

Net sales and quantity per item group (invoices minus credit memos, after header discount) for a date range.

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

Tables: OINV, INV1, ORIN, RIN1, OITM, OITBUse: Category reviews, margin planning and purchasing focus.Parameters: [%0] = from date, [%1] = to date

Tool

SELECT X.ItmsGrpNam, SUM(X.Qty) AS Quantity, SUM(X.NetSales) AS NetSales
FROM (
  SELECT T3.ItmsGrpNam, T1.Quantity AS Qty, T1.LineTotal * (1 - T0.DiscPrcnt / 100.0) AS NetSales
  FROM OINV T0
  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
  INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
  INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
  WHERE T0.CANCELED = 'N' AND T0.DocDate BETWEEN '[%0]' AND '[%1]'
  UNION ALL
  SELECT T3.ItmsGrpNam, -T1.Quantity, -(T1.LineTotal * (1 - T0.DiscPrcnt / 100.0))
  FROM ORIN T0
  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
  INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
  INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
  WHERE T0.CANCELED = 'N' AND T0.DocDate BETWEEN '[%0]' AND '[%1]'
) X
GROUP BY X.ItmsGrpNam
ORDER BY NetSales DESC
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →

What this query answers

The sales manager wants to see which product categories carry the business this quarter. This returns one row per item group with the net quantity and net sales after the invoice header discount, with credit memos subtracted, so a group that sold a lot but took many returns does not look better than it is.

Sample output

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

ItmsGrpNamQuantityNetSales
Printers41296400
Consumables238041250.5
Accessories90518730
Services646400

SQL Server vs SAP HANA

The UNION ALL and the joins stay the same. HANA needs every column and alias in double quotes, for example T3."ItmsGrpNam" and AS "NetSales", and the decimal 100.0 keeps the discount division from turning into integer math.

Common errors and fixes

  • The total is higher than the sales analysis report: LineTotal is before the header discount. The query multiplies it by (1 - DiscPrcnt / 100) on both halves; keep that factor if you change the select list.
  • Service invoices are missing: Service-type invoices have no ItemCode, so the inner join to OITM drops them. They have no item group, which is why they cannot be grouped here.
  • Cancelled invoices change the totals: Keep CANCELED = 'N' on both halves. The cancellation document carries CANCELED = 'C' and is excluded the same way.

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 SAP Business One developer for the whole job, fixed price per phase.

Sales

Related queries

Top 20 selling itemsBest-selling items by sales value, with quantity sold, for a date range.Sales by customer for a date rangeNet sales per customer (invoices minus credit notes) between two dates, highest first.Item sales by warehouseQuantity and net sales per item and warehouse from A/R invoices in a date range.Gross profit by invoiceSales, gross profit and margin percent per A/R invoice for a date range, from the gross profit SAP B1 stores on each row.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)) · ORIN table (A/R Credit Memo (header)) · RIN1 table (A/R Credit Memo (lines)) · OITM table (Items (item master data)) · OITB table (Item Groups)

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 sales by item group 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, ORIN, RIN1, OITM, OITB 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