Skip to content

SAP Business One Query Generator

Solution queries are our most popular topic for SAP Business One users. Queries form the basis of reporting—an SAP Business One hallmark that empowers businesses to improve operations.

This article focuses on SAP’s query generator and query wizard. It’s an excellent entry point for new users and a great refresher for existing users.

SAP Business One Query Generator Basics

From the ‘Tools’ menu in SAP Business One, go to ‘Queries’ and then ‘Query Generator’ to get the Query Generator screen. If you don’t know the name of a table, place your cursor in the leftmost box and press the tab key. SAP Business One will offer a ‘Choose From List’ table, which features every table in your system, along with descriptions.

Type a name or partial name in the ‘Find’ box to get the 4-letter code for the field you want. If you don’t know the 4-letter table name, highlight the ‘Description’ column and search for it using the search box. When you type the words of the field you’re searching for, SAP Business One will offer a list of tables containing those words. When you find the table, select it to be automatically returned to the Query Generator, where you can enter it into the query formula.

How to Add Fields to the SAP Business One Query Generator

To add fields to the Query Generator, place your cursor on the ‘Select’ box, then double-click the field you need. As you add additional fields like ‘CardCode’ or ‘CardName,’ SAP automatically includes a comma. To determine which grouping the customers in your query are from, you can add ‘GroupCode’ to specify a code number. However, your business growth will likely lead to more codes than you can remember.

Helpful hint: Return to the Query Generator main screen, where ‘GroupCode’ is bolded. When a field is bolded, it means there’s a table directly related to the query. If you hover your mouse over the field, a box will appear, which you can drag to the leftmost column. In doing so, SAP Business One will complete a SQL join to multiple tables. This allows you to add the ‘GroupName’ field to your query instead of the less descriptive ‘GroupCode.’

You can add the salesperson associated with the customer contacts in your query, but this will give you another number. Hover your mouse over the bolded field ‘SlpCode’ in the leftmost column in the Query Generator to SQL join the table. Once linked, you can search the new table for the more descriptive ‘SlpName’ and add it to the query to better identify the username. Repeat this process until your report contains enough information.

How to Filter the Data in Your Query

Your queried data consists of vendors, leads, customers, etc. To examine customer data only, you could create three queries: one for customers, one for leads, and one for vendors. Do this by adding the ‘CardType’ field to the ‘Where’ clause, then the equal sign (=) with a single quotation, and then one of the codes associated with ‘CardType’ (C for customers, L for leads, S for vendors). After you run the query, the list will exclude entries that don’t contain the ‘CardType’ values you selected.

Another method is to add ‘CardType’ to the ‘Where’ clause, then click on the ‘Conditions’ button. Enter the equal sign and add ‘Variables’ from the column that appears. This will create another box called ‘Query – Selection Criteria.’ Select customer, vendor, or lead from the drop-down. Now, whenever you execute the query, you’ll be prompted with the selection criteria to further filter the variables. You can also specify a date range or a value less than, greater than, or equal to operations.

Sort By

You can enhance the query above with the ‘Sort By’ clause. Add the ‘Balance’ field to the ‘Sort By’ clause to sort your queried report by customer balance in ascending order. You can also insert ‘DESC’ after the ‘Balance’ field in the ‘Sort By’ clause to change the account balance from the highest dollar amount to the lowest.

Saving Your Query

If you’d like to save your query, click the save button in the ‘Query Preview’ window, name it something descriptive like ‘Business Partner Balances,’ then add it to a category. To access it in the future, go to Tools, Queries, Query Manager, then the folder you put it in.

Learn More

There’s a lot to learn about queries in SAP Business One, but this example summarizes how queries can help you leverage your data to make fast, confident decisions.

To learn more about query generation in SAP Business One and access other helpful SAP Business One resources, watch a video demonstration hosted by Carl Lewis, Vision33’s SAP Business One Ambassador.

Watch the Video