Back to Tutorials
SQL & HANA•3 min read
VIDEO + GUIDE • OPEN

SAP Business One Query Basics: Query Generator, Query Manager & Your First Reports

Learn how SAP B1 stores data, how to build queries with the Query Generator, add date and customer parameters, and save reports in the Query Manager for your team.

SB1

SAP B1 Academy Editorial

Published • 3 min read

Save

Queries are the fastest way to get answers out of SAP Business One. With a few lines of SQL you can build reports that standard screens do not offer, power alerts, and fill in fields automatically with Formatted Search. This guide covers the basics shown in the video.

How SAP Business One Stores Data

Every document type in SAP B1 is stored in a header table and a lines table. Knowing the most common pairs gets you a long way:

DocumentHeaderLines
Sales OrderORDRRDR1
DeliveryODLNDLN1
A/R InvoiceOINVINV1
Purchase OrderOPORPOR1
A/P InvoiceOPCHPCH1
Journal EntryOJDTJDT1

Master data lives in tables such as OCRD (Business Partners), OITM (Items), and OITW (item quantities per warehouse). Header and lines are always joined on DocEntry.

To find the table and field behind any screen field, enable View → System Information and hover over the field. The status bar shows the table and column name.

Building a Query With the Query Generator

  1. Open Tools → Queries → Query Generator.
  2. Select the tables you need (for example OINV and OCRD); the generator suggests the joins.
  3. Double-click fields to add them to the Select list, and set conditions in the Where section.
  4. Click Execute to run it, then Save to store it in a query category.

For anything beyond simple joins, switch to the Query Wizard or open the query in edit mode and type the SQL directly.

Your First Report: Sales by Customer

SQL · SAP HANA / MS SQL Server
Sign in to copy
SELECT T0.CardCode, T0.CardName,
       COUNT(T0.DocEntry)          AS Invoices,
       SUM(T0.DocTotal - T0.VatSum) AS Net_Sales
FROM OINV T0
WHERE T0.CANCELED = 'N'
GROUP BY T0.CardCode, T0.CardName
ORDER BY Net_Sales DESC

Adding Parameters Users Can Fill In

SAP B1 queries support prompts. Use [%0], [%1] and so on, and the system asks the user for a value when the query runs. Referencing a real field (as below) gives the prompt the right type, such as a date picker:

SQL · SAP HANA / MS SQL Server
Sign in to copy
SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.DocTotal
FROM OINV T0
WHERE T0.DocDate >= [%0]
  AND T0.DocDate <= [%1]
  AND T0.CardCode = '[%2]'
ORDER BY T0.DocDate

Saving and Sharing Queries in the Query Manager

  • Save queries into categories (for example Sales Reports or Finance) via Tools → Queries → Query Manager.
  • Use authorization groups on categories so users only see the reports meant for them.
  • Saved queries can be exported to Excel from the result grid, scheduled as alerts, or attached to fields as Formatted Searches.

Good Habits From Day One

  • Always filter cancelled documents (CANCELED = 'N') in sales and purchasing reports.
  • Prefer table aliases (T0, T1) so queries stay readable as they grow.
  • Never use the Query Generator to UPDATE or DELETE data. Direct database changes are not supported by SAP and can break your company database.
Complete Consultant Toolkit

The Ultimate SAP B1 SQL & HANA Query Cookbook

Instant-download PDF playbook plus ready-to-import .sql files for Financials, Inventory Audit, Sales Margin, and Approval Alerts.

Get it ($29)