Book Image

Mastering SQL Queries for SAP Business One

By : Gordon Du
Book Image

Mastering SQL Queries for SAP Business One

By: Gordon Du

Overview of this book

SAP Business One is an integrated Enterprise Resource Planning (ERP) software which offers an ideal solution for growing small to midsized businesses. For such companies, retrieving the most relevant information from their business data can be key to standing out from the competition. SAP Business One is a rapidly growing software package and this book is timely in giving those businesses an advantage in the area of Business Intelligence. Having SQL query skills in- house is the most important and cost-effective move you can make in this growing field.This practical guide will provide you with the skills to gain more specific business information from SAP Business One by using SQL queries. It will provide you with solutions for solving complicated report related problems, covering basic tools like the Query Generator and Query Wizard. More advanced content like using queries with Crystal Reports will also be delved into.SQL query is one of the advanced tools available in SAP Business One which is easily learned and quickly utilized. By referring back to and applying the many examples in this book, you will be able to create and run correct, and therefore effective, SQL queries to help your business.The book begins by teaching a clear definition of the SQL query, and covers the data dictionary and table links. Coverage will then jump to a higher level of complex SQL queries, discussing features like FMS. Along the way more advanced SQL Query topics will be covered, such as extending the scope of basic SQL queries for more complicated cases. You will ultimately gain in depth query knowledge to bring more Business Intelligence into SAP Business One.
Table of Contents (4 chapters)

Chapter 2. Query Generator and Query Wizard

In the previous chapter, you learned basic concepts regarding certain terminologies used in this book. You also know that tables and table relationships are important for SQL Query. What are all of these concepts about? You will better understand when you start your own journey to create a SQL Query report.

How do I start? That is a good question. I have learned this the hard way. Even though the tools to create queries are readily available from the SAP Business One menu, I myself had never gone through most of these tools before I planned to write this book to show you how to start. I have kept using only Query Manager and system queries until now. To be honest, I just found out too late that I took such a longer route than necessary. What a pity! If I had started with Query Generator and Query Wizard, it would have saved me a tremendous amount of time.

These tools are quite convenient and efficient for everyone to use, especially for starters to write their first query for SAP Business One. These tools will help you to omit this process of writing every single statement, tables' names, or fields' names, etc.

This chapter will introduce you to two basic SQL Query tools for SAP Business One:

  • Query generator
  • Query wizard

Both tools are for starters to get to know SQL query in SAP Business One quickly. After you have learned these tools, you could get a simple job done just with a few mouse clicks. The introduction gives you detailed steps so that you can have step-by-step advice.

In the next section, the differences between these tools are discussed. You can decide which one is more suitable for you when you read through all their features and benefits.

If you are a more experienced reader, you may find that these tools are no longer necessary. Otherwise, you are strongly encouraged to go through the chapter to refresh yourself.

The last section introduces System Queries built-in to SAP Business One. Those system queries are another good start for readers whose SQL Query level is above average. You could then customize them and create your own queries quickly. For a beginner, you may just learn how to run those queries first to save time.

Query Generator and Query Wizard

The previous screenshot shows you how to access these different tools from the SAP Business One menu. You can find all tools for this chapter from it. They are all under the Tools | Queries menu. Query Generator is the second item; Query Wizard is the third item; and System Queries is number five.

Query Generator

Query Generator is the first tool to be discussed. It is located above the Query Wizard in the menu item list.

Query Generator overview

Query Generator enables you to create queries using the SQL query engine. Like all other tools, it is designed for data retrieval/selection only. You are not able to do any DML queries such as updates, insert, or delete. This menu item can be accessed from Tools | Queries | Query Generator.

With this tool, you are able to:

  • Create many queries yourself from a fixed set of tables in SAP Business One
  • Access all the data in those tables and evaluate it according to your needs
  • Create individual reports without writing any single query statements

Left part of Query Generator form

After you click on the Query Generator menu item, you can bring up the main form of Query Generator. On the top left of the form, you may find a yellow cell. Click the Tab key from the empty cell to bring up Choose from List, as in the following screenshot:

Left part of Query Generator form

The set of table names will then show up. You could type any letters in the Find textbox to bring up the table names starting with that letter. In the example, you could find that the letter O is typed in. All tables starting with O would be on the top list. You could either use the scroll bar or page up/down key to find the tables you are looking for.

You'll remember we discussed the importance of the Data Dictionary in the previous chapter. Without the dictionary, it might be too hard to find which tables you need. If Table References is available, you could easily find the table quickly. For the sake of time, if you already know the names then you could just type in the full table names.

