Using Multiple Conditions to Create an Advanced Calculated Field
In many cases, you want to see report output only if certain complex conditions are met. Chapter 9 provides an example of how to perform a calculation if a single condition is met. In that example, you added to Figure 9.6 a new calculated field that contains the value of the Airfare field minus 100, but only if the flight is first class. In this single-condition scenario, you performed a calculation only if the flight was first class. What if you wanted to perform different calculations depending on the flight class? For example, say that for first class, the Airfare field would be reduced by 100, for business class the Airfare field would be reduced by 50, and for coach class the Airfare field would be reduced by $25. As another example, you might need to base a condition on several variables, such as the wing of the airport used for each departure. There are several real-world applications for this type of calculation, including the following:
HR/payroll You could determine what security badge should be given to an employee based on the employee's location.
Finance You could determine the general ledger account number associated with a wage type.
Logistics For rebate processing, you could report on which accounts a rebate will be included with, based on certain account numbers.
To use local fields to perform complex calculations, you follow these steps:
Navigate to the main screen of the SAP Query tool by using transaction code SQ01, select the query you want to work with (for example, DLS_QUERY_10), and click the Change button.
Navigate to the Select Fields screen by clicking the Next Screen button on the application toolbar twice.
Select Edit, Short Names, Switch On/Off. This allows you to create short names for your existing query fields so that you can easily refer to them in calculations. Start by giving the Airline Carrier ID field the short name Airline.
Select Edit, Local Field, Create. Because your cursor was on the Airline field, a custom field is added to its field group (Flight Schedule Table).
When the Field Definition dialog box appears, input a short name (GATE) for your newly created local field and input the field description (Gate name), which will be the heading for the column in the report.
Define the attributes for the field, as shown in Figure 10.2. To output a potentially 10-digit gate code (for example, South gate), you could select the Text option and indicate that it will be 10 characters wide. However, you need to be sure that the column is wide enough to fit the column heading, which is set on the Field Definition dialog box (in this case, Gate Name9 characters). Be sure to indicate that the character width will be able to accommodate the column heading and the largest entry (Gate Name) that could possibly be in that field.
Figure 10.2. Setting the attributes to a text field with 10 characters permits the eventual output in the report of the largest gate name, North Gate.
Click the Complex Calculation button to bring up the Define Field: Complex Calculation dialog box, which allows you to input multiple conditions. As shown in Figure 10.3, you input of the conditions in the same order and mathematical format mentioned in Chapter 9.
Figure 10.3. The Define Field: Complex Calculations dialog box permits the entry of three fixed conditions, although you are not required to use them all.
Type the condition AIRLINE = 'LH' in the Condition line and then enter 'West Gate' as the desired output if the condition is met in the Formula line. You can enter up to three different conditions on this screen. To ensure that you have typed correctly, click the Code Check button (or press Shift+F4). The SAP Query tool then checks your syntax to ensure that there are no typos.
To add the newly created field to your report output, confirm that the field is selected on the Select Fields screen by ensuring that the small box next to the Gate Name field at the bottom of the Flight Schedule field group is selected.
Navigate to the Basic List Line Structure screen by clicking the Basic List button on the application toolbar. You see your newly created field listed there, under the name you gave it in step 6. Add the new local field to your report output on the Basic List screen of the SAP query report by indicating a line and sequence number. (I listed mine as Line 1 and Sequence 10.) Click the Save button.
Execute your report by pressing F8. You are presented with the report's selection screen. Press F8 again to see your updated SAP query, which should now contain your newly added calculated field (see Figure 10.4).
Figure 10.4. The added calculated field appears in the report output, displaying data as requested in the Define Field: Complex Calculation dialog box.