SAP Business One query library
24 ready-made reports for sales, finance, purchasing and stock. Every query in SQL Server and HANA versions, free to copy into Query Manager.
Sales by customer for a date range
Net sales per customer (invoices minus credit notes) between two dates, highest first.
OINVORINTop 20 selling items
Best-selling items by sales value, with quantity sold, for a date range.
OINVINV1Sales by sales employee
Invoice count and net sales per sales employee for a period.
OINVOSLPMonthly sales trend
Net invoiced sales per month for the last 12 months.
OINVOpen sales orders with open quantity
Every open sales order line that still has quantity to deliver.
ORDRRDR1Customer aging report (AR aging)
Open invoice balance per customer in 0–30, 31–60, 61–90 and 90+ day buckets by due date.
OINVOverdue customer invoices
Open invoices past their due date, with days overdue and the customer phone number.
OINVOCRDCustomer balances
All customers with a non-zero account balance, with credit limit and phone.
OCRDIncoming payments by date
Customer payments received in a period, split into cash, transfer, check and card.
ORCTOutgoing payments by date
Vendor payments made in a period, split into cash, transfer, check and card.
OVPMG/L account transactions
Journal lines for one G/L account in a period, with running debit and credit.
OJDTJDT1OACTG/L account balances
Current balance of every postable G/L account, grouped by account level.
OACTOpen purchase orders by vendor
Open purchase order lines with quantity still to receive, grouped by vendor.
OPORPOR1Last purchase price per item
The most recent A/P invoice price, vendor and date for every item.
OPCHPCH1Vendor aging report (AP aging)
Open A/P invoice balance per vendor in due-date buckets.
OPCHStock by warehouse
On hand, committed, ordered and available quantity per item and warehouse.
OITWOITMItems below minimum stock
Inventory items whose on-hand quantity is below the minimum level set on the item.
OITMInventory value by warehouse
Stock quantity multiplied by the item average cost, totalled per warehouse.
OITWOITMSlow-moving items (no sales in 90 days)
Items with stock on hand that have not been invoiced in the last 90 days.
OITMINV1OINVBatches expiring in the next 60 days
Batch numbers with stock that expire within 60 days, per warehouse.
OBTNOBTQItems with no price in a price list
Sales items that have a zero price in the chosen price list.
OITMITM1OPLNBusiness partners missing a tax ID
Active customers and vendors with no federal tax ID (VAT / RUC / NIT / NTN).
OCRDInvoices created per user today
How many A/R invoices each SAP B1 user created today, with the total value.
OINVOUSRDocuments waiting for approval
Approval requests still pending, with the document type, originator and age in days.
OWDDOUSRNo query found. Ask us for it →
Three steps, no signup
Find the report
Search by name or table, or filter by Sales, Finance, Purchasing, Inventory and Admin.
Pick SQL or HANA
Every query comes in both versions, tested against the standard SAP Business One schema.
Paste into Query Manager
Tools → Queries → Query Generator → Execute, then save it in your query category.
Questions about this tool
Are these SAP Business One queries free to use?
Yes. Copy them into your SAP B1 Query Manager, change them and share them with your team.
Do the queries work on SAP B1 for HANA?
Yes. Each query page has a SQL Server tab and a HANA tab. The HANA version uses quoted column names and HANA functions such as ADD_DAYS and DAYS_BETWEEN.
What do [%0] and [%1] mean in a query?
They are SAP Business One parameters. When you run the query, B1 opens a selection window and asks for the value, such as a date range or an account code.
Can you add our custom fields (UDFs) to a query?
Yes. We extend queries with your user-defined fields and tables, or turn them into Crystal reports, dashboards or alerts.
Built for SAP Business One teams
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.