Creating Queries

• Queries enable you to quickly display and format data from an SAP Business One company database. You can use queries in several ways, for example:
  • To produce ad hoc reports that can be run over and over again. These are known as User Queries.
  • Create user-defined alerts and approval processes in SAP Business One. The query can check for conditions that are not covered by predefined alerts or standard approval procedures.
  • Populate the contents of a field, including user-defined fields. The query is added to the field as user-defined values (formatted searches) and can populate the field value from the query results.
  • To show dynamic information for analytics in dashboards and KPIs
  • To validate the values imported into a table or field during data migration.
• You should understand that the query tools are not designed to produce complex, fully formatted reports or print layouts; you should use Crystal Reports for these purposes. And you are not allowed to use the query tools to insert, update or delete standard table fields.

In order to run queries you need to know the names of the SAP Business One tables and fields. This information is displayed when you enable System Information.

An object can span multiple tables in the SAP Business One database.

For example, the business partner master data object is held in multiple tables in the database, including the header table OCRD. There are related child tables, such as OCPR, CRD1, OSLP, etc. These child tables hold the data that you see on the various tabs of the business partner record.

In SAP Business One table names are 4 characters long.

To develop a SQL query you need to know the database table and field name for selection.

When you open an object in SAP Business One, you can find out the table name for a field by enabling System Information from the View menu. You can also use the keyboard shortcut Ctrl + Shift + I.

Then, when you hold your mouse over a field in the object, the relevant table information for the field is shown in the status bar at the bottom of the window.

You now have the table name and field name to enter in your SQL query.

Other information about the field is displayed, such as the maximum length of the field.

The business partner master data object is shown in the slide. If you hover over the Name field you will see that the table is OCRD and the field name is CardName.

Sales and purchasing documents, such as orders, quotations, and invoices, all have the same structure and are based on two main objects:

• The Documents object for the header parts of the document
• The Document_Lines object for the matrix or row section of the document

A document type, such as a sales order, spans multiple tables. The tables will be different for each document type, so that the set of tables for a purchase order is different from the set of tables for a sales order.

For example, the sales order uses the database table ORDR for the header and the table RDR1 for the rows.

• The system information also shows, in addition to the table and field name, the item number for a header field, and the item, column and row number for a row-based field.
• For example, in a sales order, the item number for the CardCode field is 4. The column number for the CardCode field is not displayed, since this is a header field in table ORDR and the column number for all header fields is 0.
• If you open other marketing documents, you will see that the CardCode field is always 4.
• Why is this important? The item number for a field is common across all document types that have the same structure, such as sales and purchasing documents. In these documents, the item numbers are the same, but the table names are different. Using the item number instead of the field name allows you to write queries that can work across multiple document types, by referencing the same item number.
• In the lower screenshot on the slide, you can see that the ItemCode field in the sales order document matrix has item number 38 and column number 1. The row number 1 denotes the first row in the document (that is, row numbers start at 1).
• You will notice that the item and column number for a row field is the same across similar document types, for example, the ItemCode has item number 38 and column number 1 in all sales and purchasing documents.

• You will find that the table and field name is not displayed for some fields, including the unit price and calculated fields such as totals and taxes. These fields are shown in the document concatenated with the currency symbol, whereas in the database the amount is stored without the currency symbol.
• You can still use these fields in a query. You can use the item number to refer to the field, or you can get the database field name from the Database Tables Reference.

You can find the Database Tables Reference file REFDB in the SAP Business One SDK > Help folder, or, if you have installed the installed Data Transfer Workbench you can open it from the Help menu.

In addition to the field names for an object, the reference file provides:

• The description
• The type of field (for example, Int, VarChar, Numeric, Text, etc.).
• The maximum length of a field
• If the field is a foreign key there is a link to the related table
• A default value if one exists
• Constraints on the field values

SAP provides two query tools assist you in developing the query syntax.

• Tools for creating and managing user queries are located under the Tools menu in the top menu bar of the client application.
• SAP Business One provides two tools, the Query Generator and the Query Wizard to assist you in creating queries using the structured query language (SQL). SQL is a standardized set of commands for accessing and formatting data in relational databases. Both tools produce the same results in the end, and it is mainly a matter of your own preference as to which tool you use.