However, here is still one of the best places to find all commonly used tables for SAP Business One in case the help file is not available to you. In this less than ideal case, you probably need to go through the table name list quite a few times to become familiar with them.

In the example screenshot, the highlighted table OCRD (Business Partner table) will be selected when you click Choose:

Left part of Query Generator form

You may select more than one table in the same way as the first table was selected. In the screenshot you just saw, the OCST (State table) has also been selected. If you selected the wrong table or changed your mind, the X button here could be used to remove it from the list.

Middle and right parts of Query Generator form

After the table selection, you would see that the middle part of the form shows you the fields from the highlighted table in alphabetical order. The right part shows the query component when you double-click the field name or table names on a proper box.

Under Select, three fields are selected from two tables. Under From, the table names are automatically shown with the default alias T0 and T1. The default link by system is also shown. If the link is not correct, you can manually fix it.

Under Where, you can choose any fields to restrict the query result. Here, T1.[Name] has been selected for the purpose of bringing the Business Partner according to the State/Province names.

You may notice that there is an additional form shown. This appears after clicking on the Conditions button. You may find 12 conditional formulas from the form that can be selected. The Variables part allows you to select variable as [%0], [%1], and so on. The percent sign plus a number represents a variable for SQL query in SAP Business One. It can allow the user to select or input values during query execution.

In the last example, Start With formula plus [%0] variable gives the result as T1.[Name] like '[%0]'. The additional % on the right of [%0] is a manual input wildcard character that can be used as a suffix to match any string of zero or more characters.

Under Sort, T1.[Name] is also selected to allow query results to be sorted by State/Province names.

Executing a query from query generator form

When all the required information has been selected, click on Execute. Then the following form Query—Selection Criteria will pop up for you to input any letters:

Executing a query from query generator form

In the previous example, a letter Y has been entered. That means you will get the query result with all business partner code and names from the state/province with the name starting with Y.

The result looks like the next screenshot:

Executing a query from query generator form

If this query could be used later, click on Save near the bottom to save your query. It might be saved under any query categories with the name you entered. The topic regarding query saving will be discussed in detail later.

The Reverse Table button at the bottom of the form is used to help you choose to display the table either from right-to-left or from left-to-right. This is because unlike English, some of the other languages may not start from left-to-right, but in reverse order.

You may notice that all query script from different sections of the generator has been linked together for you. Remember, you do not need to write any single statement. This is such a good gift for you to reduce your learning curve in terms of query learning. Do not waste this valuable resource!

Query wizard

Query wizard is the second tool to be discussed in this chapter. It is similar to query generator. We are going to compare both tools later in the book.

Query Wizard overview

Query Wizard enables easy access to the database and an easy way of building user-defined reports. It is designed for data retrieval/selection only. You are not able to do any DML queries such as updates, insert, or delete. This menu item can be accessed from Tools | Queries | Query Wizard.

With this tool, you can do the following:

  • Create queries through five steps from a fixed set of tables from SAP Business One
  • Access all data in those tables and get help from tips on each step
  • Create individual reports without writing any single query statements

Step 1—Splash screen

The first step is very simple. After you click on Query Wizard menu item, you get the first screen that simply tells you: This wizard will guide you step-by-step through the definition of parameters required for a query. The screenshot is omitted here since it is nothing but a splash screen for you to know you are starting this wizard.

Step 2—Select tables for the report

The second step is similar to the left part in Query Generator. You can select as many tables as you need. However, you must try to minimize the number of tables for system performance and query efficiency.

Step 2—Select tables for the report

In the example screenshot you just saw, you may find that the first table selected is OCRD (Business Partner table). The second table selected is OSLP (Sales Employee table). Each table selected is placed in a separate row in the window. The second column displays the full description of the tables.

One thing here is, it is noticeably better than Query Generator. When you select any table, it automatically shows all linked tables under the lower part of the form. You will then find it very convenient to just choose the necessary tables by double-clicking.

This process applies to all tables selected in the upper part of the form.

A linked table list in the lower part of the form changes when you highlight different tables from the upper part of the form.

Step 3—Select fields and sort orders

Step 3 in Query Wizard has the same function as the middle part of Query Generator. In addition, you have more options to select fields.

Step 3—Select fields and sort orders

You may tab out the Field column to bring up Choose from list from the selected table. It is just like when you selected tables from both tools. You may type in any letters on the Find textbox to search your requested fields. There are two columns on the list:

  • Name: Field names
  • Description: Field description

As you know from the previous chapter, you can get all field information from the Table References for SAP Business One in advance. If that is not available to you, this might be the second best place to find all commonly used field information. You will probably need to go through them many times before you can reach frequently used fields at ease.

