If you’re new to SAP Business One, solution queries tend to be one of our most popular topics. Queries form the basis of reporting; one of SAP Business One’s hallmarks that empower businesses to improve their operations. The focus of this article is on the SAP query generator and the query wizard, which offer new users a good entry point into the topic and a great refresher for existing SAP Business One users that may have forgotten some of the finer details.
Basics of the SAP Business One Query Generator
From the ‘Tools Menu’ in SAP Business One, go to ‘Queries’, then select the ‘Query Generator’ - this will prompt the ‘Query Generator’ screen. If you don’t know the name of a table you want to look up you can put your cursor in the left most box and press the tab key, and SAP Business One will prompt you with the ‘Choose from List’ table which features all the tables within your system along with associated descriptions of the table. At the time of writing there were over 1,700 tables/fields within SAP Business One that users can reference.
The find box enables users to type the name or partial name and the system will present the remainder of the 4-letter code for the field you are looking for. If, however, you don’t know, or have forgotten the 4-letter name of the table, then all you need to do is highlight the description column and you can then use the search box to search through the Description column. Doing so will enable you to type the words of the field you are searching for and SAP Business One will then prompt you with Tables that contain that word. When you find the table, select it and you will automatically return to the Query Generator where you can enter it into the Query formula.
Adding Fields to the Query Generator
To add fields to the Query Generator, put your cursor into the ‘Select’ box then double click-on the field you need. As you add additional fields like ‘CardCode’, or ‘CardName’, users will note that SAP includes a comma automatically. If you are interested in which grouping the customers in your query are from, you can add ‘GroupCode’ to further specify a code number. However, at a certain point in your business growth you may end up with more codes than you can remember. So, a useful tip starts with going back to the Query Generator main screen. Here you’ll notice that the ‘GroupCode’ is bold. When a field is bold, it means there is a directly related table to the query. If you mouse over the field, a box will appear around it and you can drag it to the leftmost column. In doing so, SAP Business One will complete a SQL join to multiple tables. This enables you to add the ‘GroupName’ field to your query instead of the less descript ‘GroupCode’.
You can add the sales person associated with the customer contacts in your query. However, like ‘GroupCode’ this will present the user with another number. Similarly, mouse over the bolded field ‘SlpCode’ to 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 so that you can better identify the user name. You can repeat this process again and again until your report contains enough information.
How to Filter the Data in Your Query?
The next thing you might be wondering is how to filter the queried data. Of the list you have, it’s made up of vendors, leads, customers, but let’s say you want to look at this data just for customers, there are several ways to do this. You could create three separate queries one for customers, one for leads, and one for vendors 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’ (either C for customers, L for Leads, S for Vendor). Once you run the query, you’ll notice that the list will now exclude entries that do not contain ‘CardType’ values that you have selected.
Another method of doing this, is to add ‘CardType’ to the ‘Where’ clause, but then to click on the ‘Conditions’ button. Next enter the equal sign and add in ‘Variables’ from the column that appears. This will create another box called ‘Query – Selection Criteria’ where you can select from the drop-down either customer, vendor, or lead. Now whenever you execute the query, you will be prompted with the selection criteria so that you can further filter the variables. This is where clause is quite useful because it can also enable users to specify a date range, or even a value less than or equal or greater than of equal to operations.
In the above query example, you can further enhance it by using 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 could insert ‘DESC’ after ‘Balance’ field in the ‘Sort By’ clause to change the account balance by the highest dollar amount to the lowest.
Saving your Query
If you’d like to save your query for future use, SAP Business One gives you that option. Just click the save button in the ‘Query Preview’ window, name it with 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 to access the query.
There’s a lot more to learn about queries in SAP Business One, but this example provides an entry level overview of how they can help you leverage your data for the purposes of decision making. If you’d like to learn more about query generation in SAP Business One, watch our recent video demonstration in SAP Business One hosted by none other than Carl Lewis, SAP Business One Ambassador at Vision33.