Utilize the power of SQL queries to bring Business Intelligence to your small to medium-sized business
Who can benefit from using SQL Queries in SAP Business One?
There are many different groups of SAP Business One users who may need this tool.
To my knowledge, there is no standard organization chart for Small and Midsized enterprises. Most of them are different. You may often find one person that handles more than one role.
You may check the following list to see if anything applies to you:
- Do you need to check specific sales results over certain time periods, for certain areas or certain customers?
- Do you want to know who the top vendors from certain locations for certain materials are?
- Do you have dynamic updated version of your sales force performance in real time?
- Do you often check if approval procedures are exactly matching your expectations?
- Have you tried to start building your SQL query but could not get it done properly?
- Have you experienced writing SQL query but the results are not always correct or up to your expectations?
If you are an SAP Business One consultant, you have probably mastered SQL query already. However, if that is not the case, this would be a great help to extend your consulting power. It will probably become a mandatory skill in the future that any SAP Business One consultant should be able to use SQL query.
If you are an SAP Business One add-on developer, these skills will be good additions to your capabilities. You may find this useful even in some other development work like coding or programming. Very often you need to embed SQL query to your codes to complete your Software Development Kit (SDK) project.
SAP Business One end user
If you are simply a normal SAP Business One end user, you may need this more. This is because SQL query usage is best applied for the companies who have SAP Business One live data. Only you as the end users know better than anyone else what you are looking for to make Business Intelligence a daily routine job. It is very important for you to have an ability to create a query report so that you can map your requirement by query in a timely manner.
SQL query and related terms
Before going into the details of SQL query, I would like to briefly introduce some basic database concepts because SQL is a database language for managing data in Relational Database Management Systems (RDBMS).
RDBMS is a Database Management System that is based on the relation model. Relational here is a key word for RDBMS. You will find that data is stored in the form of Tables and the relationship among the data is also stored in the form of tables for RDBMS.
Table is a key component within a database. One table or a group of tables represent one kind of data. For example, table OSLP within SAP Business One holds all Sales Employee Data. Tables are two-dimensional data storage place holders. You need to be familiar with their usage and their relationships with each other. If you are familiar with Microsoft Excel, the worksheet in Excel is a kind of two-dimensional table.
Table is also one of the most often used concepts. Relationships between each table may be more important than tables themselves because without relation, nothing could be of any value. One important function within SAP Business One is allowing User Defined Table (UDT). All UDTs start with “@”.
A field is the lowest unit holding data within a table. A table can have many fields. It is also called a column. Field and column are interchangeable. A table is comprised of records, and all records have the same structure with specific fields. One important concept in SAP Business One is User Defined Field (UDF). All UDFs start with U_.
SQL is often referred to as Structured Query Language. It is pronounced as S-Q-L or as the word “Sequel”. There are many different revisions and extensions of SQL. The current revision is SQL: 2008, and the first major revision is SQL-92. Most of SQL extensions are built on top of SQL-92.
Since SAP Business One is built on Microsoft SQL Server database, SQL here means Transact-SQL or T-SQL in brief. It is a Microsoft’s/Sybase’s extension of general meaning for SQL.
Subsets of SQL
There are three main subsets of the SQL language:
- Data Control Language (DCL)
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
Each set of the SQL language has a special purpose:
- DCL is used to control access to data in a database such as to grant or revoke specified users’ rights to perform specified tasks.
- DDL is used to define data structures such as to create, alter, or drop tables.
- DML is used to retrieve and manipulate data in the table such as to insert, delete, and update data. Select, however, becomes a special statement belonging to this subset even though it is a read-only command that will not manipulate data at all.
Query is the most common operation in SQL. It could refer to all three SQL subsets.
You have to understand the risks of running any Add, Delete, or Update queries that could potentially alter system tables even if they are User Defined Fields. Only SELECT query is legitimate for SAP Business One system table.
In order to create working SQL queries, you not only need to know how to write it, but also need to have a clear view regarding the relationship between tables and where to find the information required. As you know, SAP Business One is built on Microsoft SQL Server. Data dictionary is a great tool for creating SQL queries. Before we start, a good Data Dictionary is essential for the database. Fortunately, there is a very good reference called SAP Business One Database Tables Reference readily available through SAP Business One SDK help Centre. You can find the details in the following section.
SAP Business One—Database tables reference
The database tables reference file named REFDB.CHM is the one we are looking for. SDK is usually installed on the same server as the SAP Business One database server. Normally, the file path is: X:Program FilesSAPSAP Business One SDKHelp. Here, “X” means the drive where your SAP Business One SDK is installed. The help file looks like this:
In this help file, we will find the same categories as the SAP Business One menu with all 11 modules. The tables related to each module are listed one by one. There are tree structures in the help file if the header tables have row tables. Each table provides a list of all the fields in the table along with their description, type, size, related tables, default value, and constraints.
Naming convention of tables for SAP Business One
To help you understand the previous mentioned data dictionary quickly, we will be going through the naming conventions for the table in SAP Business One.
Three letter words
Most tables for SAP Business One have four letters. The only exceptions are numberending tables, if the numbers are greater than nine. Those tables will have five letters. To understand table names easily, there is a three letter abbreviation in SAP Business One. Some of the commonly used abbreviations are listed as follows:
- ADM: Administration
- ATC: Attachments
- CPR: Contact Persons
- CRD: Business Partners
- DLN: Delivery Notes
- HEM: Employees
- INV: Sales Invoices
- ITM: Items
- ITT: Product Trees (Bill of Materials)
- OPR: Sales Opportunities
- PCH: Purchase Invoices
- PDN: Goods Receipt PO
- POR: Purchase Orders
- QUT: Sales Quotations
- RDR: Sales Orders
- RIN: Sales Credit Notes
- RPC: Purchase Credit Notes
- SLP: Sales Employees
- USR: Users
- WOR: Production Orders
- WTR: Stock Transfers