Creating a Fixed Positional Extract File by Using the SAP Query Tool

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:

• Control Levels

• Control Level Texts

• List Line Output Options

• Field Output Options

• Field Templates

• Basic List Header

• Graphics

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.

Table 8.2. A Sample Spec Layout for a Report of Data from the IDES Test Database

Position	Description of Data	Example
110	Flight date	01/01/2006
1121	Plane type	ABCDEFGHIJKLMNOPQRST
2242	Airfare	100,000,000,000,000.00
4354	Company-specific name designator	ABCDEFGHIJK
5575	Flight class text	ABCDEFGHIJKLMNOPQRST
7681	End-of-line filler, XXXXX	XXXXX

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:

1.	Create a basic SAP query list report.
2.	Perform the additional configuration needed to meet the specification.
3.	Download the extract file.

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.

Helpful Hint

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:

1.	Launch Notepad by selecting Start, Run and then typing notepad and pressing Enter.
2.	Select the file you saved earlier by selecting File, Open and indicating the path where you saved the text file (for example, c:\step1.txt).
3.	Press Enter. The resulting file should look similar to the one displayed in Figure 8.1. (Keep in mind that your report output may appear different based on the data you have stored in your SAP test IDES solution.) Figure 8.1. Viewing the file data in Notepad allows you to view what the file output would look like.

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 report has three header lines preceding the actual report data.

• The report has field separators in the - or | format, designating section or column breaks.

• The report has column headers.

• The report has an additional column for currency designation.

• The report is not the positional format requested.

• The report does not contain the filler values designated in positions 4354 and 116120.

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:

1.	In your existing SAP query, navigate to the Basic List Line Structure screen by clicking the Basic List button on the Application toolbar. Deselect the Basic List with Box check box at the top left of the screen to remove the box that appears around the displayed report, which may interfere with the extract download.
2.	Navigate to the List Line Output Options screen by selecting Goto, Basic List, Line Output Options. As shown in Figure 8.3, the List Line Output Options screen provides options for varying the output of the entire report list. Figure 8.3. The List Line Output Options screen applies to the actual line items in the report. Most basic list reports contain only one line, so all fields are designated as appearing on line 1 on the basic List Line Structure screen.
3.	Deselect the Header Line check box (which is selected by default) to remove the header line from your SAP query report.
4.	Select Goto, Field Selection, Title Format to access the first screen of the SAP Query tool (the Title, Format screen) and make three adjustments: Change the Output Format designator at the bottom of the screen from SAP List Viewer to ABAP List. This change enables you to see the formatting changes directly on the report's output screen because the report will no longer be formatted in the pretty table view. Change Columns Option from its default setting of 83 to a setting of 255. The 83 setting designates that in ABAP List view, the number of characters across a single line will wrap after 83 characters. Changing this from 83 to 255 causes the wrap to not occur until character 256. Deselect the Print List, With Standard Title check box, which prevents the report title from displaying in the report.
5.	Click the Save button and then press the F8 key to execute the report and to view the report's standard selection screen. Notice that the format output on the selection screen is listed as ABAP List because that is what is indicated on the Title, Format screen. Press the F8 key again to execute the report. Your report should now appear similar to the report shown in Figure 8.4. Figure 8.4. Viewing your SAP report data in ABAP List view is very similar to viewing it in the Notepad editor, and it saves you the extra step of downloading your report to view each small change.

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:

1.	Navigate to the Field Output Options by clicking the Basic List button on the Application toolbar and then selecting Goto, Basic List, Field Output Options. As shown in Figure 8.5, the Field Output Options screen provides options for varying the output of specific fields in a report. Figure 8.5. This screen provides options to vary each field in a report.
2.	In the Unit field, select the second option button to remove the currency column from your report. (Chapter 7 describes the three Unit field option buttons.)
3.	Click the Save button and then press F8 to execute the report and view its selection screen. Click the Execute button on the Application toolbar to see the changes to your report. The currency designations should now be gone, and your report output should appear similar to that shown in Figure 8.6.

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:

1.

Navigate to the Field Output Options by clicking the Basic List button on the Application toolbar and then selecting Goto, Basic List, Field Output Options.

2.

View the Length Std/New column on this screen, which indicates the standard width of the field in the ABAP database. Compare the values listed here with the values listed in your specification for the designated field widths listed in the Position column. The New field allows you to input a longer or shorter number for the width of that field. By default, the Std and New columns contain the same numbers. If you find any that require modification, insert the appropriate value into the New column. In my example, all fields were the appropriate width except for the Text: Flight Class field, which I changed to 20 characters to match the spec (see Figure 8.7). Figure 8.7. When making fields shorter, be sure to consider whether you have any report output in that field that is larger than the designated number.

Helpful Hint

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.

Table 8.3. The Default Layout for the Test SAP Query of Data from the IDES Test Database Compared to the Spec

Column STD	Description of Data	As-Is (Current) Position	To-Be-Modified Position
10	Flight date	110	110
10	Plane type	1121	1121
20	Airfare	2242	2242
	Company-specific name designator		4354
60	Flight class text	43103	5575
	End of line filler, XXXXX		7681

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:

1.	Navigate to the Field Output Options screen by clicking the Basic List button on the Application toolbar and then selecting Goto, Basic List, Field Output Options.
2.	The filler text you want to add appears both before and after the Text: Flight Class field. Recall from Chapter 7 that you can use the template indicator on this field to create templates. Templates give you the opportunity to insert text before and/or after the output of the field data. The only limitation with templates is that they cannot be longer than 46 characters in total width.
3.	Indicate that you want to create a template for the Flight Class field by selecting the Template check box next to the field name.
4.	Click the forward arrow button on the Application toolbar to navigate to the Field Templates screen, which you use to insert text before or after the output of a field that has been designated as a template. Type the appropriate filler values, as indicated in the specthe company-specific name designator and the end-of-line filler XXXXXwithout overwriting the 20-character placeholder for the output of the field (see Figure 8.8). Figure 8.8. There is a space after the company name because the field width is designated as 12 characters, and the fictional company name HARVICK-AIR is only 11 characters.
5.	Click the Save button and then press F8 to see the report's standard selection screen. Click the Execute button to see the changes to your report, which appear similar to the ones shown in Figure 8.9.

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:

1.	On your report's output screen, select List, Export, Local File (or press Ctrl+Shift+F9) and then select the Unconverted Text option. Then specify a location for your saved text file (for example, c:\SPEC_SAMPLE.txt) and then click the Transfer button. Your report output is then downloaded to a text file in the designated location.
2.	Launch Notepad and open the file you saved earlier by selecting File, Open and indicating the path where the text file was saved (for example, c:\SPEC_SAMPLE.txt). The final specification file appears; it should look like the one shown in Figure 8.10. Figure 8.10. The report output matches the specification.