You can type the letter C to bring the field names starting with C on top of the list, so that you can get to the fields quicker. Or, you may not need to type any letters. Just use the mouse or page down to browse through the list in order to become familiar with those fields.

Step 3—Select fields and sort orders

From the previous screenshot, you can see that two fields in OCRD have been selected. Another field from OSLP has also been selected. They are:

  • CardCode: Business Partner Code from OCRD
  • CardName: Business Partner Name from OCRD
  • SlpName: Sales Employee Name from OSLP

The third column, Heading, displays the field description by default. You can change them to anything you want such as Customer Name instead of BP Name. It will show on the top of the query result screen as a column heading.

The fourth column, Sort Order, uses an integer (1, 2, 3) to set the sort priority; you can assign any orders to the field you have selected.

The fifth column defines the sort type as Ascending or Descending.

The sixth column allows you to set the group on any fields you would like to add. You just need to select Y for the field you would like it to be grouped to. If you have not selected this, the default value would be N.

The last column in the previous screenshot, Comp., offers six computation options:

  • Total Records: Displays the number of records retrieved
  • Total Distinct Records: Displays the number of distinct records retrieved
  • Amount: Displays the sum of the values for numeric field in the retrieved records
  • Average: Calculates and displays the average of the values of that field in the retrieved records
  • Minimum: Displays the smallest value of this field from within the retrieved records
  • Maximum: Displays the largest value of this field from within the retrieved records

Step 4—Conditions and relations

Step 4 is for defining the conditions and relations for retrieving data. Both conditions and relations are based on the database structure and logic.

Step 4—Conditions and relations

For the previous example, it is the Display Conditions tab. You can see the condition entered is Sales Employee's name, which contains Gordon.

You may select ( or ) on these two tabs to define the priority sequence of the conditions. You may also select And/Or to define complex conditions.

Step 4—Conditions and relations

The other tab is for Display Relations. Under this tab, you will find the first column's checkbox, Execute. It applies the defined relationship between the tables that appear in the row. When this checkbox is selected, SAP Business One adds another condition. This means that the records you want to retrieve must comply with the conditions defined on the Define Conditions tab and with the added condition.

Step 5—Query wizard completion

When you complete all Conditions and Relations, clicking on Next will bring you to the final step, which shows you the Query script created by the system that applies all of your selections.

Step 5—Query wizard completion

You may review the query to check if it is all you need. If you find that it did not include all conditions, you can go back to edit some of them in the previous steps.

In the example case, there are no problems. Click Finish to bring up the query result window.

You can find all Business Partner Codes and Names under the selected State/Province from the query results.

Like Query Generator, if the query is useful, you can click Save to save your query. The topic regarding query saving will be discussed in the Creating and saving user queries section of the next chapter.

Step 5—Query wizard completion

Note

There is a video tutorial available for Query Wizard by SAP. You can find it here: http://www.youtube.com/watch?v=xaLO_4JnG-E. From this video, you will have additional information in a classroom-like instruction for the topic here.

Query Wizard overview

Query Wizard enables easy access to the database and an easy way of building user-defined reports. It is designed for data retrieval/selection only. You are not able to do any DML queries such as updates, insert, or delete. This menu item can be accessed from Tools | Queries | Query Wizard.

With this tool, you can do the following:

  • Create queries through five steps from a fixed set of tables from SAP Business One
  • Access all data in those tables and get help from tips on each step
  • Create individual reports without writing any single query statements

Step 1—Splash screen

The first step is very simple. After you click on Query Wizard menu item, you get the first screen that simply tells you: This wizard will guide you step-by-step through the definition of parameters required for a query. The screenshot is omitted here since it is nothing but a splash screen for you to know you are starting this wizard.

Step 2—Select tables for the report

The second step is similar to the left part in Query Generator. You can select as many tables as you need. However, you must try to minimize the number of tables for system performance and query efficiency.

Step 2—Select tables for the report

In the example screenshot you just saw, you may find that the first table selected is OCRD (Business Partner table). The second table selected is OSLP (Sales Employee table). Each table selected is placed in a separate row in the window. The second column displays the full description of the tables.

One thing here is, it is noticeably better than Query Generator. When you select any table, it automatically shows all linked tables under the lower part of the form. You will then find it very convenient to just choose the necessary tables by double-clicking.

This process applies to all tables selected in the upper part of the form.

A linked table list in the lower part of the form changes when you highlight different tables from the upper part of the form.

Step 3—Select fields and sort orders

Step 3 in Query Wizard has the same function as the middle part of Query Generator. In addition, you have more options to select fields.

