As discussed in Chapter 3, "Creating Basic Reports with the SAP Query Tool," the SAP Query tool, in its standard form, is designed so that an end user who has no technical skills can create a report from scratch. Chapter 7, "Creating Advanced Reports with the SAP Query Tool," describes how the SAP Query tool's advanced screens give you even more advanced options and functionality. These are the seven advanced screens of the SAP Query tool:
The following sections explain how to use the SAP Query tool to create a fixed positional formatted file based on a specification, using the sample SAP flight scheduling IDES system that has been used in other examples in this book so far. The spec shown in Table 8.2 details the expected format of an SAP extract file, using the data in the SAP flight scheduling IDES system. The table explains which data should be in which positions in the file. For example, by reading through the table, you can expect that the first 10 characters of the extract file will contain the flight date.
This spec tells you where each piece of data should reside in the file, based on the positional number. An example of what a single line of this extract would look like is 01/01/2006ABCDEFGHIJKLMNOPQRST100,000,000,000,000.00ABCDEFGHIJK ABCDEFGHIJKLMNOPQRSTXXXXX. In the following sections, you will follow three main steps to build an extract based on this specification:
Step 1: Creating the Basic SAP Query List Report
As discussed in Chapter 3, you can create a basic list report by using the five main screens of the SAP Query tool. (For details on how to create a basic list report with the SAP Query tool, see Chapter 3.) Your first step is to create an SAP query basic list report that outputs the fields listed in Table 8.2 in the order in which they are listed in the table. For my example, I called my new SAP query report DLS_QUERY_08. Your finished report will output the Flight Date, Plane Type, Airfare, and Flight Class text fields.
However, simply creating this report does not satisfy the specification requirement. For example, the report is not positionally spaced according the spec and does not contain the two additional fields listed in Table 8.2 as Company-Specific Name Designator and End-of-Line Filler. The purpose of a positional extract file is to be interpretable by another computer system that takes each piece of information in the positional location and loads it to the computer system. It is therefore required that each piece of data reside exactly where the specification says it should reside.
For example, you can download your current SAP query report (for example, DLS_QUERY_08) to see how much it differs from the output request in the specification. A quick and easy way to download report data to a text file from the displayed report output screen is to select List, Export, Local File (or press Ctrl+Shift+F9) and then select the Unconverted Text option. You can then specify a location for your saved text file. For my example, I entered c:\step1.txt and clicked the Transfer button. The report output is then downloaded to a text file in the designated location.
You can use several different types of software solutions to check that a positional text file has each of the characters in the appropriate place. These include the DOS editor (or the DOS command prompt, which you can get to by selecting Start, Run and typing CMD) and the Microsoft Excel import text file function. However, because you may not have either of these tools on hand, this chapter simply uses the Microsoft standard Notepad to count the characters to ensure that they are correct for this example.
After you download your SAP query report data, you can follow these steps to open your report data with Microsoft Notepad to see if your data is in the correct positions compared to the spec:
As mentioned earlier, in order to be interpreted by a third-party computer system, the expected report output should appear in a fixed positional format. For the example we're using, the expected output should appear in the format of the sample output shown in Figure 8.2.
Figure 8.2. The expected positional output based on the spec shown in Table 8.2.
If you compare Figures 8.1 and 8.2, you can see why the third-party system would easily recognize the data in Figure 8.2 and why the system would likely reject the data in Figure 8.1. The data in Figure 8.1 has the following problems:
The following section describes how to overcome these problems and how you can use the SAP Query tool to create an extract file based on a spec.
Step 2: Performing the Additional Configuration to Meet the Specification
You use the advanced screens covered in Chapter 7 to make the fixed-width positional extract file meet the specification. The following sections describe the steps you need to take.
Removing the Header Lines, Column Headers, Formatting Lines, and Field Separators
The first change you need to make to your report is to remove the header lines from the report. In this example, by default the SAP Query tool inserts three header lines that identify, among other things, the current date, query name, and page number. You also need to remove the column headers and field separators. To make these changes, you follow these steps:
The quick changes you have made so far have resolved the first three listed problems with the report, so you're now ready to tackle the last three.
Removing the Measurement Designation (Where Applicable)
Not all reports contain some form of measurement classification, such as currency (for example, U.S. dollars) or weight (for example, 123 lb.), so this step is applicable only if you have a measurement classification that requires removal. Removing the measurement designation is easy. To remove the measurement classification (in this case, Currency) from your existing SAP query, you follow these steps:
Figure 8.6. You have three options with regard to displaying units of measurement for a field.
If you compare Figure 8.4 with Figure 8.6, you can see that the entire Currency column has been removed. This quick change has resolved the fourth of the six problems with the report. You now need to fix only two more problems.
Making the Correct Positional Designations
At this point, you need to see if your column widths match up to those listed in the specification shown in Table 8.2, earlier in this chapter.
As discussed in Chapter 7, the column width designations are on the Field Output Options screen. To vary the column widths of your SAP query report to match the specification, follow these steps:
Editing the width of basic text fields is permitted. However, before modifying a field's width, you should check the database to learn whether there is a list of acceptable values that are tied to that field; this will assist you in determining the maximum and minimum widths you can set for the field. For example, the Flight Class field has three acceptable fixed values: First Class, Business Class, and Economy Class. The longest width of those three acceptable values is 14 characters. Modifying that column width is easy because the field can have only a limited number of values. A more challenging field would be something like Company Name, which could, for example, be set at 60 characters; most company names will fit within that width, but there could be a company name that is greater than 60 characters. You therefore need to use caution when varying a field's width.
The specification includes the starting and ending position numbers of each field in the Position column. To determine the width, you can subtract the starting value from the ending value of the Position column. For example, if the Position column in the spec says that the Text: Flight Class field goes from Position 55 to Position 75, you can subtract 55 from 75 to get a width of 20. By using basic math, you can translate that into column start and end values to compare. By looking at Figure 8.7, you can see the column widths, and you can extrapolate the start and end values, as shown in Table 8.3.
Inserting Filler Data
Now you need to address the fields that are not included in your report, which I refer to as filler fields. When you examine Table 8.3, you see that the first three fields do not require any modification because they currently meet the spec. However, after the Airfare field and before the Flight Class text field, you need to insert the company-specific name designator in Positions 4354. To insert a filler field, follow these steps:
Figure 8.9. The report output now matches the specification.
Step 3: Downloading the Extract File
The final step you need to take in creating a fixed positional extract file by using the SAP Query tool is to download the file. To complete this process, you follow these steps: