SAP Business ONE reporting tools "hands-on"
Now, let’s finally log in to SAP and run some reports. The simplest form of a report in SAP is a plain query that is saved with a title. This can be run at any time in the system. Therefore, if you know how to design a query, then this is a fast and simple solution. In case you do not know how to create a query, let me show you how. I am explaining this to ensure you are making good use of your SQL skills when using SAP Business. In most business solutions, it may be hard to find the right tables and columns to run a query. In SAP Business ONE, you can switch on System Information via View | System Information. Then, as you browse with the mouse over fields in a form, the table name will be displayed in the footer section of the SAP Business ONE interface.
I have added a screenshot that shows the table name and column name for the Business Partner Master record. You can see that the cursor is in the Code section. In the footer, it shows OCRD, CardCode. This means the information is in the OCRD table, and the column for Code is CardCode in the database table level:
I have created a simple query (as seen in the following screenshot) that shows all of the business partners. You can fine-tune this even more based on your requirements. If you know how to run SQL queries, then please continue your own adventure with this area. I recommend creating views from complex queries in the SQL Server, and then use simple queries against those views in SAP.
If you do not understand this, you may ask for another way to run reports appropriate for mere mortals without an SQL background. Well, SAP Business ONE has a concept for this too.
SQL for managers
SAP offers two additional ways to create SQL queries using wizard-type tools: Query Generator and Query Wizard. The Query Generator works at the table level and helps you create a query, including conditions. The Query Wizard is of a higher level and allows tables and columns to be selected on a business context level by providing more descriptive information about each table. You should experiment with both the Query Generator and the Query Wizard to find out what works best for you. I believe that these tools are a great teaching aid when learning how to create queries. You can first attempt to create the query on your own. If you encounter problems, use the Query Wizard and see what the query should look like in order to work.
Open the Query Generator (seen in the following screenshot) and enter OCRD in the left section. If you remember, this was the table name provided for business partners. Now, what if we only want a report with business partners from a specific zip code? Well, you hit the Tab key once you enter OCRD in the left section. Based on this information, SAP displays the columns available in this table in the next section on the right side. Now place the cursor in the Select section in the upper-right white field. Then click on CardCode and CardName. SAP automatically adds the names here. Next, place the cursor in the Where section and click on the column which will provide the Where criteria. I selected ZipCode. However, I want to enter the zip code dynamically and not create a new report for every zip code. Therefore, I clicked on the Conditions button. This opens a side panel on the right. I clicked on Equal and [%0]. This will ensure that the SAP system will ask for a zip code value every time we run this report.
I saved the report as Business Partners by ZIP and I will now run it. A small window opens, which provides an entry field for the ZIP parameters:
Once a zip code is entered, the query runs and returns the result. In the result window, the underlying query that produced the result is always displayed. Take a look at the following screenshot:
By using this method, it is possible to create pretty sophisticated queries and reports without advanced SQL knowledge. In addition, by investigating the generated queries, you can easily increase your SQL knowledge if you like. In case you do not want that, then I recommend looking at the other available reporting options such as the Query Wizard.
Although the Query Generator was a good help, when it came to generating a query that required parameters, the Query Wizard helps us in identifying related data in different tables. When a database is defined, not all data is saved in a single table. I would like to provide a simple example that showcases this by using a query against the item table called OITM. The OITM table has all of the information about all items. However, it only has the Item Group code expressed as an id number called ItmsGrpCod, and we do not get the Group Name with a simple query. We need to find the related table that has the Group Code and the Group Name. Let's activate the System Information feature and open the Item Master Data form in the Inventory menu. With previously described method, move the cursor over the form and find the table name OITM. Next, open the Query Wizard and enter OITM in the first column.
In case you are not sure which table to use, you can also hit the Tab key with the cursor in the empty Table box and all of the available tables will show up along with an explanation of the relevant table. We entered OITM directly into the first row and the Description is updated with the value Items, indicating that we correctly entered a valid table name and that it represents item information. Now, the trick is to select the first row in the Table column and click on OITM again. This will display the related tables in the lower section (as seen in the following screenshot). We will select Item Groups. In other ERP systems, you may need to request for technical documentation to get this type of information.
The next step is to select the columns that we want to match and display. Since it is our goal to get a descriptive Group Name and not a number-like Group Code, I am using the common column ItemCode to match the two tables and display the Group Name. For the purpose of the example, I am displaying the Group Code twice. The first value comes from the Items table and the second comes from the Items Group table. You will see in the query result that they match up:
In the next screen of the wizard, I will now match the column as shown in the following screenshot. This is a visual guide for designing an SQL query. If you have ever studied SQL and forgot some of the techniques, this wizard will refresh your memory. As a matter of fact, you do not need to know SQL. However, the resulting query will be presented once you are done. In addition, the process of first selecting the tables and then specifying the conditions and relations will help you understand the required steps to actually design an SQL query. Therefore, if you have technical personnel willing to do some of the reporting themselves, they can easily get started with the SAP Query Generator and Query Wizard:
The final query is presented. When confirming with the Finish button (seen in the following screenshot), the query is executed and we can see the result. Do not forget to save this query. I saved it as Items_Groupname.
Drag and relate
As you can see, there are many ways of reporting and it is up to you to choose the appropriate tool. In case you are on the phone with a customer and need a report that is not available in SAP Business ONE, you may also use the so-called drag and relate functionality. This is somewhat groundbreaking because you can use the form interface elements and drag them against common business objects to see related information. Sounds complicated again? I am using the term business objects on purpose to make you think in terms of objects that hold a specific type of information. Examples of such objects can be found when actually using the drag and relate functionality.
The basis of the Drag and Relate functionality is an open form in SAP Business ONE. I will open the item master for you and select the Lemonade Basic product. This is our only finished good. When I click on the Drag & Relate tab page in the Main Menu, all of the available business objects show up.
If you want to quickly see any invoices that were issued against this item, open the Sales – A/R business object. Click on Item Number and hold the mouse button. The box with the Item Number then becomes a floating box and you can drag it over the A/R Invoice business object. When you release the mouse, you will get an immediate drag and relate report about the invoices related to the selected item.
Look at the following screenshot to see how it is done:
When should you use the drag and relate information? I usually use this to obtain information in a system that has no specific reports set up for the information I require. In addition, if the information you are looking for is a one-time lookup type, you may not want to create a query and save it. You may just drag and relate quickly and move on once you have the information you need.
The Drag and Relate concept also allows you to obtain information based on open data in a marketing document. When dragging the fields, you get immediate information about related data for the particular document. You do not need to run a lengthy report where you need to enter parameters and then analyze the results.
Another example is a situation where you have a customer on the phone and you want to quickly see the related transactions for this customer as you talk to them.
A drag and relate activity cannot be saved though. It is strictly ad hoc style. If you think about it, this concept basically transits the SAP Business ONE system into a dashboard, where every form can be immediately reported on within the context of a specific data set.
A bit of caution is advised for drag and relate if your database is large. In such a case, a simple drag and relate could generate a very large query which will take a long time to complete and slow the system down.
Alert system
In SAP Business ONE, you can define alerts. An alert is a business event that meets a certain defined criteria. An example of an event could be that a salesperson exceeded the maximum discount allowed. You can define an alert which will notify the manager of the salesperson if such a thing happens. Another example is a situation where a customer orders items that exceed his or her credit limit. You can then get an alert and discuss with the customer the option to prepay certain parts of the open orders.
As you can see, the alert system is a bit like a workflow trigger system. You can trigger business processes, such as order verification, by using alerts. This will help keep your business in line with your policies and make sure that there are no surprises in the key areas.
There are two types of alerts: pre-defined alerts and user-defined alerts. Pre-defined alerts are already defined in the SAP Business ONE system. However, you can create any type of alert that complies with your individual business rules. These alerts would be user-defined alerts.
Technically, an alert is a simple query that checks that the business rules are met. For example, we may create a query to check the maximum number of orders. If the maximum number of orders is exceeded, then the query returns a result and the alert would notify the assigned employee.
With the knowledge obtained in the previous chapters, you now know that you first identify the business objects involved in this business rule. You then identify the relevant tables, and finally specify the conditions. Therefore, you can use the previously explained query tools to develop the query, and then assign the query to an alert.
At this point, let's focus on the pre-defined alerts that come with SAP Business ONE. You can find them by navigating to Administration | Alerts Management. Enter * in the Name field and hit Enter. You will then get the list of pre-defined alerts. I am selecting the Deviation from Discount (in %) alert. This alert, as the name suggests, will notify the selected user if the discount granted in a marketing document exceeds a set limit.
In the following screenshot, it can be seen that I set the discount percentage to 25%. The Documents tab page allows us to easily assign the documents that we will monitor using this alert.
See how the discount can be set based on your requirements.
Using the Documents tab page, you can set the documents which will be monitored by this alert:
One final step is required to activate the alert system. Go to Administration | System Initialization | General Settings and then select the Services tab page. This is where you can configure the way the alerts will work in the system.
Two crucial settings must be specified: Send Alert for Activities Scheduled for Today and Display Inbox When New Message Arrives. These settings, along with the Update Message (Min.) parameter, will get this going. I set the update to 5 Minutes. This means the system will check every five minutes if the alert criteria is met and will then inform the designated user about that.
How does this work under the surface? Since the alert system uses pre-defined and custom queries to identify if the criteria for the alert are met, there must be a query hidden somewhere in the system. Let's try to find it. Open the Alerts Management window, and while System Information is activated, browse over Query and note that the table is called OUQR. When you select this table using the Query Wizard, you will see that there is a query called Locate Exceptional Discount in Invoice. You can copy the QString column and you will have the underlying query for this alert. Why would you do that? Well, if you have a requirement that is similar to an existing alert, but not quite the same, you can use this method to get started with a query and then make adjustments to fit your needs.
As you can see, the alert system is a workflow-concept based on queries and custom criteria that reflect your business rules.
At this point, it has become clear that reporting in SAP Business ONE goes through all levels of expertise and addresses the needs of managers and technical personnel. I have even explained features such as drag and relate and alerts that do not immediately look like a reporting tool. However, a closer look did uncover that most SAP Business ONE features are on some level based on key reporting concepts and technologies.