Step 3—Select fields and sort orders

You may tab out the Field column to bring up Choose from list from the selected table. It is just like when you selected tables from both tools. You may type in any letters on the Find textbox to search your requested fields. There are two columns on the list:

  • Name: Field names
  • Description: Field description

As you know from the previous chapter, you can get all field information from the Table References for SAP Business One in advance. If that is not available to you, this might be the second best place to find all commonly used field information. You will probably need to go through them many times before you can reach frequently used fields at ease.

You can type the letter C to bring the field names starting with C on top of the list, so that you can get to the fields quicker. Or, you may not need to type any letters. Just use the mouse or page down to browse through the list in order to become familiar with those fields.

Step 3—Select fields and sort orders

From the previous screenshot, you can see that two fields in OCRD have been selected. Another field from OSLP has also been selected. They are:

  • CardCode: Business Partner Code from OCRD
  • CardName: Business Partner Name from OCRD
  • SlpName: Sales Employee Name from OSLP

The third column, Heading, displays the field description by default. You can change them to anything you want such as Customer Name instead of BP Name. It will show on the top of the query result screen as a column heading.

The fourth column, Sort Order, uses an integer (1, 2, 3) to set the sort priority; you can assign any orders to the field you have selected.

The fifth column defines the sort type as Ascending or Descending.

The sixth column allows you to set the group on any fields you would like to add. You just need to select Y for the field you would like it to be grouped to. If you have not selected this, the default value would be N.

The last column in the previous screenshot, Comp., offers six computation options:

  • Total Records: Displays the number of records retrieved
  • Total Distinct Records: Displays the number of distinct records retrieved
  • Amount: Displays the sum of the values for numeric field in the retrieved records
  • Average: Calculates and displays the average of the values of that field in the retrieved records
  • Minimum: Displays the smallest value of this field from within the retrieved records
  • Maximum: Displays the largest value of this field from within the retrieved records

Step 4—Conditions and relations

Step 4 is for defining the conditions and relations for retrieving data. Both conditions and relations are based on the database structure and logic.

Step 4—Conditions and relations

For the previous example, it is the Display Conditions tab. You can see the condition entered is Sales Employee's name, which contains Gordon.

You may select ( or ) on these two tabs to define the priority sequence of the conditions. You may also select And/Or to define complex conditions.

Step 4—Conditions and relations

The other tab is for Display Relations. Under this tab, you will find the first column's checkbox, Execute. It applies the defined relationship between the tables that appear in the row. When this checkbox is selected, SAP Business One adds another condition. This means that the records you want to retrieve must comply with the conditions defined on the Define Conditions tab and with the added condition.

Step 5—Query wizard completion

When you complete all Conditions and Relations, clicking on Next will bring you to the final step, which shows you the Query script created by the system that applies all of your selections.

Step 5—Query wizard completion

You may review the query to check if it is all you need. If you find that it did not include all conditions, you can go back to edit some of them in the previous steps.

In the example case, there are no problems. Click Finish to bring up the query result window.

You can find all Business Partner Codes and Names under the selected State/Province from the query results.

Like Query Generator, if the query is useful, you can click Save to save your query. The topic regarding query saving will be discussed in the Creating and saving user queries section of the next chapter.

Step 5—Query wizard completion

Note

There is a video tutorial available for Query Wizard by SAP. You can find it here: http://www.youtube.com/watch?v=xaLO_4JnG-E. From this video, you will have additional information in a classroom-like instruction for the topic here.

Step 1—Splash screen

The first step is very simple. After you click on Query Wizard menu item, you get the first screen that simply tells you: This wizard will guide you step-by-step through the definition of parameters required for a query. The screenshot is omitted here since it is nothing but a splash screen for you to know you are starting this wizard.

Step 2—Select tables for the report

The second step is similar to the left part in Query Generator. You can select as many tables as you need. However, you must try to minimize the number of tables for system performance and query efficiency.

Step 2—Select tables for the report

In the example screenshot you just saw, you may find that the first table selected is OCRD (Business Partner table). The second table selected is OSLP (Sales Employee table). Each table selected is placed in a separate row in the window. The second column displays the full description of the tables.

One thing here is, it is noticeably better than Query Generator. When you select any table, it automatically shows all linked tables under the lower part of the form. You will then find it very convenient to just choose the necessary tables by double-clicking.

This process applies to all tables selected in the upper part of the form.

A linked table list in the lower part of the form changes when you highlight different tables from the upper part of the form.

Step 3—Select fields and sort orders

