Modifying an SAP Query
After you have created a query, you might want to navigate between the various screens to make changes and modifications. You can use the Next Screen and Previous Screen white arrow buttons on the Application toolbar in the first four screens of the SAP Query tool.
You can also click the Basic List button to jump to the Basic List Line Structure screen or press the F8 key to navigate to the selection screen or finished report output from the selection screen. Clicking the green Back button on the Application toolbar from the finished report output screen brings you to the report's selection screen. You can also navigate between screens by using the toolbar menus.
Saving a Query
From any screen in the SAP Query tool except the Selections screen or the finished report output screen, you can click the Save button on the Application toolbar to save your query. This makes sense: If you click the Save button on the selection screen, the system thinks you want to save the entries as a variant, and if you click the Save button on the list screen, the system thinks you want to save your output as a list.
You can maintain and execute queries from the main SAP Query tool screen, which you access by using the transaction code SQ01. By using the buttons at the top of this screen, you can execute, change, create new, or view descriptions of queries in R/3. By using the Application toolbar, you can also copy, delete, or rename your queries (see Figure 3.14).
Figure 3.14. The main screen of the SAP Query tool contains functions on the menu bar for maintaining queries.
Things to Remember
Chapter 4. The Fundamentals of Reporting with the SAP Query Tool
In this chapter
Now that you have completed the one-time configuration and have begun creating basic SAP queries, it is important to practice navigating within the SAP Query tool to edit and work with your queries. This chapter covers everything you need to know to excel at SAP query report writing and, more importantly, how to start off on the right foot.
SAP Query Reporting in the Real World
Chapter 3, "Creating Basic Reports with the SAP Query Tool," details how to create a basic SAP query report by using the five screens of the SAP Query tool. Before you proceed to more advanced topics, it is crucial that you understand how the maintenance of queries works, because you will spend a great deal of time manipulating and changing existing queries. This chapter builds on what you learned in Chapter 3.
Requests for information from the SAP database are generally received in this format: "How many widgets do we have in stock in our database?" or "Please give me a list of all associates in Texas and their annual salaries." The following sections present a real-world example of the types of reports you will be creating using the SAP Query tool.
Your First Official Report Request
For this example, you need to prepare a report that answers the question, "In your SAP solution, how many flights with Plane Type A319 are scheduled for arrival in the city of Frankfurt on the flight date June 1995?" (Although your SAP system may contain different information than this example, the report format should be the same.) This type of request is common in the sense that it asks for an answer based on multiple criteria.
Most often, reports are created to answer a specific question and not to simply review long lists of data. Producing a list of all active cost centers in the SAP database would waste a lot of paper. But you can use a basic report such as a list of all active cost centers to produce a list of which active cost centers are in Mexico. A simple change to an entry on a selection screen can make a simple report of all cost centers into a myriad of different types of useful reports, each of which can then be used to answer a business-specific challenge or question.
The example presented in this chapter shows how you can use a single report multiple times to satisfy multiple needs without having to change the core report. To answer the question posed earlier, you execute a basic list SAP query report (which you should call DLS_Query_02, where DLS is your initials) that includes the fields you need for output in addition to some other fields. Figure 4.1 shows the output of this report.
Figure 4.1. The report output screen of the SAP Query tool, listing the fields used in the report to answer the question in this example.
When you review the report output, you can easily answer the question posed earlier. The answer in this example is 1. (Your answer may be different, depending on your system output.)
Best Naming Conventions for SAP Query Reporting
Because it is so easy to create reports, many companies end up with a library of thousands of reports, many of which are duplicates. To ensure that your organization is utilizing the reporting functionality in the most efficient manner possible, it is a good idea to set some guidelines. Following three rules will ensure a clean library and SAP environment, assist you in custom report identification, and help with upgrades, where applicable, because you can easily identify key reports and report creators:
Organizations that begin using the SAP Query tool often have several users creating reports. They use the proper report naming convention, but, as discussed in Chapter 3, a SAP query also has a long description of the report (for example, List of Open Invoices by Cost Center). Although it is a good idea to use a long title that is descriptive, it is also important to keep in mind that other users may stumble upon your report and think that it is just what they are looking for, based purely on the title. But a report titled List of Open Invoices by Cost Center may not actually contain a list of open invoices by cost center. Unless instructed formally otherwise, you should follow Rule 1 and identify your SAP queries by your initials in the short description and be generic about the report data contained within the report in the report's long description, because then it is clear what the reports really are.
As mentioned earlier, you can use a single report to satisfy multiple reporting requirements by simply changing the information entered on the report's Selections screen. The long description of your report should describe what those columns arefor example, invoices sorted by cost center, without specifying which invoicesbecause a user can vary the selection such that only open invoices or closed invoices are included upon report execution.
If you are creating a report just to look something up, and you will likely use the report only one time, it is best to follow Rule 2 and name the report using the convention DELETE_Report01. This way, you can be certain that others will not trust the report and misuse it. More importantly, the DELETE_ prefix is a flag for the report to be deleted during regular maintenance.
You and others will create at least a dozen reports that will become staples in your organization. As mentioned earlier in this chapter, you can create a single report, and simply by varying your entries on the selection screen, you can use the report to satisfy a multitude of different reporting requirements. Major reports should be collectively agreed upon and locked against changes, as described in the section "Special Attributes" in Chapter 3. You should also follow Rule 3 and use an abbreviation of your company's name as a prefix in the name of any such report. Here is a real-world example: In my organization we use the SAP Query tool for approximately 85% of the company's human resources and payroll reporting (with the remaining 15% handled in ABAP). We have a standard SAP query called ABC_EELIST (where ABC is my company's initials). This SAP query of the Human Capital Management module simply contains a list of associate names, addresses, and phone numbers. Table 4.1 shows a snapshot of the output of five fictional associate records from this report. This basic SAP query outputs the fields associate first name, last name, street address 1, street address 2, city, state, postal code, and telephone number.
You could execute this single report or produce hundreds of different reports simply by altering the text on the selection screen when you execute the report. For example, this single SAP query could be used to create the following reports and more:
You will notice that some of the sample reports listed here contain data that is not displayed in the report output. As mentioned in Chapter 3, you can include data in your selection criteria but not in your report output.
You do not need a dozen different reports; you can simply create the one main SAP query and vary the output on the selection screen to ensure that you get just the data you need. This type of report is an ideal candidate for Rule 3.
Do You Want Fries with That?
Inevitably, all organizations run into the same problem: which measurements to use to classify major items. My department calls this the "Do you want fries with that? syndrome" because we always need to ask an additional question of people asking for report data. For example, in my day-to-day job, I am often asked, "How many associates do we have?" It seems like an easy enough question, but I need to ask about a number of details: "Do you mean active headcount? Do you want me to include associates on paid leave? Do you want me to count full-time associates or all associates, regardless of status (student, temporary, and so on)? Should I include union associates?" Without the follow-up questions, I could provide dozens of different answers. As you can see, standards are key to reporting.
SAP Query Reporting Standards
Regardless of which application area you will be reporting on (Materials Management, Sales & Distribution, Finance, and so on), you need to identify reporting standards. You then need to publish and distribute these standards so that anyone creating or receiving reports knows what they are.
At my workplace, we have a published document that we update and redistribute monthly. It includes all the standards needed to understand creating and receiving reports. It is often easiest to use the Human Capital Management (HCM) module as an example, because unlike the other functional areas, all readers are familiar with the workings of human resources. Table 4.2 shows an example of reporting standards for HCM module reporting.
In Chapter 3 you learned how easy it is to create reports. In this chapter, it is critical that you understand that reports created without reporting standards are not meaningful, because you will continually be comparing apples to oranges.