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
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 use your data to make fast, confident decisions.
Want to dive deeper into query writing? Check out Vision33’s query writer webinar recordings.
In part one, you’ll learn how to build your own queries from simple to moderate level. You'll receive an overview of the query generator and learn how to identify table and field names, access the help file, what table joins are and how they work, and how to build a query.
In part two, you’ll see how to take query writing to the next level. You’ll learn about left and right joins, query category management, and special command examples.
These videos also highlight key shortcuts that can help you save time in your daily operations.
Vision33 is a multi-award-winning SAP partner who has lived and breathed SAP Business One for over 20 years. We work hand in hand with our customers to help them get the most value out of SAP Business One with ongoing training, integration solutions, and exclusive technologies.
Learn more and join our customer resource center here.