Step 3 in Query Wizard has the same function as the middle part of Query Generator. In addition, you have more options to select fields.

Step 3—Select fields and sort orders

You may tab out the Field column to bring up Choose from list from the selected table. It is just like when you selected tables from both tools. You may type in any letters on the Find textbox to search your requested fields. There are two columns on the list:

  • Name: Field names
  • Description: Field description

As you know from the previous chapter, you can get all field information from the Table References for SAP Business One in advance. If that is not available to you, this might be the second best place to find all commonly used field information. You will probably need to go through them many times before you can reach frequently used fields at ease.

You can type the letter C to bring the field names starting with C on top of the list, so that you can get to the fields quicker. Or, you may not need to type any letters. Just use the mouse or page down to browse through the list in order to become familiar with those fields.

Step 3—Select fields and sort orders

From the previous screenshot, you can see that two fields in OCRD have been selected. Another field from OSLP has also been selected. They are:

  • CardCode: Business Partner Code from OCRD
  • CardName: Business Partner Name from OCRD
  • SlpName: Sales Employee Name from OSLP

The third column, Heading, displays the field description by default. You can change them to anything you want such as Customer Name instead of BP Name. It will show on the top of the query result screen as a column heading.

The fourth column, Sort Order, uses an integer (1, 2, 3) to set the sort priority; you can assign any orders to the field you have selected.

The fifth column defines the sort type as Ascending or Descending.

The sixth column allows you to set the group on any fields you would like to add. You just need to select Y for the field you would like it to be grouped to. If you have not selected this, the default value would be N.

The last column in the previous screenshot, Comp., offers six computation options:

  • Total Records: Displays the number of records retrieved
  • Total Distinct Records: Displays the number of distinct records retrieved
  • Amount: Displays the sum of the values for numeric field in the retrieved records
  • Average: Calculates and displays the average of the values of that field in the retrieved records
  • Minimum: Displays the smallest value of this field from within the retrieved records
  • Maximum: Displays the largest value of this field from within the retrieved records

Step 4—Conditions and relations

Step 4 is for defining the conditions and relations for retrieving data. Both conditions and relations are based on the database structure and logic.

Step 4—Conditions and relations

For the previous example, it is the Display Conditions tab. You can see the condition entered is Sales Employee's name, which contains Gordon.

You may select ( or ) on these two tabs to define the priority sequence of the conditions. You may also select And/Or to define complex conditions.

Step 4—Conditions and relations

The other tab is for Display Relations. Under this tab, you will find the first column's checkbox, Execute. It applies the defined relationship between the tables that appear in the row. When this checkbox is selected, SAP Business One adds another condition. This means that the records you want to retrieve must comply with the conditions defined on the Define Conditions tab and with the added condition.

Step 5—Query wizard completion

When you complete all Conditions and Relations, clicking on Next will bring you to the final step, which shows you the Query script created by the system that applies all of your selections.

Step 5—Query wizard completion

You may review the query to check if it is all you need. If you find that it did not include all conditions, you can go back to edit some of them in the previous steps.

In the example case, there are no problems. Click Finish to bring up the query result window.

You can find all Business Partner Codes and Names under the selected State/Province from the query results.

Like Query Generator, if the query is useful, you can click Save to save your query. The topic regarding query saving will be discussed in the Creating and saving user queries section of the next chapter.

Step 5—Query wizard completion

Note

There is a video tutorial available for Query Wizard by SAP. You can find it here: http://www.youtube.com/watch?v=xaLO_4JnG-E. From this video, you will have additional information in a classroom-like instruction for the topic here.

Step 2—Select tables for the report

The second step is similar to the left part in Query Generator. You can select as many tables as you need. However, you must try to minimize the number of tables for system performance and query efficiency.

Step 2—Select tables for the report

In the example screenshot you just saw, you may find that the first table selected is OCRD (Business Partner table). The second table selected is OSLP (Sales Employee table). Each table selected is placed in a separate row in the window. The second column displays the full description of the tables.

One thing here is, it is noticeably better than Query Generator. When you select any table, it automatically shows all linked tables under the lower part of the form. You will then find it very convenient to just choose the necessary tables by double-clicking.

This process applies to all tables selected in the upper part of the form.

A linked table list in the lower part of the form changes when you highlight different tables from the upper part of the form.

Step 3—Select fields and sort orders

Step 3 in Query Wizard has the same function as the middle part of Query Generator. In addition, you have more options to select fields.

Step 3—Select fields and sort orders

You may tab out the Field column to bring up Choose from list from the selected table. It is just like when you selected tables from both tools. You may type in any letters on the Find textbox to search your requested fields. There are two columns on the list:

  • Name: Field names
  • Description: Field description

