Using a Simple Mathematical Formula to Create a Basic Calculated Field

In this section you will insert a calculated field to perform a basic mathematical equation within the query you created in the preceding section. You will create a calculated field that subtracts 100 from the existing Airfare field and then shows the result as output. There are several real-world applications for this type of calculation, including the following:

• Human capital management You could perform an analysis for an increase in salaries to assist with budgeting and forecasting.

• Finance You could monitor the incoming and outgoing cash flows.

• Logistics You could calculate the number of days a plant maintenance order has been open.

You insert all basic calculations on the Select Field screen of the SAP Query tool. This screen, shown in Figure 9.2, lists the fields selected to be included in your SAP query. To add a basic mathematical calculation to your SAP query, you follow these steps:

Figure 9.2. The Select Field screen lists all the fields available within the InfoSet.

1.

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_09), and click the Change button.

2.

Navigate to the Select Field screen by clicking the Next Screen button on the Application toolbar twice. The screen should appear similar to the one shown in Figure 9.2.

3.

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. For example, enter TIXCOST as the short name for the Airfare field (see the background of Figure 9.3).

Figure 9.3. The Field Definition dialog box displays all the attributes of your newly created calculated field in the SAP Query tool.

4.

Position your cursor in the Airfare field to base your calculated field on it.

5.

From the Select Field screen of the SAP Query tool, select Edit, Local Field, Create. Because your cursor was on the Airfare field, your custom-calculated field will be added to its field group (Flight Demo Table).

6.

When the Field Definition dialog box appears, input a short name for your newly created local field (DISCOUNT) and input a field description (Airfare Discount), which will be the heading for the column in the report (refer to Figure 9.3).

7.

Define what kind of field your newly added custom field will be; the available options are listed in Table 9.2. Select the attributes for the field (refer to Figure 9.3) by indicating that the field is similar to the existing field that you created a short name for. That is, indicate that the field has the same attributes as the TIXCOST field. (Both are currency fields.)

Table 9.2. The Available Field Attributes

Attribute	Description
Same attributes as field	Allows the user to select from a list of any fields in the SAP query that have been given a short name. This selection indicates that the new local field will have the same technical characteristics as the selected field.
Text field	Formats the field to be text and specifies the number of characters for the field (that is, the column width).
Calculation field	Formats the field to be numeric and specifies the number of digits and decimal places for the field (that is, the column width).
Date field	Formats the field to be a calendar-based date field in line with the system's calendar settings.
Time field	Formats the field to be a calendar-based time field in line with the system's calendar settings.
Symbol	Formats the field to be a black-and-white symbol from the list of hundreds available within SAP.
Icon	Formats the field to be a color icon from the list of hundreds available within SAP.

8.

If it is not already selected, select the option button next to the Calculation Formula box at the bottom of the Field Definition dialog box and then enter your basic mathematical formula into the designated box to the right of it (refer to Figure 9.3). In this case, enter TIXCOST-100, which translates to an output in the Airfare Discount field of the calculation: Airfare minus 100. Several different types of basic mathematical symbols are available, as shown in Table 9.3. Press Enter to proceed.

Table 9.3. Valid Operators and Operands for Mathematical Formulas

Operator	Description
*	Multiplication
/	Division
DIV	Whole-number division, where the result is an integer
MOD	Remainder
+	Addition
-	Subtraction
%NAME	The user's name
%DATE	The system date
%TIME	The system time
Numeric constants	A constant numeric value you input (for example, 10 or 28173.29)
Character strings	A constant alphabetic (nonnumeric) value you input (for example, THISISMYTEXT or ABC)
Symbols and icons	A black-and-white or color picture

9.

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 Airfare Discount field at the bottom of the Flight Demo Table field group is selected.

10.

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 7.) Click the Save button.

11.

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 now contains your newly added calculated field (see Figure 9.4).

Figure 9.4. The newly added column that contains the value of the Airfare field minus 100 is now shown on the report.

Technical Details of SAP Query Calculations

When inserting mathematical formulas into an SAP query report, you structure formulas based on standard math procedures, using operands (values) and operators (operand links), which are described in Table 9.3. In case you are like me and remember nothing from college math classes, I have included some specific clarifications on calculations for your reference:

• The operators + and - can also be one-character operators. In this case, they do not link two operands but refer to subsequent operands.

• The processing sequence follows the usual rules, where the operators + or - take priority over characters *, /, DIV, and MOD, which in turn take priority over the operators + and - . However, you can change the processing sequence to suit your own requirements by using parentheses.

• In some cases, you may need to output the fields used in the calculation sequentially on the Basic List Line Structure screen in order for a detailed calculation to function as it is designed to function. For example, if the calculation formula is A + B and the local field is C, you may need to output A and B prior to C in your report output.

• Table 9.3 lists some operands, including %NAME, %DATE, and %TIME, which are special fields that the system automatically provides. %NAME is a 12-character text field that contains the name of the user entered when logging on to the SAP R/3 system. %DATE is a date field that contains today's date. %TIME is a time field that contains the current time. If the character ' appears within a character string, you must precede it with a ' so that it is correctly interpreted. Also, the following special notation is possible for field names and the special fields %NAME, %DATE, and %TIME:

  • field[n1:n2] This field must have its attributes set as a text field. In addition, both n1 and n2 must be whole-number numeric constants (without decimal points), and n1 < n2. field[n1:n2] is the part of the text field that extends from the character at n1 to the character at n2. The first character of a text field appears at Position 1.

  • field[YEAR], field[MONTH], field[DAY] This field must have its attributes set as a date field. The year, the month, and the day can then be provided (in number form).

  • field[HOUR], field[MINUTE], field[SECOND] This field must have its attributes set as a time field. Hours, minutes, and seconds can then be provided.

• Formulas can extend over one or several lines in free format. For example, the individual operands and operators can appear one immediately after the other in a consecutive sequence, or there may be a number of spaces between them. However, there can be no spaces within field names, and field names and character strings cannot extend over more than one line.

• If the fields specified on the screen for the formula and the condition are sufficient to allow full specification of the calculation formula for the local field, you can use these fields for input. Otherwise, you need to use the Complex calculation function, which is covered in Chapter 10, "Creating Advanced Calculated Fields with the SAP Query Tool." This takes you to an editor where you can enter all the necessary calculations.