Modifying an SAP Query


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.



Maintaining Queries

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

  • Creating a query report using the SAP Query tool is very easy.

  • You can navigate between the basic screens of the SAP Query tool by using the navigational arrows on the Application toolbar.

  • Whenever you want to execute your report, you can do so by pressing F8 on your keyboard.

  • When you execute a report, you always see a selection screen that gives you the opportunity to further specify your selections.

  • When creating reports, it is a good idea to format your output on the Basic List Line Structure screen in increments to make it easier to edit your report in the future.



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.)

Report Naming

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 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.

Table 4.1. Snapshot of Fields and Data from SAP Query ABC_EELIST

First Name

Last Name

Street 1

Street 2

City

State

Postal Code

Telephone

Grace

MacFarland

1 Smith St

Apt 15

Brooklyn

NY

11209

212-555-1212

Will

Adler

2 Milky Way

 

Charlotte

NC

28217

704-555-1212

Jack

Walker

3 Oak Ct

Suite JCS

Wantagh

NY

11793

516-555-1212

Karen

Truman

4 Red Ave

 

Garden City

NY

11530

516-799-1212

Anastasia

Behausen

5 Bell Ln

Weddington

NC

28173

704-555-1213


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

Data

Sample Fictional Company Reporting Standard

Active associates

Employee Group 1 + 7 on infotype 0001

Terminated associates

Employee Group 3, 5, and 9 use infotype 41 to get term date

Associates on leave

Employee group = 7

EE status

To pull all salaried ee's (S*), all hourly ee's (H*), etc. Please note all temporary (T*) and on-call (O*) are excluded from our scheduled management level reports and are not included in the LTO numbers we report.

Home address

Use Infotype 0006 Address Record Type 1 Permanent address

Union associates

Employee Sub Groups beginning in U are considered union (e.g. to pull all union ee's use U*)

Gender

Infotype 02, text gender key

Race

Infotype 77, text ethnic origin


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.