You can also define a calculation that identifies a special condition on your report by using either an expression or a REXX EXEC.
In this example, you will use calculated values to identify two special conditions on a report, one to identify employees who deserve a commission bonus, and one to identify employees who need a raise.
For more information on REXX EXECs, see the TSO/E Procedures Language REXX/MVS Reference (for TSO) or the VM System Product Interpreter Reference (for CMS). Your installation might not support use of calculations and REXX functions. Check with your QMF administrator before you try to use calculations in a report.
In the first part of this example, you create a condition that prints the text *** Commission Bonus *** on the Personnel Status Report for all employees with a commission greater than or equal to $800.00.
Because you want to be able to specify a different commission amount to qualify for the bonus each time you run the report, write a REXX EXEC that allows you to specify the commission amount when you display the report.
To use a REXX program to identify a special condition:
SHOW FORM.CALC
The FORM.CALC panel displays.
For this example, type BONUS(&7 800). BONUS is a REXX EXEC you write that checks the value in the COMMISSION column (&7) to see if it is greater than or equal to the amount you specify for the commission bonus (800). If the value in the column qualifies for the commission bonus, the words *** Commission Bonus *** display on the report.
Here is the BONUS program for this example:
/* REXX BONUS */ /* program to flag employees whose commission levels warrant a bonus */ parse arg commission commission_level retvalue = ' ' if (commission ¬= "DSQNULL") & (commission >= commission_level) then retvalue = '*** Commission Bonus ***' return retvalue
Figure 124. Specify an expression using a REXX EXEC on FORM.CALC.
+--------------------------------------------------------------------------------+ | FORM.CALC MODIFIED | | | | Pass For &CALCid | | ID CALCULATION EXPRESSION Nulls? WIDTH EDIT | | --- -------------------------------------------------- ------ ----- ----- | | 1 &6 + NULL(&7) YES 12 D2 | | 2 BONUS(&7 800) YES 25 C | | *** END *** | | | +--------------------------------------------------------------------------------+
Now that you have specified the calculation expression, use detail block text to specify the placement of the text string.
SHOW FORM.DETAIL
The FORM.DETAIL panel displays.
If the text you want to display on the report exceeds the report text line width, you can increase the report text line width on the FORM.OPTIONS panel. For information about the fields on the FORM.OPTIONS panel, see the QMF Reference, SC26-4716-05.
+--------------------------------------------------------------------------------+ | Put Tabular Data at Line (Enter 1-999 or NONE) ===> NONE | | LINE ALIGN DETAIL BLOCK TEXT | | ---- ------ ----+----1----+----2----+----3----+----4----+----5----+ | | 6 3 Total Earnings: &CALC1 | | 6 40 &CALC2 | +--------------------------------------------------------------------------------+
Figure 125. The changed report shows the conditional text for bonuses.
+--------------------------------------------------------------------------------+ | 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: 11043.50 | | | | ===> Number in CLERK position in Department 66: 1 | | | | Position: MGR | | Employee: LEA | | ID: 270 | | Years of Service: 9 | | Salary: 18555.50 | | Commission: 0.00 | | Total Earnings: 18555.50 | | | | ===> Number in MGR position in Department 66: 1 | | | | Position: SALES | | Employee: WILSON | | ID: 280 | | Years of Service: 9 | | Salary: 18674.50 | | Commission: 811.50 | | Total Earnings: 19486.00 *** Commission Bonus *** | | . | | . | | . | | | | | | Total Number of Employees for the WESTERN Division is 9. | | Average Salary for the WESTERN Division is 16913.69. | | | | ** Company Name ** | | *** END *** | | | +--------------------------------------------------------------------------------+
In this example, you create a report that displays some flag text whenever the data meets certain conditions. This example is very similar to the previous example that uses the FORM.CALC panel and a REXX EXEC. This time, however, you will use the FORM.CONDITIONS panel with the FORM.DETAIL panel. Using this method, you can format your report in different ways depending on conditions you want QMF to test for. You specify an expression for any tests or conditions on the FORM.CONDITIONS panel. Then you associate a FORM.DETAIL panel with each test. When the test evaluates to true for the data in a particular row, QMF formats the report the way you specify on the associated FORM.DETAIL panel. When the test does not evaluate to true, you can specify a different format on another FORM.DETAIL panel.
In this example, you create a condition that prints the text *** Needs Raise *** in the report for all employees with salary plus commissions less than $17,000.00.
To use an expression to identify a special condition:
For this example, use this query:
SELECT ID, NAME, JOB, DEPT, SALARY, COMM FROM Q.STAFF
SHOW FORM.CONDITIONS
The FORM.CONDITIONS panel displays.
For this example, you need to create two conditions; one for employees who earn greater than or equal to $17,000.00, and one for employees who earn less than $17,000.00.
Type 1 for the first expression, and 2 for the second expression.
For this example, type:
&5 + NULL(&6) >= 17000.00
This is the first condition. It means find each row for which the employee's total earnings (SALARY+COMM) are greater than or equal to $17,000.00.
Type:
&5 + NULL(&6) < 17000.00
This is the second condition. It means find each row for which the employee's total earnings are less than $17,000.00.
If you want to process nulls, you can create a REXX EXEC like NULL to substitute a 0 (or whatever value is appropriate) for the null. Then, you can use that value in the calculation.
Figure 126. Specify conditional expressions for employees' raises.
+--------------------------------------------------------------------------------+ |FORM.CONDITIONS | | | | Pass | | ID CONDITIONAL EXPRESSION Nulls? | | --- -------------------------------------------------- ------ | | 1 &5 + NULL(&6) >= 17000.00 YES | | 2 &5 + NULL(&6) < 17000.00 YES | | *** END *** | | | +--------------------------------------------------------------------------------+
Now that you have defined the conditions you want to display on the report, use the FORM.DETAIL panel to create a report variation for each condition. This type of report formatting is called conditional formatting.
You can select each report variation unconditionally, or associate it with a condition such as those you just entered on the FORM.CONDITIONS panel.
SHOW FORM.DETAIL
The FORM.DETAIL panel displays.
Or you can enter NEXT on the QMF command line.
A second FORM.DETAIL panel displays.
Figure 127. Specify detail block text to appear when the condition is true.
+--------------------------------------------------------------------------------+ | FORM.DETAIL Var 1 of 1 | | | | Include Column Headings with Detail Heading? ===> YES | | LINE ALIGN DETAIL HEADING TEXT | | ---- ------ ----+----1----+----2----+----3----+----4----+----5----+ | | 1 LEFT | | 2 LEFT | | *** END *** | | | | New Page for Detail Block? ===> NO Repeat Detail Heading? ===> NO | | Keep Block on Page? ===> NO Blank Lines after Block ===> 0 | | Put Tabular Data at Line (Enter 1-999 or NONE) ===> 1 | | LINE ALIGN DETAIL BLOCK TEXT | | ---- ------ ----+----1----+----2----+----3----+----4----+----5----+ | | 1 60 *** Needs Raise *** | | 2 LEFT | | *** END *** | | | | Select Panel Variation? ===> C2 | | | +--------------------------------------------------------------------------------+
SHOW FORM.OPTIONS
Figure 128. The changed reports shows employees who need a raise.
+--------------------------------------------------------------------------------+ | ID NAME JOB DEPT SALARY COMM | | ------ --------- ----- ------ ---------- ---------- | | | | 10 SANDERS MGR 20 18357.50 - | | 20 PERNAL SALES 20 18171.25 612.45 | | 30 MARENGHI MGR 38 17506.75 - | | 40 O'BRIEN SALES 38 18006.00 846.55 | | 50 HANES MGR 15 20659.80 - | | 60 QUIGLEY SALES 38 16808.30 650.25 | | 70 ROTHMAN SALES 15 16502.83 1152.00 | | 80 JAMES CLERK 20 13504.60 128.20 *** Needs Raise *** | | 90 KOONITZ SALES 42 18001.75 1386.70 | | 100 PLOTZ MGR 42 18352.80 - | | 110 NGAN CLERK 15 12508.20 206.60 *** Needs Raise *** | | 120 NAUGHTON CLERK 38 12954.75 180.00 *** Needs Raise *** | | 130 YAMAGUCHI CLERK 42 10505.90 75.60 *** Needs Raise *** | | 140 FRAYE MGR 51 21150.00 - | | . | | . | | . | +--------------------------------------------------------------------------------+