• As its name suggests, the Query Wizard guides you step-by-step through the process of creating a query without you having to write SQL commands and syntax. You should use this tool if you do not have much experience with SQL. The query wizard is a multi-step wizard.
• To run a query from the query wizard, in the final step choose the Finish button.
• The generated query and the results of the query appear in the query preview window.

• In the first step of the wizard you press Tab to view the list of database tables and select the required table (related tables are shown and can be selected).
• Then in the next step you press Tab to view and select the fields. All fields from the table are shown and you can enter partial names in the Find field. Double-click a field to select it for the query. Select each field on a separate row in the wizard step.
• The system generates the SQL statements in the background so you do not require precise SQL syntax knowledge. However, because this is done by a wizard it takes several steps to produce the query.
• You will find the Query Wizard easier to use if you do not have much familiarity with SAP Business One tables and how they are related to each other. When you select a table, the Query Wizard will show you all tables that are related to your chosen table, allowing you to select data from multiple tables. If you select multiple tables for your query, the wizard will take care of the table joins.
• The generated query syntax is shown in the slide.

• The second tool, the Query Generator, allows you to create the SQL in a single screen. If you have some basic SQL knowledge, you will find the Query Generator much faster than the Query Wizard.
• You select the tables and then the fields, and the query is shown in the right-hand side of the window.
• To run the assembled query in the query generator, choose the Execute button.
• The query results appear in the query preview window.

• To generate the query, enter the table name in the text box in the top-left of the window, or press Tab in this box to see the complete list of tables.
• Press Tab again to see a list of all fields in the table. Double-click to select each field from the displayed list. The elements of the query are built and shown on the right-hand side of the tool window.
• Like the Query Wizard, the Query Generator will automatically supply the inner join when you select more than one table for a query. Although it does not show you the list of tables related to your chosen table, fields that are keys to related tables are shown in bold. You can drag and drop the bolded field to the table selection column and the Query Generator will open the related table, allowing you to select fields from that table.

The Query Preview window displays the query syntax and the results

The SQL syntax generated by the in-built query tools depends on the underlying database, either SQL Server database or SAP HANA. However, the results will be the same.

From the query preview window you have the option to edit the built query. Click the "pencil" icon to edit the query syntax. In edit mode you can write and run your own query from scratch.

The query preview window also allows you to save queries for reuse. This will be discussed later in this course.

The two query tools assist you in developing the query, but you also need some basic understanding of the elements that make up the SQL query syntax.

A query, or the underlying SQL statement, contains one or more of the basic elements listed on the slide:

• Selection of fields. You can also specify calculation fields that display the result of an addition, subtraction, multiplication, or division of two fields.
• Conditions for selection (where clause)
• Sort order (order by clause)
• Grouping and summarizing (group by clause)

The sample query shown in the top right part of the screen will show information for open purchase orders that have been added in the last week. The query selects data from the purchase order table OPOR. The results of the query, a simple snapshot from the database, is shown. The total of the rows is not shown, but you can add this to the report by holding the Ctrl key and clicking twice with your mouse.

Note that SQL is not case sensitive, therefore the commands do not have to be upper case. For table names and field names that contain lower case letters, in SAP HANA syntax you are required to add double quotation marks around the field names.

All query examples shown in this course are for SAP HANA. For a list of differences in SQL syntax for SAP HANA, see the document Best Practices of SQL in the SAP HANA Database

• The optional Where clause lets you filter only records that meet specified criteria. Although the query tools will help you assemble the basic elements of the query, you will need some knowledge of SQL syntax to complete the Where clause.
• In the Where clause you can include:
  • Fixed conditions as comparisons. For example, in our sample query, only purchase orders that are open (DocStatus 'O') will be displayed.
  • Calculations and functions. In the sample query, we have added a calculation to include only purchase orders that were posted in the last 7 days. We match the posting date value (DocDate) to the current date - 7. The function ADD_DAYS is used to calculate the current date minus 7.
  • Variables. Variables are specified as [%0], [%1], [%2], etc. When you include a variable, the user will be prompted to enter a value as a parameter when the query runs.
• AND and OR operators can be used to link multiple conditions. In the sample query we use the AND operator since both criteria must be met.

Note: The sample query shown uses the DocStatus field as criteria. In the database this field has a fixed list of permitted values (constraints). To find out the possible values for a field such as DocStatus, you have two options:

