Chapter 1. Getting Started with the SAP R/3 Query Reporting Tools
In this chapter
The SAP R/3 Query Reporting Tools 2
How the SAP R/3 Query Tools Work 4
Comparing the Query Tools to Decide Which to Use 8
The reporting attributes, objects, and tools discussed in this book apply equally to both the SAP R/3 and mySAP ERP solutions, which this book refers to collectively as the SAP enterprise solution. Your SAP enterprise solution is delivered with standard query reporting tools that end users can use to create their own reports, without needing any technical programming skills. These tools provide functionality that many SAP customers do not know is possible.
Having the ability to extract and report on data from the SAP enterprise solution is empowering for you as an end user because it means you will not have to rely on others to gain access to the information you need in order to make business decisions. A common complaint I hear from SAP enterprise solution users is about their inability to get access to the data they need and their reliance on others to provide it for them. This book explains everything you need to know to work with the SAP enterprise solution reporting tools so that you can have access to the information you need.
The challenge most SAP users face is the unavailability of training materials specific to end-user reporting. Many SAP customers are under the mistaken impression that R/3 reporting is accomplished via four main avenues:
• Standard SAP-delivered “canned” reports.
• Reports created by trained programmers via Advanced Business Application Programming (ABAP). (ABAP is a high-level programming language created by SAP.)
• A purchased SAP add-on solution such as Business Warehouse (BW), soon to be known as SAP NetWeaver Business Intelligence (or BI or BIW).
• A purchased non-SAP third-party add-on solution (such as Crystal Reports).
The SAP R/3 Query Reporting Tools
In addition to these four main avenues, SAP comes preinstalled with the following end-user reporting tools:
• SAP Query
• InfoSet (Ad Hoc) Query
• QuickViewer
The added bonus of these solutions is overwhelming. Instead of using standard canned reports, you can use these tools to create your own reports from scratch, selecting the fields, formatting, and so on. Unlike reports created via ABAP programming, reports created with these tools require no programming skills whatsoever. Many customers assume that BW (or BI or BIW) is the reporting solution designed for all reporting in SAP. BW is a great tool for customers who require it, but many people do not understand several facts about BW:
• You have to purchase, license, and install BW.
• BW is not part of your R/3 environment and therefore requires separate configuration.
• BW does not provide real-time reporting; rather, it reports from a storage warehouse of data.
• BW is designed primarily for SAP installations where reporting from multiple non-SAP solutions as well as SAP is required.
If the SAP R/3 Query tools are so useful, why aren’t they more popular? Let’s start with the history. The earliest versions of SAP offered a tool originally called the ABAP/4 Query tool. This tool was the first one designed to allow end users to create their own reports. It was rudimentary in its first deployment, and early on it received a bad reputation for being a resource hog on the SAP R/3 database. Nevertheless, over the years and following releases, SAP continued to work with the updated ABAP Query tool to improve its indexing, speed up its retrieval methods, and encourage the use of standard delivered logical databases as the data source behind the reporting engine. In SAP 4.6, the tool was drastically improved, and by then it was no longer considered a resource hog.
About the same time that the ABAP Query tool was improved, SAP stopped offering training classes on the query-based reporting tools, even though the SAP Query tool had just been enhanced and updated and the new query-based tools, including InfoSet Query and QuickViewer, had just been introduced. Some think that SAP wanted to encourage its SAP customers to purchase and install BW (a new revenue generator), and others believe that SAP simply wanted to make a commitment to its custom ABAP coding for reports, which is a popular class in the SAP training catalog. What the motivation was is unimportant; what is pressing is that reporting tools are available to end users for the creation of custom reports that require no advanced technical training or programming capabilities. Everything you need to know about how to configure and use these tools is included in this book.
Note
The SAP R/3 Query tools are SAP Query, QuickViewer, and InfoSet (Ad Hoc) Query. When I refer to the SAP Query tool, I am referring to the individual SAP Query tool, and when I refer to all three of the tools, I use the term SAP R/3 Query tools. This may appear a bit confusing at first, but you will get the hang of it as you move along.
Detailed information, especially training materials, for the three SAP R/3 Query tools is hard to find. Even harder to find is a comprehensive comparison of what these tools offer, their configuration and usage considerations, and how they rank comparatively. The following sections introduce the three SAP R/3 Query reporting tools; later in the book, you will learn more about each tool so you can compare and contrast them yourself.
The SAP Query Tool
The SAP Query tool, known as the ABAP Query tool in earlier versions of SAP, is delivered with the SAP R/3 system. End users can use this tool to quickly and easily create reports from data stored in the SAP R/3 database. This tool can be used in any application module in SAP. Its easy-to-use format simplifies the report creation process. The SAP Query tool includes basic and advanced features for every level of end user. The SAP Query tool offers a broad range of ways to define output and create different types of reports, such as basic lists, statistics, and ranked lists. This tool can be used in a basic or graphical mode.
The InfoSet (Ad Hoc) Query Tool
Unlike the SAP Query tool, which is a complete reporting solution, the InfoSet Query tool is designed for basic users to retrieve simple, single-use lists of SAP R/3 data. In SAP 4.6, the Human Capital Management module reporting tool called the Ad Hoc Query tool was combined with the technology of the SAP Query tool and made available for all modules; its new name is the InfoSet Query tool (although it is still referred to as the Ad Hoc Query tool when executed for human resources reporting). This book refers to it as the InfoSet (Ad Hoc) Query tool.
Unlike with the SAP Query tool, all query information, including the selection criteria, for InfoSet Query tool reporting is available on a single screen. You can use the InfoSet (Ad Hoc) Query tool to quickly answer simple questions or to create a comprehensive report for printing or downloading to your PC. A user can use the InfoSet (Ad Hoc) Query tool to pose questions to the SAP system and receive real-time answers.
The QuickViewer Tool
The QuickViewer tool that is delivered with a SAP 4.6 system is a WYSIWYG (“what you see is what you get”) utility for quickly collecting data from an R/3 system. The QuickViewer tool is actually a variant of the robust SAP Query tool that is designed for new or occasional users or for single-use data inquiry reports. You can use this tool to create reports referred to as QuickViews. To define a report with the QuickViewer tool, you simply enter texts (titles) and select the fields and options that define the QuickView. QuickViews cannot be exchanged among users, but they can be converted to reports to be used with the SAP Query tool.
How the SAP R/3 Query Tools Work
Query tools are based on the foundation of the SAP R/3 database. Historically, the primary method of creating reports in SAP was by using the ABAP Workbench and writing code in the language of ABAP. Specially trained programmers could write a long series of lines of code in an ABAP editor to retrieve information from the database, compute relationships, configure security, design selection screens, and present data in a particular arrangement. The skill set for ABAP programming is challenging to learn and usually requires experience in each application area of SAP for which programming will occur. For example, an ABAP programmer with experience in the Financials module would require special training to change functions and create reports in the Sales and Distribution area. Because these programming skills are substantial, SAP created the Query family of tools so that end users could pick and choose the fields they want to include in their reports and, behind the scenes, SAP would handle all the technical details. Figure 1.1 is a diagram that shows the foundation of the SAP Query tools.
Figure 1.1. An overview of the technical aspects of the SAP R/3 Query tools.
As described in the following sections, the SAP R/3 Query tools—SAP Query, InfoSet (Ad Hoc) Query, and QuickViewer—are built on the foundation of four main components:
• Query areas
• Query groups
• InfoSets
• Administrative decisions (which are company-specific)
Query Areas
Query areas (known as application areas in versions of SAP earlier than 4.6) contain SAP Query elements, queries, InfoSets, and query groups. SAP has two distinct query areas:
• Standard—Standard query areas are client-specific. That is, by default, they are available only within the client in which they were created. For example, if they were created in the live production client, they would exist only in the production client. Transport of query objects created in the standard area can be accomplished between multiple clients on the same application server.
• Global—Queries designed in the global query area are used throughout the entire system and are client-independent. In version 4.6, SAP delivers many of its standard reports in the SAP Query global query area. These queries are also intended for transport into other systems and are connected to the ABAP Workbench.
Query Groups
Query groups were known as user groups in versions of SAP prior to version 4.6. A query group is a collection of SAP users who are grouped. A user’s assignment to a query group determines which queries he or she is allowed to execute or maintain. In addition, it designates which InfoSets (that is, data sources) the user has access to work with. Basically, query groups give a user access to create, modify, and execute reports in a certain area within R/3. For example, you could create a query group for the Finance department that would house your financial users, or you could create a query group for the Human Resources department that only members of the Human Resources department would belong to. Using query groups is an easy way to group and segregate reports and users.
Query groups, which are often maintained by a system administrator, are created on the User Groups: Initial screen, which you can find by using the transaction code /nSQ03.
Users can belong to multiple query groups and might, under certain circumstances, copy and execute queries from other query groups (if the permissions are the same). Any user within a query group has authority to execute queries that are assigned to it, but only users with the appropriate authorization can modify queries or define new ones. Users are not permitted to modify queries from other query groups.
InfoSets
InfoSets, known as functional areas in earlier versions of SAP, are areas that provide special views of logical databases and determine which fields of a logical database or data source can be evaluated in queries. That is, an InfoSet is basically the data source, from which you get data to use in reports.
InfoSets can be built on a variety of different sources, but the most common is the use of an SAP logical database. Remember that writing reports without Query tools requires a programmer to write code that goes into the main R/3 database and retrieves the records it needs, and that is no easy skill. SAP delivers logical databases, which are rational prearranged groupings of data from multiple related indexed tables. SAP places all the fields you want to report from in a nice container from which you simply select the fields you want to include in your report.
Administrative Decisions for Query Tool Use
Using any new tool in an SAP R/3 system requires that you plan ahead and make sure you have all your administrative bases covered before you dive in and start creating reports. Because the SAP tools are so easy to configure and use, you might be tempted to skip ahead in this book and begin creating reports. Although that is possible, it is a best practice to first review and make some administrative decisions before proceeding, as described in this section.
From the get-go, deciding which departments will have ownership of and responsibility for the different areas of the Query tools is imperative. Again, because the tools are so easy to configure and use, it is possible to configure the tools and begin creating reports without ensuring that you are following the desired strategic direction of the organization. Making sure you review the different options and recommended best practices in this section is crucial to your successful deployment of query-based reporting at your organization. The following sections use the most popular and most robust query solution, the SAP query, instead of continually referring to each of the query tools individually.
Administrative Decisions for Query Areas
In Chapter 2, “One-Time Configuration for Query Tool Use,” you will learn how to do the behind-the-scenes configuration for the SAP R/3 Query tools. This configuration is very easy to perform, and it is important that you first decide ownership and rules for the different query items.
One important administrative decision is how to use query areas. In version 4.6, SAP began to distribute its standard R/3 canned reports in the global query area. Those reports are now automatically available to every client on the application server because they exist in the global query area. A common best practice is to allow SAP to continue to deliver reports via the global area and for end users to use the standard query area to create all query-related reports. Reports created in this standard area are client-specific and by default exist only within the client in which they are created. However, they can be transported to other clients via the transport truck on the InfoSets screen (SQ02), bypassing the ABAP Workbench Organizer. The recommended best business practice is for SAP customers to use the standard query area to create their reports.
Administrative Decisions for Query Groups
Another important administrative decision is where to keep and maintain the query groups and InfoSets. For example, will you keep them in your development environment or in your production environment? With custom-coded ABAP reports written by programmers, the traditional methodology for report creation was as follows: A programmer accessed a development environment, where the first draft of the custom report was coded and tested. Next, the report was transported to a testing or quality assurance client, where it was more thoroughly tested. If it passed the testing, it was then moved on to the production environment for use.
The Query tools were added to SAP to give end users with no technical skills the ability to create reports in real time. With this in mind, your organization has to make a decision regarding a transport strategy. Query objects can be created in any client. However, there are some best practices. For starters, end users who will be using the Query tools often only have user IDs in the live Production environment. Because of this, and because the Query tools are designed for real-time live access to information, the best practice is to maintain query groups live in the production client.
Helpful Hint
If your organization uses some version of a standard new SAP user request form, it would be ideal to add a section to that form where you specify which query groups the new user will belong to and assign the task of placing users in the appropriate query groups as part of the SAP ID creation process.
Administrative Decisions for InfoSets
Although InfoSets can be created in any client, best practice dictates that they be treated in line with normal programming methodology: InfoSets should be created in a development environment owned by a technical professional and transported to a testing quality assurance client, where they are tested and then moved on to production for use. InfoSets are treated differently from the other fundamental objects of the query tools, such as query areas, because a trained user can add special coding or programs to InfoSets that may have an impact on system resources or functioning, and testing them is required in those cases. So the best business practice is to create InfoSets in your development environment.
Administrative Decisions for Queries
Finally, you need to make some administrative decisions regarding the reports (queries) themselves. Unlike custom-coded ABAP reports, query reports are designed to be made in real time in an ad hoc fashion, so the best practice is to create queries live in your production environment. Although the Query tools no longer have the resource hog reputation that existed in the early days, a user still needs to be trained before using queries in production.
As with all SAP-delivered canned reports and all of your organization’s custom ABAP reports that are available to end users in your production client, if a user does not input the appropriate information on the report’s selection screen, he or she runs the risk of pulling an incorrect amount of data from the database. This is also true of queries. The same training that new users of SAP receive to learn how to run standard reports must be extended to the Query tools as well. The best business practice is to allow trained end users to create queries live in your production environment.
Comparing the Query Tools to Decide Which to Use
As described earlier in this chapter, in version 4.6C and above of the SAP R/3 enterprise solution, SAP delivers the following Query tools: the SAP Query tool, the InfoSet (Ad Hoc) Query tool, and the QuickViewer. Each of these tools has a different purpose.
The best end user reporting solution is the SAP Query tool, because it has the broadest range of features, is very easy to use, and can be used to create complex reports with no programming skills whatsoever. Because this tool is the most robust, it is often used by power users, functional and business analysts, and savvy end users. The InfoSet (Ad Hoc) Query and QuickViewer tools are often used by a casual user performing a single lookup or inquiry.
You should select a tool for primary and exclusive use by your organization and train your end users on it. That way, you avoid any possible data inconsistencies due to the use of different or multiple tools. I always recommend the use of the SAP Query tool above all other tools because it has the most features and flexibility. The SAP Query tool has the most coverage in this book. Throughout this book, you will learn about each of the SAP R/3 Query tools and will be able to decide for yourself which is an appropriate fit for your organization.
Things to Remember
• The SAP Query family of reporting tools is available for free within all standard SAP installations.
• You should use the standard query area for your queries.
• You should create your query groups live in your production environment.
• You should create InfoSets in your development client and test them in a test or quality assurance client before making them available in your live production client.
• Trained users should create queries in your live production client.
• Use of all three Query tools at the same time may yield inconsistent data, so an organization should commit to a reporting tool and use it exclusively.