As you know from the previous chapter, you can get all field information from the Table References for SAP Business One in advance. If that is not available to you, this might be the second best place to find all commonly used field information. You will probably need to go through them many times before you can reach frequently used fields at ease.

You can type the letter C to bring the field names starting with C on top of the list, so that you can get to the fields quicker. Or, you may not need to type any letters. Just use the mouse or page down to browse through the list in order to become familiar with those fields.

Step 3—Select fields and sort orders

From the previous screenshot, you can see that two fields in OCRD have been selected. Another field from OSLP has also been selected. They are:

  • CardCode: Business Partner Code from OCRD
  • CardName: Business Partner Name from OCRD
  • SlpName: Sales Employee Name from OSLP

The third column, Heading, displays the field description by default. You can change them to anything you want such as Customer Name instead of BP Name. It will show on the top of the query result screen as a column heading.

The fourth column, Sort Order, uses an integer (1, 2, 3) to set the sort priority; you can assign any orders to the field you have selected.

The fifth column defines the sort type as Ascending or Descending.

The sixth column allows you to set the group on any fields you would like to add. You just need to select Y for the field you would like it to be grouped to. If you have not selected this, the default value would be N.

The last column in the previous screenshot, Comp., offers six computation options:

  • Total Records: Displays the number of records retrieved
  • Total Distinct Records: Displays the number of distinct records retrieved
  • Amount: Displays the sum of the values for numeric field in the retrieved records
  • Average: Calculates and displays the average of the values of that field in the retrieved records
  • Minimum: Displays the smallest value of this field from within the retrieved records
  • Maximum: Displays the largest value of this field from within the retrieved records

Step 4—Conditions and relations

Step 4 is for defining the conditions and relations for retrieving data. Both conditions and relations are based on the database structure and logic.

Step 4—Conditions and relations

For the previous example, it is the Display Conditions tab. You can see the condition entered is Sales Employee's name, which contains Gordon.

You may select ( or ) on these two tabs to define the priority sequence of the conditions. You may also select And/Or to define complex conditions.

Step 4—Conditions and relations

The other tab is for Display Relations. Under this tab, you will find the first column's checkbox, Execute. It applies the defined relationship between the tables that appear in the row. When this checkbox is selected, SAP Business One adds another condition. This means that the records you want to retrieve must comply with the conditions defined on the Define Conditions tab and with the added condition.

Step 5—Query wizard completion

When you complete all Conditions and Relations, clicking on Next will bring you to the final step, which shows you the Query script created by the system that applies all of your selections.

Step 5—Query wizard completion

You may review the query to check if it is all you need. If you find that it did not include all conditions, you can go back to edit some of them in the previous steps.

In the example case, there are no problems. Click Finish to bring up the query result window.

You can find all Business Partner Codes and Names under the selected State/Province from the query results.

Like Query Generator, if the query is useful, you can click Save to save your query. The topic regarding query saving will be discussed in the Creating and saving user queries section of the next chapter.

Step 5—Query wizard completion

Note

There is a video tutorial available for Query Wizard by SAP. You can find it here: http://www.youtube.com/watch?v=xaLO_4JnG-E. From this video, you will have additional information in a classroom-like instruction for the topic here.

Step 3—Select fields and sort orders

Step 3 in Query Wizard has the same function as the middle part of Query Generator. In addition, you have more options to select fields.

Step 3—Select fields and sort orders

You may tab out the Field column to bring up Choose from list from the selected table. It is just like when you selected tables from both tools. You may type in any letters on the Find textbox to search your requested fields. There are two columns on the list:

  • Name: Field names
  • Description: Field description

As you know from the previous chapter, you can get all field information from the Table References for SAP Business One in advance. If that is not available to you, this might be the second best place to find all commonly used field information. You will probably need to go through them many times before you can reach frequently used fields at ease.

You can type the letter C to bring the field names starting with C on top of the list, so that you can get to the fields quicker. Or, you may not need to type any letters. Just use the mouse or page down to browse through the list in order to become familiar with those fields.

Step 3—Select fields and sort orders

From the previous screenshot, you can see that two fields in OCRD have been selected. Another field from OSLP has also been selected. They are:

  • CardCode: Business Partner Code from OCRD
  • CardName: Business Partner Name from OCRD
  • SlpName: Sales Employee Name from OSLP

The third column, Heading, displays the field description by default. You can change them to anything you want such as Customer Name instead of BP Name. It will show on the top of the query result screen as a column heading.

The fourth column, Sort Order, uses an integer (1, 2, 3) to set the sort priority; you can assign any orders to the field you have selected.