• Run a query on the table and select the field name. In the results you will see the possible values that are already stored in the database.
• See the Database Tables Reference for a list of the allowed values (constraints).

• Variables give the user the flexibility to change the parameters when they run the query.
• Variables are simply defined in square brackets with %0 as the first variable, %1 the second variable, and so on.
• In the example, the posting date DocDate is used as a filter but we have added a variable condition. When the user runs the query they are prompted to enter the posting date. The date entered by the user is used in the Where clause and compared with the posting date from the purchase order. Only record where the posting data is greater than the parameter date will be included in the query results.

• You can optionally sort the query results by adding the Order By clause to the query.
• The results will be sorted by default in ascending sequence (ASC) using the specified field. In this example the rows of results (purchase orders) will be sorted by posting date (DocDate).
• You can sort by descending sequence by adding the keyword DESC
• You can sort by multiple fields, and these fields can be either part of the select clause or other fields in any of the tables of the query.

• The optional Group By clause allows you to display the query results grouped or summarized by a specified field, for example, by business partner. In our example, the original query has been rewritten to use the Group By element and is shown beside the original query on the slide.
• The grouped results are collected into sets using the Group By field or fields as the common value.
• Group by is usually used in conjunction with a mathematical function such as Count or Sum. In the sample query the Count function counts the number of POs and the SUM function calculates the document total from each PO.
• The selected fields are consolidated according to the fields specified in the Group by clause, so in the sample query the results are consolidated by vendor (CardCode), and the query shows one consolidated row for each vendor with the count and the total amount of all open purchase orders for the vendor.
• Notice that we need to supply the column headings for the fields that are counted and summed, since the column headings for these fields are not in the database.
• When using the Group by clause, fields that you use in the Select statement must also appear in either the Group By clause or the aggregate function (Count or SUM). For this reason, we have specified the CardName field in the Group by clause.

• When you specify more than one table in your query, you normally need to create joins between each table. The Query Wizard and Query Generator tools make this easy for you by automatically supplying the inner join when you select more than one table for a query. For example, if you select the purchase orders table (OPOR) and also select the business partners master data table (OCRD), the query tools will link these tables using the business partner code which is common to both tables.
• The query tools automatically add an alias for each table, for example, T0, T1, etc. If the query references more than one table this helps you to identify the relevant table for a field.
• When you select a table, the Query Wizard will show you all tables that are related to your chosen table. In the Query Generator, fields that are keys to related tables are shown in bold. You can drag and drop the bolded field to the table selection column and the Query Generator will open the related table, allowing you to select fields from that table.

When a query runs, headings for the report columns are taken from the database column names. To change a column heading in the query report:

• In the Query Wizard, simply type in the desired text in the Heading column of the wizard screen.
• In the Query Generator, use the "as" keyword followed by the new heading text in double quotes. Enter this text directly in the Select box.

• In many situations, the query needs to reference fields in the active window. In the example, the active window is identified by a blue line on the top of the form or document.
• This is true for queries used in approval procedures and with user-defined values. In these two situations the query runs while a document is being processed by the user in the active window.
• If a query needs to refer to a field in the active window instead of from the database, you must include a $ sign and place square brackets around the table and field name to indicate the field is in the active window.
• In the example shown, you have added a query as user-defined values to a field in the sales order. The query will fetch the customer's account balance from the master data record and show the balance in a user-defined field in the sales order. The sales order is the active window because the user is working on it, therefore the reference to CardCode includes the special syntax. The query matches the CardCode from the active window to the CardCode in the master data.
• The master data record in this example is not in the active window. Fields that are accessed from the database, such as the balance field from the master data record, do not need the $ sign.
• For more information on queries and approval processes, see the Approval Processes course. For more information on queries and user-defined values, see the User-Defined Values course.

• When you reference a field in the active window, you can use either the table and field name syntax or the item and column number syntax.
• In the slide is an example of a query used in an approval process. If you use the table and field name syntax for the active window, the query includes the table name so can only be used with a single document type. In this case the query can only be run to approve purchase orders since the OPOR table is explicitly included in the query.

Note: Queries used in approval processes always need to select a true result. In SAP HANA SQL, if there is no FROM clause in the statement, use FROM DUMMY.

