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.