Chapter 4. The Fundamentals of Reporting with the SAP Query Tool
In this chapter
SAP Query Reporting in the Real World 42
SAP Query Maintenance Functions 46
Working on Your Existing SAP Queries 50
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.)
As you can see from the previous example and Chapter 3, it is very easy to quickly create SAP query reports. Because of this, many organizations start off small, but after a few months they have thousands of reports. Using the naming convention DLS_ReportName (replacing DLS with your initials) used in the previous example is a great start. At the very least, if you follow this convention, each user’s reports will be segregated by user to make it easier to locate users.
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:
1. When you create custom reports that you intend to reuse for yourself, use a naming convention such as DLS_ReportName, replacing DLS with your initials.
2. When you create custom reports that you do not intend to reuse (designed for single-inquiry lookup), use a naming convention such as DELETE_DLS01, replacing DLS with your initials. Routinely delete reports whose names have the prefix DELETE_ to ensure that your library remains clean and efficient.
3. When you create custom reports that are standards for your organization (and that will be used by multiple users), use a common prefix to identify them as major reports that can be used by anyone. For example, you could follow the convention ABC_Report04, where ABC is an abbreviation of your company’s name.
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 are—for example, invoices sorted by cost center, without specifying which invoices—because 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.
Table 4.1. Snapshot of Fields and Data from SAP Query ABC_EELIST
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:
• A list of all associates and their addresses
• Mailing address labels for all active associates in North Carolina
• A list of all retired associates and their phone numbers
• A list of union associates in benefits plan A
• A mailing list of associates over the age of 75
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.
Table 4.2. Sample Fictional SAP Query Reporting Standards for the HCM Module
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.
SAP Query Maintenance Functions
Performing maintenance—including locking your queries against changes, transporting them between clients, and so on—is a task you will likely perform on a regular basis as you work with SAP Query. Having a good understanding of these maintenance functions is the key to properly administering the reporting solution in your organization.
Locking Your SAP Queries
Earlier in this chapter, I encouraged the use of the SAP Query tool’s locking feature. The locking feature, described in the “Special Attributes” section in Chapter 3, allows a user to lock an SAP query. A locked query can be executed, copied, and transported (to the standard or global application area), but it cannot be altered or deleted by anyone other than the person who locked it (not the person who created it). Note that any user who opens an SAP Query (via the Change or Create button) and selects the Change Lock check box has his or her SAP user ID locking that query.
Best practice dictates that if you are creating queries associated with Rule 1, it is a good practice to lock your own queries during the creation process. In line with Rule 2, you should never select the lock indicator for queries that you intend to delete (those starting with a DELETE_ prefix). Finally, for those queries that will be used by multiple users and will become company staples (refer to Rule 3), you should ask your system administrator/owner to lock these individual company reports. This way, you can rest assured that queries cannot accidentally be modified or deleted.
Helpful Hint
Keep in mind that when a user locks a query and then is no longer a user, the only way the query can be unlocked is for the system administrator to reset that user’s password and log in as that user to unmark the query. Another workaround is to simply make a copy of the query and then make changes to the copied version.
Copying Existing SAP Queries (Same Client, Same Query Group)
Although it is a great idea not to duplicate one report over and over, there are instances in which a standard report contains everything you need but is missing only a field or two. Because of Rule 3, you cannot change a company query. However, you can simply make a quick copy of the report (and its variant, if it has one; this is covered in Chapter 6, “Using Reporting Selection Screens: Advanced Skills,” in the section “Variants”) and than make the change you need to the copied version.
Follow these steps to copy a query:
1. Navigate to the main screen of the SAP Query tool by using transaction code SQ01 and select from the list the query you want to copy (or type the query’s name in the Query box at the top of the screen).
2. Click the white copy button on the Application toolbar. A dialog box like the one shown in Figure 4.2 appears.
Figure 4.2. The Copy a Query dialog box allows you to enter a new name for your copied query.
3. Type in a new name for your copied SAP query in the To Query Name box and then press Enter. Be sure to follow the naming rules discussed earlier in this chapter when naming the copied query.
4. When your copied SAP query appears in the same user group as the original, change the long report title (description) of the copied query to distinguish it from the original.
Copying or Moving Existing SAP Queries (Same Client, Different Query Group)
In some cases, you might want to copy or move an existing query from one query group on the client to another. At any time, if you want to see a list of valid query groups on the client, you can navigate to the SAP Query tool main screen (by using transaction code SQ01) and press Shift+F7, to get a list. This list will appear only if you are currently assigned to multiple query groups (or if you have an administrator or SAP_ALL login type of security access). Although it is a great idea not to duplicate reports between query groups, in some cases, there is a business case for moving or copying a query to a different query group.
Follow these steps to copy a query to a new query group:
1. Navigate to the main screen of the SAP Query tool by using transaction code SQ01 and select from the list the query you want to copy (or type the query’s name in the Query box at the top of the screen).
2. On a scrap of paper, write the name of the query and the name of the query group in which it currently resides.
3. Press Shift+F7 to view a list of all query groups to which you are assigned.
4. Double-click the query group you want to move the query to. You are now on the main screen of the SAP Query tool, and the query group is listed on the top left of the screen.
5. Click the white Copy button on the Application toolbar.
6. When a dialog box like the one shown in Figure 4.2 appears, take a look at your scrap of paper and type the query and query group names into the form.
7. Type a new name for your copied SAP query in the To Query Name box, ensuring that the Query (User) Group lists the new query group name to which you are moving the query.
8. Press Enter. Your copied SAP query, with its new name, appears in the new query group, along with the original query group.
9. Be sure to change the long report title (description) of the copied query to distinguish it from the original (as needed), and be sure to follow the naming rules discussed earlier in this chapter when naming the copied query
10. If you want to move and not copy a query, return to the original query group by pressing Shift+F7 and then delete the copied query as described in the following section.
Deleting SAP Queries
To delete an SAP query, follow these steps:
1. Navigate to the main screen of the SAP Query tool by using transaction code SQ01 and select from the list the query you want to delete (or type the query’s name in the Query box at the top of the screen).
2. Select the trash can Delete button on the Application toolbar (see Figure 4.3).
Figure 4.3. The Delete Query dialog box allows you to delete existing queries.
3. When a Delete Query dialog box like the one shown in Figure 4.3 appears, confirm that you want to delete the query by pressing Enter. A message appears in the bottom left of the screen, letting you know that the query has been successfully deleted.
Helpful Hint
Keep in mind the following guidelines when deleting reports:
• Never delete another user’s report.
• Never delete a report that has the prefix ABC_(where ABC is an abbreviation of your company’s name).
• If you are unsure whether to delete a report, ask first. (It’s better to be safe than sorry!)
Working on Your Existing SAP Queries
Now that you have learned how easy it is to create queries, copy them, and delete them, you need some real-world practice with editing and making changes to a query that already exists. Here is your opportunity to practice receiving report requests, performing maintenance, and editing them.
As your first exercise, create an SAP query from scratch and name it DLS_Query_03 (replacing DLS with your initials). The query should contain the following output: flight class, airline carrier ID, arrival city, maximum capacity, plane type, and airfare. Your finished output should be similar to what is shown in Figure 4.4.
Figure 4.4. Your report output will vary from what is shown here, based on your system’s data, but the column format should appear the same.
Navigating Through an Existing SAP Query
After a query is created, quite often you need to return to it and make modifications. You have a couple options for editing existing queries and the related navigation:
• You can navigate to the main screen of the SAP Query tool by using transaction code SQ01 and select from the list the query you want to copy (or type the query’s name in the Query box at the top of the screen).
• You can click the Change button to open your existing SAP query for editing. You are then presented with the first of the five basic screens of the SAP Query tool.
• You can navigate between the screens of the SAP Query tool by using the directional arrows at the top left of the screen and by clicking the Basic List button on the Application toolbar to get to the last screen.
• You can navigate between the five basic screens of the SAP Query tool by using menu paths. From any screen in the SAP Query tool, you can select Goto, Field Selection, and then select one of the first four screens to jump directly to it. You can access the last screen, the Basic List screen, by selecting Goto, Basic List, Structure.
Modifying an Existing Query: Adding Fields
Now that you have mastered navigating an existing query, you can move on to the next exercise: Modify the SAP query you created in the preceding exercise (refer to Figure 4.4) by implementing the following changes: Add a Weight of Luggage field between the Plane Type and Airfare fields. Also add the Flight Date and Destination Airport fields at the end of the report. Your finished output should appear similar to Figure 4.5.
Figure 4.5. Your revised report output will vary from what is shown here, based on your system’s data.
You may have noticed the field I have been referencing called Airfare (see Figure 4.6, which has a column heading of Flgtprice, and Figure 4.7). In most cases within SAP logical databases, the field name (as it appears within query tool creation screens) and the column header (as it appears in the finished report output) are the same. However, in the SAP Flight Scheduling System logical database, it is different for this particular field and may cause confusion. Be aware of these types of differences. If you want to alter a column heading for a field, you can do so by accessing the InfoSet as described in Chapter 2, “One-Time Configuration for Query Tool Use,” in the section “Changing the Name of a Field or Column Header.”
Helpful Hint
Recall from Chapter 3 that it is a best business practice to list your sequence of fields on the Basic List screen in increments (refer to Figure 3.13 in Chapter 3). That way, if you need to modify the report output, add a field, or delete a field, you do not need to renumber the entire sequence in the report. A popular way to do this is to use increments of 5.
Modifying an Existing Query: Removing Fields and Modifying the Sort
Now that you have finished editing your query, you can move on to your next exercise: Modify your query by removing the output of the Flight Class, Airline Carrier ID, and Arrival City fields, but continue to indicate that you want to sort on the Flight Class field (see Figure 4.6). Your finished output should look similar to what is shown in Figure 4.7.
Figure 4.6. You use the Basic List screen of the SAP Query tool to define the report output.
Figure 4.7. My sample report output when displayed in the SAP List Viewer auto-sizes each column to fit its contents.
To test all the skills you have learned in this chapter, you can try one final exercise: Make a copy of your existing SAP query and name it DLS_Query_04 (replacing DLS with your initials). Change the title of the query on the first screen of the SAP Query tool to reflect that it is SAP Query 4. In this copied version, add the following fields: Text:Maximum Capacity (as the first field in the report output), Text:Flight Class (to the right of the Flight Date field), and Smoker and Text:Smoker (as the last two fields in the report). Add totals to appear in the report for the Maximum Capacity and Weight of Luggage fields. Your finished output should look similar to what is shown in Figure 4.8.
Figure 4.8. In this example, some of my column data is blank; this will vary based on what is stored in the database.
Things to Remember
• The best way to ensure that your organization is utilizing the SAP reporting functionality in the most efficient manner is to utilize the three best practices described in this chapter.
• Identifying which measurements you use to classify major items and documenting them is the key to accurate, consistent reporting measures.
• You can execute a single SAP query report to produce hundreds of different reports simply by altering the text on the selection screen. When you execute a report, you almost always see a selection screen that gives you an opportunity to further specify your selections.
• You should never delete another user’s report.
• It is easy to navigate between the five basic screens of the SAP Query tool by using menu paths. From any screen in the SAP Query tool, you can select Goto, Field Selection, and then select one of the basic screens to jump directly to it. You can access the Basic List Line Structure screen by selecting Goto, Basic List, Structure.