You can display a calculated value in detail block text, break footing text, and final text on a report.
In this example, you'll define an expression that adds an employee's salary and commission. This expression is similar to the one used in a query in Creating a column using expressions. Then you will display the result in the detail block text on the Personnel Status Report you created and changed in previous examples.
To display a calculated value:
SHOW FORM.CALC
Figure 121. Specify an expression to calculate a value on FORM.CALC.
+--------------------------------------------------------------------------------+ | FORM.CALC MODIFIED | | | | Pass For &CALCid | | ID CALCULATION EXPRESSION Nulls? WIDTH EDIT | | --- -------------------------------------------------- ------ ----- ----- | | 1 &6 + NULL(&7) YES 12 D2 | | *** END *** | | | +--------------------------------------------------------------------------------+
In this example, type 1 for ID.
In this example, type &6 + NULL(&7), which means to add the values in columns 6 (SALARY) and 7 (COMM).
Because some of the commission values in the sample tables are null, they appear as a hyphen in the report. REXX can not perform an arithmetic operation on data that contains both numeric values and nulls. The REXX NULL EXEC looks for the nulls in the data and replaces them with a specified value. In this case, it replaces nulls with zeros.
When you write a REXX EXEC, make sure that you make it available to QMF by placing it on an accessible disk or specifying the correct data set. Here is the NULL EXEC for this example:
/* REXX EXEC to substitute 0 in place of nulls */
parse arg in1
if in1 = "DSQNULL" then
value = 0
else value = in1
return value
See Specifying punctuation for the values in a column for information about edit codes.
After you define the expression, you can use the FORM.DETAIL panel to define how you want to display the result of the calculation on the report.
SHOW FORM.DETAIL
The FORM.DETAIL panel displays.
For this example, type Total Earnings:&CALC1. The variable &CALC1 corresponds to the calculation expression you created on the FORM.CALC panel.
Change the line number and alignment for the new line of text. For this example, change the line number to 6 and change the alignment to 3. This means that you want this line of detail block text to begin in column 3.
Figure 122. Specify where a calculated value appears with detail block text.
+--------------------------------------------------------------------------------+ | LINE ALIGN DETAIL BLOCK TEXT | | ---- ------ ----+----1----+----2----+----3----+----4----+----5----+ | | 1 LEFT Employee: &2 | | 2 LEFT ID: &1 | | 3 LEFT Years of Service: &5 | | 4 LEFT Salary: &6 | | 5 LEFT Commission: &7 | | 6 3 Total Earnings: &CALC1 | +--------------------------------------------------------------------------------+
Figure 123. The calculated value appears next to Total Earnings in the report.
+--------------------------------------------------------------------------------+ | Employee Data for the WESTERN Division | | Date: 1998-03-17 | | | | | | ***************************** | | ** Personnel Status Report ** | | ***************************** | | Department number 66, Department name PACIFIC | | Manager: 270 | | | | Position: CLERK | | Employee: BURKE | | ID: 330 | | Years of Service: 1 | | Salary: 10988.00 | | Commission: 55.50 | | Total Earnings: $11,043.50 | +--------------------------------------------------------------------------------+
In this example, the value for Total Earnings comes from the &CALC1 variable. You can also define Total Earnings as a new column by using the same expression and REXX EXEC. Then, you can specify the value on the FORM.DETAIL panel by using the form variable &n, where n is the column number given to the new column.
For information on defining a column, see Adding a new column to a report.