• If you instead use the item number and column syntax, the query can be used with multiple document types of a similar structure. For example, this query can be used in an approval process where multiple sales and purchasing marketing documents are selected in the approval template.
• The system uniquely identifies each field of a document using the field's index and column number.
• When you reference a header field using the item and column syntax, set the column number as 0.
• The field is fetched as a string, so if you need a different format you need to specify the format after the column number:
  • 0 - which is the default string format
  • Number - which returns the result as a numeric that you can use in calculation or comparison operators. In the slide example, the result is compared to 500. Since the DocTotal field holds both the amount and the currency symbol, specifying numeric here will extract the amount.
  • Currency - which returns the currency symbol from a field that holds both the amount and the currency symbol, such as the DocTotal.
  • Date - which should only be used if the field is a date field. The date is retuned in a format for use in calculation or comparison operators.

After you create a query, you may want to save it for reuse.

• You can save a query from the query preview window. When you save a query you must select a category. The General category is provided for you, but if you plan on adding many user queries you should create additional categories to manage the queries.
• Queries that you save are located as User Queries. To run a saved user query, choose the Tools menu in SAP Business One, and then select the category and the query name.

• As the name implies, the Query Manager allows you to manage user queries that you save.
• You can save and organize queries by category. You can also delete queries.
• The System category is for SAP supplied queries. The General category is provided for you to save user queries, and you can create additional categories.
• To create a new category, choose the Manage Categories button. Then choose Add to add the new category and save your query. In the example here we will create a new category called Purchasing.

• When you create a new category, you must assign it to at least one query authorization group, or you will not be able to save the new category.
• Only users given access to a query authorization group can run the queries saved for the category. This provides a way to control which users can access and run the saved user query.

Let us look at the example provided here:

• You can see that query authorization groups 1 and 2 are assigned to the query category Sales. Query authorization groups 2 and 3 are assigned to the category Purchasing.
• Users Bill and Donna are assigned to query authorization group 1.
• Users Sophie and Tim are assigned to query authorization group 2.
• Julie and Juan are assigned to query authorization group 3.
• So, Bill and Donna can run all queries saved in the Sales category through its association with query authorization group 1. Sophie and Tim can run all queries saved in both the Sales and Purchasing categories because both categories are associated with query authorization group 2. And Julie and Juan can run all queries saved in the Purchasing category which is associated with query authorization group 3.

A user is granted authorization to a query authorization group in the General Authorizations window. You can find this authorization under Reports > Query Generator. You can see 15 query authorizations - Saved Queries - Group No 1 -15. These are provided for you out-of-box. You can create additional query authorization groups in the Query Manager.

In the slide example, the user bob is granted permission to query authorization groups 1 and 2 at read only. The same user is also granted full authorization to the group called "User queries for Alerts", which is a new authorization group that has been added to the list.

Since authorization is granted at the category level, the user bob can run any query that is saved in a category that is associated with one of these selected authorization groups.

If you want you secure access to saved user queries you need to carefully plan query categories and query authorization groups.

Be aware that query authorization groups are not the same as the authorization user groups used to assign general authorizations to multiple users. To learn more about general authorizations, see the related course General Authorizations and User Groups. You can also assign the authorization simultaneously to multiple users from a User Group.

New users, unless they are super users, have no authorization to create new user queries or query categories. This is designed to protect information in the database. The required authorizations are:

• New Queries: create new queries using the Query Generator
• Create/Edit Categories: create and edit categories in the Query Manager
• Query Wizard: use the query tool
• Query Manager: use Query Manager to manage saved queries
• Report Scheduling: schedule a saved query as a report

Note: To view dashboards and interactive views in SAP HANA, authorizations must be set for the Analytics subject area.

Here are some key points to take away from this session. Please take a minute to review these key points:

• SQL queries can be used to produce ad hoc reports, as the first step to design a more complicated report, with user alerts and approval procedures, and during the implementation project to validate migrated data imported into tables.
• System information can help identify table and field names, or item and column numbers. Use the View > System information command to enable the system information display.
• Two tools exist to help you create SQL queries - the Query Wizard and the Query Generator. Try both tools to see which you prefer.
• You can save queries as user queries and organize them by category. The query Manager allows you to organize and manage saved user queries.
• Users need authorization to run saved user queries. First you select a query authorization group for the category in which the query was saved, then assign a user to the category's query authorization group in the general authorizations window.
• Special syntax is used for queries that run while a document is being processed in the active window. This applies mainly to queries used in approval processes.