The fifth column defines the sort type as Ascending or Descending.

The sixth column allows you to set the group on any fields you would like to add. You just need to select Y for the field you would like it to be grouped to. If you have not selected this, the default value would be N.

The last column in the previous screenshot, Comp., offers six computation options:

  • Total Records: Displays the number of records retrieved
  • Total Distinct Records: Displays the number of distinct records retrieved
  • Amount: Displays the sum of the values for numeric field in the retrieved records
  • Average: Calculates and displays the average of the values of that field in the retrieved records
  • Minimum: Displays the smallest value of this field from within the retrieved records
  • Maximum: Displays the largest value of this field from within the retrieved records

Step 4—Conditions and relations

Step 4 is for defining the conditions and relations for retrieving data. Both conditions and relations are based on the database structure and logic.

Step 4—Conditions and relations

For the previous example, it is the Display Conditions tab. You can see the condition entered is Sales Employee's name, which contains Gordon.

You may select ( or ) on these two tabs to define the priority sequence of the conditions. You may also select And/Or to define complex conditions.

Step 4—Conditions and relations

The other tab is for Display Relations. Under this tab, you will find the first column's checkbox, Execute. It applies the defined relationship between the tables that appear in the row. When this checkbox is selected, SAP Business One adds another condition. This means that the records you want to retrieve must comply with the conditions defined on the Define Conditions tab and with the added condition.

Step 5—Query wizard completion

When you complete all Conditions and Relations, clicking on Next will bring you to the final step, which shows you the Query script created by the system that applies all of your selections.

Step 5—Query wizard completion

You may review the query to check if it is all you need. If you find that it did not include all conditions, you can go back to edit some of them in the previous steps.

In the example case, there are no problems. Click Finish to bring up the query result window.

You can find all Business Partner Codes and Names under the selected State/Province from the query results.

Like Query Generator, if the query is useful, you can click Save to save your query. The topic regarding query saving will be discussed in the Creating and saving user queries section of the next chapter.

Step 5—Query wizard completion

Note

There is a video tutorial available for Query Wizard by SAP. You can find it here: http://www.youtube.com/watch?v=xaLO_4JnG-E. From this video, you will have additional information in a classroom-like instruction for the topic here.

Step 4—Conditions and relations

Step 4 is for defining the conditions and relations for retrieving data. Both conditions and relations are based on the database structure and logic.

Step 4—Conditions and relations

For the previous example, it is the Display Conditions tab. You can see the condition entered is Sales Employee's name, which contains Gordon.

You may select ( or ) on these two tabs to define the priority sequence of the conditions. You may also select And/Or to define complex conditions.

Step 4—Conditions and relations

The other tab is for Display Relations. Under this tab, you will find the first column's checkbox, Execute. It applies the defined relationship between the tables that appear in the row. When this checkbox is selected, SAP Business One adds another condition. This means that the records you want to retrieve must comply with the conditions defined on the Define Conditions tab and with the added condition.

Step 5—Query wizard completion

When you complete all Conditions and Relations, clicking on Next will bring you to the final step, which shows you the Query script created by the system that applies all of your selections.

Step 5—Query wizard completion

You may review the query to check if it is all you need. If you find that it did not include all conditions, you can go back to edit some of them in the previous steps.

In the example case, there are no problems. Click Finish to bring up the query result window.

You can find all Business Partner Codes and Names under the selected State/Province from the query results.

Like Query Generator, if the query is useful, you can click Save to save your query. The topic regarding query saving will be discussed in the Creating and saving user queries section of the next chapter.

Step 5—Query wizard completion

Note

There is a video tutorial available for Query Wizard by SAP. You can find it here: http://www.youtube.com/watch?v=xaLO_4JnG-E. From this video, you will have additional information in a classroom-like instruction for the topic here.

Step 5—Query wizard completion

When you complete all Conditions and Relations, clicking on Next will bring you to the final step, which shows you the Query script created by the system that applies all of your selections.

Step 5—Query wizard completion

You may review the query to check if it is all you need. If you find that it did not include all conditions, you can go back to edit some of them in the previous steps.

In the example case, there are no problems. Click Finish to bring up the query result window.

You can find all Business Partner Codes and Names under the selected State/Province from the query results.

Like Query Generator, if the query is useful, you can click Save to save your query. The topic regarding query saving will be discussed in the Creating and saving user queries section of the next chapter.

Step 5—Query wizard completion

Note

There is a video tutorial available for Query Wizard by SAP. You can find it here: http://www.youtube.com/watch?v=xaLO_4JnG-E. From this video, you will have additional information in a classroom-like instruction for the topic here.

