Using QMF

Showing totals across rows in a report

You can display a report that gives a total or average across the rows in a report by using column usage codes. Totals and averages are examples of QMF's aggregation functions, which are any functions that summarize the data in a column. You can also specify other aggregation usages, such as standard deviation, percent, or cumulative totals. For more information on aggregation functions, see the QMF Reference, SC26-4716-05.

In this example, you use the ACROSS, GROUP, SUM, and OMIT usage codes to create a report that summarizes the salary, commission, and total earnings for each job description within every department.

To summarize report data:

1. Run the query to display the report:

   For this example, run the following SQL query:

    SELECT NAME, DEPT, JOB, SALARY, COMM, SALARY + COMM
      FROM Q.STAFF
      WHERE DEPT IN (15, 20, 38) AND JOB <> 'MGR'
      ORDER BY DEPT, JOB
   

2. On the QMF command line, enter:

   SHOW FORM.COLUMNS
   

   The FORM.COLUMNS panel displays.

3. Type any changes to the column names in the COLUMN HEADING field.

   For this example, change the column name that is created by the query to TOTAL_EARNINGS.

4. Type the usage codes and other changes for the columns. For this example, make the following changes:
   1. Specify GROUP for the DEPT column to group your data by department. You must group your data by at least one column. Be sure to order your data by that column.
   2. Specify ACROSS for the JOB column to summarize salary, commission, and total earnings by job.
   3. Specify SUM for the SALARY, COMM, and TOTAL_EARNINGS columns.
   4. Specify OMIT for the NAME column, because you do not want to display it on the report. When you use the GROUP usage code, if you leave a column usage blank, that column does not appear on the report.
   5. Specify D2 in the EDIT field for the SALARY, COMM, and TOTAL_EARNINGS columns.
   6. Change the width of the SALARY column to 11.

   Figure 131. Use GROUP and ACROSS usage codes to summarize data in a report.
   

   +--------------------------------------------------------------------------------+
   | FORM.COLUMNS                                         MODIFIED                  |
   |                                                                                |
   |  NUM COLUMN HEADING                           USAGE   INDENT WIDTH EDIT  SEQ   |
   |  --- ---------------------------------------- ------- ------ ----- ----- ---   |
   |    1 NAME                                     OMIT    2      9     C     1     |
   |    2 DEPT                                     GROUP   2      6     L     2     |
   |    3 JOB                                      ACROSS  2      5     C     3     |
   |    4 SALARY                                   SUM     2      11    D2    4     |
   |    5 COMM                                     SUM     2      10    D2    5     |
   |    6 TOTAL_EARNINGS                           SUM     2      12    D2    6     |
   |      *** END ***                                                               |
   |                                                                                |
   +--------------------------------------------------------------------------------+

5. On the QMF command line, enter:

   SHOW FORM.OPTIONS
   

   The FORM.OPTIONS panel displays.

6. In the Automatic reordering of report columns? field, type YES. If you do not make this change, your report displays with a warning at the top.
7. Press the Report function key to see the changed report.
8. For this example, press the Right function key to see the summary column.

   Figure 132. Pressing the Right function key displays the rest of the summarized data.
   

   +--------------------------------------------------------------------------------+
   |           <------------------------------------ JOB ---------------------------|
   |           <--------------- CLERK --------------->  <--------------- SALES -----|
   |                                            SUM                                 |
   |                   SUM          SUM        TOTAL            SUM          SUM    |
   |     DEPT         SALARY        COMM      EARNINGS         SALARY        COMM   |
   |   ------    -----------  ----------  ------------    -----------  ----------  -|
   |       15     $24,766.70     $316.70    $25,083.40     $16,502.83   $1,152.00   |
   |       20     $27,757.35     $254.70    $28,012.05     $18,171.25     $612.45   |
   |       38     $24,964.50     $416.50    $25,381.00     $34,814.30   $1,496.80   |
   |             ===========  ==========  ============    ===========  ==========  =|
   |              $77,488.55     $987.90    $78,476.45     $69,488.38   $3,261.25   |
   +--------------------------------------------------------------------------------+