How to Use the Query Generator in SAP Business One

Open the SAP Business One main screen

Confirm you are on the main screen. A navigation panel on the left lists modules such as Administration, Financials, Sales Opportunities, Sales - A/R, Purchasing - A/P, Business Partners, Banking, Inventory, Production, MRP, Service, Human Resources, and Reports.

A welcome message at the top reads "Welcome, Manager. You are in the home cockpit of OEC Computers." A search bar lets you look up operations, master data, and documents.

Launch the Query Wizard

Locate the Query Wizard in the menu or toolbar and open it. The introduction screen explains that the wizard enables easy access to the database and an easy way to build user-defined reports. It also says the wizard guides you step by step through the parameters a query requires.

The wizard shows that you are on Step 1 of 5. Click Next > to continue.

Search for tables to include in the report

In Step 2 of 5, the wizard prompts you to select tables for your report. Open the "Choose from List" dialog. In the "Find" field, start typing the description of the table you need, for example "business partners". The list filters as you type and shows the matching tables.

Choose the Business Partner table

Find the table with the technical name OCRD and the description Business Partner, and select it. Below the list, the wizard displays related tables such as Business Partner Addresses, Bank Accounts, and Control Accounts.

Add related tables

Double-click any related table you want to include, such as Card Groups (technical name OCRG). The selected tables appear in the main list for the report.

The related tables for your selection, such as Card Groups, Payment Terms, Price Lists, and Commission Groups, appear below the list. Click Next > to continue.

Select fields for the report columns

In Step 3 of 5, you select the fields to display as column titles. Open the "Choose from List" dialog for the Business Partner table and scroll through the available fields. Hold down the Control key to select several fields at once. This example uses:

• Balance (Account Balance)
• CardCode (BP Code)
• CardName (BP Name)
• CardType (BP Type)
• CntctPrsn (Contact Person)

Include user-defined fields

To add user-defined fields, type U_ in the "Find" field. The wizard performs a smart lookup and lists all user-defined fields, such as:

• U_APA (Artwork Proof Agreement)
• U_APAAGMT (APA Agreement)
• U_AvailCredit (Available Credit)
• U_CreditApp (Credit Application Received)
• U_Cust_Type (Customer Type)
• U_FavTeam (Favorite Team)
• U_Fav_Food (Favorite Food)
• U_Fav_Rest (Favorite Restaurant)

Select the user-defined fields you want in your report.

Switch to a related table and add its fields

To include fields from a related table such as Card Groups, open the drop-down menu in the "Table" column of the "Select Fields & Sort Orders" step. Change the table from "Business Partner" (OCRD) to "Card Groups" (OCRG).

Select the field you want from the Card Groups table, such as Group Name. Then click Next >.

Preview the query results

After you click Next > and Finish, the "Query Preview" window shows the raw data results. The columns include Accrual, Account Balance, BP Code, BP Name, BP Type, Contact Person, Year End, and Group Name.

The SQL statement the wizard generated appears at the top of the window. Scroll to view all returned records. If needed, click Reverse Table to change the order of the displayed data.

Add a parameter to filter the query

To filter results dynamically, for example by Business Partner type, click Back to return to the "Conditions and Relations" step. Then set up the condition:

• On the "Display Conditions" tab, select the OCRD (Business Partner) table.
• In the "Field" column, choose CardType (BP Type).
• Set "Condition" to Equal.
• In the "Field/Value" column, enter [%0]. This defines a parameter that prompts for input each time the query runs.

Review the generated SQL script

Go to the final step of the wizard. The "Query Wizard completed" screen displays the full SQL statement, including the BP Type parameter. Check that all fields and conditions are correct, then click Finish.

Run the query with a parameter

When you finish the wizard, the "Query - Selection Criteria" dialog appears. In the BP Type field, select a value:

• "C" for Customers
• "V" for Vendors
• "S" for Suppliers
• "L" for Leads

Click OK to run the query with that parameter.

View the filtered results and save the query

The "Query Preview" window now shows only the records that match the selected BP Type, for example Customers. Review the filtered results. To keep the query for future use, click Save.

Name and store the query

When prompted, choose the General folder to store the query. Enter a name, such as BP query (Business Partner query). Confirm to save the query.

Run the saved query later

To run the saved query again, follow these steps:

1. Open the Query Manager from the toolbar.
2. Go to the General folder.
3. Select the BP query you saved.
4. When prompted, enter a different BP Type, for example "V" for Vendors, and click OK.

The query runs and displays the data filtered by your parameter selection.