Using Multiple Fields to Perform a Calculation in an Advanced Calculated Field
In Chapter 9 you learned how to perform a dynamic calculation based on the entry of data on a report's selection screen upon execution. In some cases, you might want to perform a calculation or output a value based on a value that is only calculated or input dynamically at report execution time. For example, you can use the same SAP query you have been working with in this chapter to create a calculated field to output the gate name based on the day the weather is entered on the selection screen (assuming for this example that the gate may vary based on the weather). There are several real-world applications for this type of conditional calculation, including the following:
HR/payroll You could analyze how much it would cost for an employee on the third shift to work on a holiday by calculating the cost of increasing the hourly rate by a conditional dollar amount that varies based on the year entered on the selection screen.
Finance You could monitor the impacts of accounts payable reports if an invoice were paid in a conditional time frame, based on whether the account was in good or poor standing, as indicated on the report's selection screen.
Logistics You could calculate the number of days a plant maintenance order has been open as of a conditional date entered on the selection screen and perform a calculation on a fee entered at runtime based on the date.
To create a calculated field that is based on another calculated field that uses dynamic selection screen input, 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. If the short names are not already displayed, select Edit, Short Names, Switch On/Off to turn them on. You will create a new local field within the first field group (the Flight Schedule field group).
Position your cursor within any field in the Flight Schedule field group and then select Edit, Local Field, Create.
When the Define Field dialog box appears, input a short name (WEATHER) for your newly created local field and input the field description Weather, which will be the heading for the column in the report.
Define the attributes for the field by selecting the Text Field option and indicating that the width of the field will be seven characters. This is long enough to support the column heading and to accommodate the entries in the field that will be only one character.
Because the newly created field will simply store a value entered at runtime, select the Input on Selection Screen option button and the mandatory check box. Selecting the mandatory check box ensures that a value is entered on the selection screen upon report execution. Click the green Continue button to return to the Select Fields screen.
Create a calculated field that uses the Weather field's dynamic entry to affect the calculated field. In this case, you can reuse the Gate Name advanced calculated field from the previous example.
Position your cursor in the Gate Name field, on which you will base your calculated field.
Select Edit, Local Field, Change because, again, you are changing a field, not creating a new one. The Field Define Field dialog box appears. Click the Complex Calculation button to review the complex calculation created in the previous example (refer to Figure 10.3).
Modify the conditional statements to use the data entered in the new Weather field on the selection screen. You input these conditions, as shown in Figure 10.5, very similarly to how you entered them in the previous example, but this time, you are assuming that the value options for entry in the Weather field at runtime are limited to R (for rain), S (for snow), and F (for fog).
Figure 10.5. Three different conditional options and an otherwise condition are included.
Type the condition WEATHER = 'R' in the Condition line and then enter the desired output 'West Gate' if the condition is met in the Formula line. You can enter up to three conditions and an "all else/otherwise" option. This says that if an option other than the three listed occurs, the value listed should be output in the Otherwise section.
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 11.) Click the Save button.
Execute your report by pressing F8. You are presented with the report's selection screen, on which you are required to enter a value in the Weather field: R (for rain), S (for snow), or F (for fog), as shown in Figure 10.6.
Figure 10.6. When not populated, the Weather field has a check mark in it to indicate that it is mandatory on the selection screen.
Input a value in the Weather field and then press F8 to see your updated SAP query. It should now contain your dynamically updated calculated field that varies the gate number based on the weather (see Figure 10.7). Press F8 again to see the finished report (refer to Figure 10.6), which now includes the dynamically populated gate name. (In my example, I entered a weather condition of F.)
Figure 10.7. Because an entry of F (fog) was indicated on the selection screen, the query reads the complex logic that says to output South Gate.
To test what other entries on the selection screen would do to the report output, click the Back Screen button and try various options, including weather conditions not listed, such as W, X, or Y. The SAP Query tool should appropriately read the complex calculation logic and output the otherwise condition, Main Gate.
Be sure to refer to Table 9.3 in Chapter 9 for assistance in properly performing complex calculations.
In the preceding example I used the Complex Calculation button to do three fixed conditions and an otherwise condition. Keep in mind that by maximizing the use of the operands AND, NOT, and OR, you can include even more conditions in your complex calculation logic.