What is the difference between Query generator and Query wizard?

When you go through the first part of the chapter, you have probably already noticed the differences between the Query Generator and Query Wizard tools.

You will find the summarized differences here to clarify any doubts you may have.

The first tool, Query Generator, is one of the simplest tools for building SQL Queries. It just uses one simple User Interface (UI) for you to create queries. You can start to use it whenever you wish to find the tables, fields, and their relationship.

The second tool, Query Wizard, has added five steps with different forms, so that you can find the table relationship more easily. Also, finding fields becomes much easier because you have the option to bring up Choose from list.

Here is the list showing the similarities between these two tools:

  • There is no need to write full query statements
  • Allows selection of tables and fields
  • Prohibits updating data
  • Uses the mouse alone to add conditions

Differences

Query Generator

Query Wizard

 

Single step User Interface

Requires more tables and fields knowledge

Less help and tips

Field level selection is basic

Multiple steps User Interface

Requires less tables and fields knowledge

More help and tips

Field level selection is sophisticated

In summary, the Query Generator is designed to get quick results in a single step, while Query Wizard is more concentrated on step-by-step instructions to help you build queries with clear selection in different phases.

I prefer the Query Generator over the Query Wizard because I am familiar with all tables and fields. To me, the simpler the steps, the better. To a beginner, I would suggest you try Query Wizard because it gives you more power to select different tables and fields. You will also be provided with more helpful tips.

Benefitting from built-in system queries

Besides Query Generator and Query Wizard, there is another powerful tool for you to learn SQL Query in SAP Business One. That is: System Queries.

System queries enable you to generate additional reports and retrieve data that is not available by running the other reports. You may access system queries either from the different modules—in each module the relevant system queries appear as entries under report menu, identified with the icon ? or by choosing them from Tools | Queries | System Queries.

The following screenshot shows you the names of all the available system queries. You may find as many as 18 queries here. Most queries are related to finance and banking.

Benefitting from built-in system queries

You can see the query results from one of the system queries: Purchase Order Linked to Deposit.

The result is actually empty because there is no deposit linked to the purchase order in the database. You would like this result, wouldn't you?

What you need to know is the top part of the query result. You will see there is a Left Arrow (Show icon) there. When you click on the arrow, you will get the full body of the query statements.

Benefitting from built-in system queries

As soon as you click on the Left Arrow icon, it will become Down Arrow (Hide icon). You will notice there are two additional icons displayed. One is Pencil Only. The other is a Pencil with Cross. All query statements are shown as well.

This is only available if you run those system queries under the tools menu instead of each module under report.

When you click on the Pencil Only icon, the background of the query statement area will become yellow instead of grey. You are now able to modify the query for your own use.

Tip

Be careful about changing this. You need a good understanding of the SAP Business One data structure in advance. Don't panic here either. You can try anything to amend the query. There is no harm to the system unless you try to overwrite the system query.

When you finish editing, you may click the other icon (Pencil with Cross). That will change back to the read only mode of the query body.

Benefitting from built-in system queries

If you are interested, here is the complete statement of this query:

DECLARE @Factor as numeric(1,0)

SELECT @Factor = 
CASE (SELECT TOP 1 DispPosDeb FROM OADM)
 WHEN 'N' THEN 1 ELSE -1
END 

SELECT T0.CardCode,
T0.CardName,
T0.Balance,
T0.CreditLine,
(T0.CreditLine + @Factor*T0.Balance) "Deviation"

FROM OCRD T0
WHERE (select T0.CreditLine + @Factor*T0.Balance) < 0 AND T0.CardType = 'C'

If you come across any difficulties in creating your query later, you can always come back to system queries to find some useful tips.

Summary

In this chapter, you have learned about the following tools for creating or using SQL queries in SAP Business One:

  • Query Generator
  • Query wizard
  • System queries

You have also learned the differences between Query generator and Query wizard. This is a good start for your progress in mastering SQL Queries for SAP Business One.

By practicing these tools, you could have better understanding of the tables and table relationship within SAP Business One. Some of the system queries will give you more hints than others regarding how to create the correct query.

If you are a beginner, you may need to try those tools quite a few times. Only when you feel comfortable in creating simple queries – without problems – with these tools, will you build a solid foundation to create more sophisticated queries. In order to benefit from Business Intelligence, it would be better to try to master these tools to as high a level as possible. To learn the details of each statement and the complicated syntax, continue to read the following chapters.

In the next chapter, you will learn about the Query Manager and all the commonly used statements and functions, one by one, and in detail.