Tools for Creating Queries in SAP Business One
For pulling data out of SAP Business One, you need to generate a query. A query is a user-defined report that directly reads the required tables from the database and displays them formatted using the Structured Query Language (SQL). There are two different tools in SAP Business One to create the necessary SQL statements -- the Query Wizard and the Query Generator.
Query Wizard: Does NOT require SQL knowledge. Guides you step-by-step through the process of creating a query. The system generates the SQL statements in the background and keeps them permanently hidden.
Query Generator: DOES require SQL knowledge. Has a user-friendly interface for creating an SQL statement. The system displays the SQL commands so you can edit them directly.
Where to Find eLearning Materials on the Query Wizard and Query Generator
Free eLearning is available on the Query Wizard and the Query Generator through the SAP Business One Academy.
Go To Implementation and Support, Customization Tools, and see the material available for Queries (9.0).
Tips on Developing SQL Queries in SAP Business One
Here are our top 3 tips on developing SQL queries in SAP Business One.
- Make sure the users responsible for writing queries have the correct authorizations.
- The most important thing to know about SQL queries in SAP Business One is that you can only run SELECT type SQL statements in SAP. You are not allowed to run any other kinds of SQL (i.e. INSERT, UPDATE, DELETE, CREATE, ALTER, etc). Running any of these in SAP Business One will cause SAP not to provide support for your system.
- Know how to identify the tables to pull data from. Two places can help us find that out, system information and the SAP database reference. To view the system information, go to view > system information from the menu bar. This will display system information in the message bar at the bottom of the business one window. The table name and column name will be on the right of the system information. The SAP database reference is typically found on your server.
Example Queries for SAP Business One
Here are some sample, example queries used in SAP Business One to execute data on customers, orders and items.
-- Example 1 --
-- Open Sales orders by customer --
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal]
FROM ORDR T0
WHERE T0.[DocStatus] ='O'
ORDER BY T0.[CardCode]
--To allow a user to select customer or leave it blank use
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal]
FROM ORDR T0
WHERE T0.[DocStatus] ='O' AND T0.[CardName] Like '%%[%0]%%'
ORDER BY T0.[CardCode], T0.[CardName]
--Example 2 --
--Open Sales Orders grouped by customer
SELECT T0.[CardCode], T0.[CardName], SUM(T0.[DocTotal])
FROM ORDR T0
WHERE T0.[DocStatus] ='O'
GROUP BY T0.[CardCode], T0.[CardName]
--Example 3--
--Open Sales Orders by customer group
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal], T2.[GroupName]
FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode
ORDER BY T2.[GroupName]
-- Example 4 --
-- Open Sales order lines by customer --
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T1.[ItemCode],
T1.[Dscription], T1.[Quantity], T1.[OpenQty], T1.[Price], T1.[LineTotal]
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocStatus] = 'O' AND T1.[LineStatus] ='O'
AND T0.[CardName] Like '%%[%0]%%'
AND T0.[DocDate] >=[%1]
AND T0.[DocDate] <=[%2]
--Example 5 --
-- Open Sales order lines grouped by item --
SELECT T0.[ItemCode], T0.[Dscription],
SUM(T0.[OpenQty]) AS 'Total Open Qty', SUM(T0.[LineTotal]) AS 'Total Order Value'
FROM RDR1 T0
GROUP BY T0.[ItemCode], T0.[Dscription]
--Example 6 --
-- Closed sales order lines that short shipped or never delivered --
SELECT T1.[ItemCode], T1.[Dscription],
T1.[Quantity] AS 'Order Qty',
T2.[Quantity] AS 'Delivered Qty',
T1.[Price], T1.[LineTotal]
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN DLN1 T2 ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine
WHERE T0.[DocStatus] ='C' AND T1.[LineStatus] ='C'
AND (T1.[Quantity] - T2.[Quantity] > 0 OR T2.Quantity IS NULL)
ORDER BY T1.[ItemCode]
--Example 7 --
-- Open Purchase Orders by Vendor --
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate],
T0.[DocDueDate], T0.[DocTotal]
FROM OPOR T0 WHERE T0.[DocStatus] ='O'
ORDER BY T0.[CardName]
--Example 8 --
-- Items on Open Purchase Orders --
SELECT T1.[ItemCode], T1.[Dscription], T1.[Quantity],
T1.[OpenQty], T0.[DocDueDate], T0.[CardCode],
T0.[CardName]
FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[LineStatus] ='O'
ORDER BY T1.[ItemCode]
--Example 9--
-- Items OnHand sorted by item group--
SELECT T0.[ItemCode], T0.[ItemName], T0.[ItmsGrpCod], T1.[ItmsGrpNam],
T0.[OnHand], T0.[OnOrder], T0.[CardCode]
FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode
ORDER BY T1.[ItmsGrpNam]
--Example 10--
-- Items OnHand by item warehouse--
SELECT T0.[ItemCode], T0.[ItemName], T0.[ItmsGrpCod], T1.[ItmsGrpNam],
T2.[WhsCode], T2.[OnHand], T2.[OnOrder], T0.[CardCode]
FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode
WHERE T2.[WhsCode] Like '%%[%0]%%'
ORDER BY T1.[ItmsGrpNam], T0.[ItemCode]
--Example 11--
--Business Partner account balances and billing addresses--
SELECT T0.[CardCode], T0.[CardName], T0.[GroupCode], T0.[CntctPrsn],
T0.[Balance], T1.[Street], T1.[Block], T1.[City], T1.[State],
T1.[ZipCode]
FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
WHERE T1.[AdresType] ='B' AND T0.[Balance] >=1000
ORDER BY T0.[GroupCode], T0.[CardCode]
--Example 12--
--Balances by business partner group--
SELECT T1.[GroupName], SUM(T0.[Balance])
FROM OCRD T0 INNER JOIN OCRG T1 ON T0.GroupCode = T1.GroupCode
GROUP BY T1.[GroupName]