SQL*Plus allows you to print totals and subtotals in a report. To do this, you use a combination of the BREAK command and one or more COMPUTE commands. This section continues where the previous section left off. It will show you how to modify the master/detail report created earlier so that it totals the hours and dollars by project and by employee. You will also see how to print grand totals for these columns at the end of the report.
The COMPUTE command defines summary calculations needed in a report. You can use COMPUTE in conjunction with BREAK to calculate and print column totals, averages, minimum and maximum values, and so on. These calculations are performed by SQL*Plus as the report runs.
The syntax for the COMPUTE command looks like this:
COMP[UTE] [{AVG|COU[NT]|MAX[IMUM]|MIN[IMUM]|
NUM[BER]|STD|SUM|VAR[IANCE]}... [LABEL label_text]
OF column_name...
ON {group_column_name|ROW|REPORT}...]where:
- COMP[UTE]
May be abbreviated to COMP. Entering COMPUTE with no parameters causes SQL*Plus to list all currently defined computations.
- AVG
Computes the average of all non-null values for a column. AVG only applies to columns of type NUMBER.
- COU[NT]
Computes the total number of non-null values for a column. COUNT may be used with columns of any datatype, and may be abbreviated to COU.
- MAX[IMUM]
Computes the maximum value returned for a column. MAXIMUM may be abbreviated as MAX, and applies to columns of type NUMBER, CHAR, VARCHAR2, NCHAR, and NVARCHAR2.
- MIN[IMUM]
Computes the minimum value returned for a column. MINIMUM may be abbreviated as MIN, and applies to columns of type NUMBER, CHAR, VARCHAR2, NCHAR, and NVARCHAR2.
- NUM[BER]
Similar to COUNT, but computes the number of all values, including nulls. This applies to columns of any datatype, and may be abbreviated to NUM.
- STD
Computes the standard deviation of all non-null values for a column. STD applies only to columns of type NUMBER.
- SUM
Computes the sum of all non-null values for a column. SUM applies only to columns of type NUMBER.
- VAR[IANCE]
Computes the variance of all non-null values for a column. VARIANCE applies only to columns of type NUMBER, and may be abbreviated to VAR.
- LABEL label_text
Allows you to specify a label for the computed value. If possible, this label will be printed to the left of the computed value. The label text may be enclosed in quotes, either single or double. To embed a quote within the label, when that label has been quoted, place two quote characters back to back.
- column_name
Is the name of the column you are summarizing. If it is a computed column, then the expression is the name. If your SELECT statement aliases the column, then you must use that alias here.
- group_column_name
Causes SQL*Plus to restart the calculation every time this column changes. Typically, the report is sorted or grouped by this column, and then the computed value is printed once for each distinct value of the group column.
- ROW
Causes the computation to be performed once for each row returned by the query.
- REPORT
Causes the computation to be performed at the end of the report, and to include values from all rows. REPORT is used for grand totals.
The COMPUTE command is complex, and can be intimidating the first time you go to use it. In order to get any of the computed values to print, COMPUTE must be used in conjunction with the BREAK command. The next section will show you how this is done.
The COMPUTE command tells SQL*Plus what columns to summarize and over what range of records. That’s all it does. By itself, it won’t cause anything to print. In order to print the computed values, you must issue a BREAK command to define breaks on the same report elements used in your COMPUTE command. For example, if you total hours by project ID, then you need a project ID break in order to print that total.
With the BREAK command, you have only one break setting, and each new BREAK command overwrites that setting with a new one. That’s not the case with COMPUTE. Each defined computation is a separate entity, and COMPUTE commands do not overwrite each other. COMPUTE definitions also “stick” for the duration of a SQL*Plus session. A COMPUTE setting issued for one report may stick around to affect the printing of subsequent reports. SQL*Plus provides the CLEAR COMPUTES command to help you deal with this. CLEAR COMPUTES causes SQL*Plus to erase all computations so you can start over. If you are using COMPUTE in a report script, you may want to include a CLEAR COMPUTES command to avoid any possible contamination from previous reports that may have used COMPUTE.
The Project Hours and Dollars Detail report has two numeric columns showing the hours logged to a project together with the resulting dollar amount that was charged. You can easily see that it would be desirable to total these for each project and employee. The following five commands will accomplish this:
CLEAR COMPUTES COMPUTE SUM LABEL 'Totals' OF hours_logged ON project_id COMPUTE SUM LABEL 'Totals' OF dollars_charged ON project_id COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
The first command simply clears any existing computations that may be defined. The next two commands summarize the hours and dollars by project. The last two commands do the same thing except that the totals are for the employee, and cover all projects to which an employee has charged hours. Here’s how the output will look when you run the modified script:
The Fictional Company
I.S. Department Project Hours and Dollars Detail
==================================================================
Employee: 101 Jonathan Gennick
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
1001 Corporate Web Site 01-Jan-1998 1 $169.00
01-Mar-1998 3 $507.00
01-May-1998 5 $845.00
01-Jul-1998 7 $1,183.00
01-Sep-1998 1 $169.00
01-Nov-1998 3 $507.00
******* ************************** ------ ------------
Totals 20 $3,380.00
1002 Year 2000 Fixes 01-Feb-1998 7 $1,183.00
01-Apr-1998 1 $169.00
01-Jun-1998 3 $507.00
...
1005 TCP/IP Implementation 01-Jan-1998 5 $845.00
01-Mar-1998 7 $1,183.00
01-May-1998 1 $169.00
01-Jul-1998 3 $507.00
01-Sep-1998 5 $845.00
01-Nov-1998 7 $1,183.00
******* ************************** ------ ------------
Totals 28 $4,732.00
------ ------------
116 $19,604.00Notice that the label “Totals” appears in the project_id column. SQL*Plus always places the label you specify into the ON column of the COMPUTE command. The label will be formatted according to the rules specified in that column’s COLUMN command.
Tip
Only one label can be printed for project totals, though there are two COMPUTE commands. Had you specified two different labels, the first one would have taken precedence.
Why no label for the employee totals? Because this is a master/detail report, and the NOPRINT option has been used to suppress printing of the employee_name and employee_id columns. Normally, SQL*Plus would print the COMPUTE label in the employee_id column, but that can’t be done if the column is not being printed. There is really nothing you can do if you aren’t happy with this behavior. You either have to live with it or avoid master/detail reports.
The width of the label identifying the project totals is limited by the width of the project_id column, which simply won’t hold a longer, more descriptive label such as “Project Totals.” However, there are a couple of things you can do to make room for a longer label. The first and most obvious thing is simply to make the project_id column wider. Change the COLUMN command to widen the field from 7 to 14 digits:
COLUMN project_id HEADING 'Proj ID' FORMAT 99999999999999
Just be sure to bump up the linesize setting by the same amount, and also adjust the page headers and footers.
A less obvious approach would be to change the computations so the project totals are summarized for each project name rather than for each project ID. The resulting commands would be:
COMPUTE SUM LABEL 'Project Totals' OF hours_logged ON project_name COMPUTE SUM LABEL 'Project Totals' OF dollars_charged ON project_name
The output would then look like this:
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
1001 Corporate Web Site 01-Jan-1998 1 $169.00
01-Mar-1998 3 $507.00
01-May-1998 5 $845.00
01-Jul-1998 7 $1,183.00
01-Sep-1998 1 $169.00
01-Nov-1998 3 $507.00
************************** ------ ------------
Project Totals 20 $3,380.00You now have room for a more descriptive label, and there is the added benefit that it looks better with the label indented closer to the printed totals.
The REPORT keyword is used to compute and print totals for an entire report. Use REPORT in the ON clause of a COMPUTE statement to define report-level computations. Use REPORT with the BREAK command to enable a report break that will cause the report-level computed values to print.
To print grand totals for the project_hours and dollars_charged columns, add the following two lines to the script file:
COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT
Notice that instead of specifying a column in the ON clause, the keyword REPORT has been used. This tells SQL*Plus to sum the data over the entire report. Also notice that the LABEL clause has been used. Normally the label would print in the column specified in the ON clause. In cases like this where there is no ON column, SQL*Plus will place the labels in the first column of the report.
The next thing to do is to modify the BREAK command by adding a report break. Forget to do this and the report totals will not print. The final version of the BREAK command looks like this:
BREAK ON REPORT -
ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES -
ON project_id SKIP 2 NODUPLICATES -
ON project_name NODUPLICATESThe REPORT break was added to the beginning of the BREAK command because it is the outermost break. The position doesn’t really matter because SQL*Plus always makes the report break outermost, but I like to put it first anyway for the sake of clarity.
If you run the report now, the grand totals will be printed on a page by themselves at the end of the report. Here’s how that output will look:
The Fictional Company
I.S. Department Project Hours and Dollars Detail
==================================================================
Employee: 113 Jacob Marley
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
------ ------------
786 $110,779.00Notice three things about how the report totals are printed. First, notice that they printed on a page by themselves. Next, notice that the page with the grand totals still shows an employee name in the page header. Finally, notice that the “Grand Totals” label did not print as expected in the first column. I’ll explain all of these oddities next.
First the pagination issue. Before SQL*Plus executes a report break, it first executes all the other breaks. Execution begins with the innermost break and proceeds outwards until the report break actions are executed. In this example, SQL*Plus will skip two lines — the project break and the skip to a new page — the employee break, and then print the report totals. This is usually the behavior you want when printing a master/detail report. You may intend to give each employee his own section of the report so he can double-check his hours. Since the grand total doesn’t really “belong” with any one employee, you don’t want it on the pages you are giving out.
The last employee’s name printed on the page header simply because it was the last value retrieved from the database. It would be nice if SQL*Plus were smart enough to make this value null or blank, but it isn’t. The value in the header is refreshed only when a new value is read from the database, and in the case of a report break, that simply doesn’t happen. This is only an issue on master/detail reports when you use variables to include report data in the header.
The final item to notice, and the only one you can do anything about, is the lack of a label for the grand total values. I did say that SQL*Plus puts the label for report-level calculations in the first column of the report. Contrary to what you might intuitively expect, SQL*Plus bases the first column on the SELECT statement, not on what is actually printed. When this report was converted to a master/detail report, printing of the first two columns was suppressed using the NOPRINT clause of the COLUMN command. No employee_id column, no “Grand Totals” label.
Since the employee_id and employee_name columns are not being printed, their position in the SELECT statement is irrelevant. You can move them to the end, making project_id the first column, widen the project_id column to hold 12 characters instead of 7, and the “Grand Totals” label will now print in the first column of the report.
The final listing, complete with the changes that allow the “Grand Totals” label to print, is shown next. To widen the project_id column to accommodate 12 characters, 5 extra leading spaces were inserted into the project_id column title. The linesize was also adjusted from 66 to 71, and 5 equal-sign characters were added to the ruling lines in the header and footer.
--Setup pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55
--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 71
--Setup page headings and footings
TTITLE CENTER 'The Fictional Company' SKIP 3 -
LEFT 'I.S. Department' -
RIGHT 'Project Hours and Dollars Detail' SKIP 1 -
LEFT `=============================================================' -
SKIP 2 'Employee: ' FORMAT 9999 emp_id_var ' ' emp_name_var SKIP 3
BTITLE LEFT `=============================================================' -
SKIP 1 -
RIGHT 'Page ' FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_id NEW_VALUE emp_id_var NOPRINT
COLUMN employee_name NEW_VALUE emp_name_var NOPRINT
COLUMN project_id HEADING ' Proj ID' FORMAT 9999
COLUMN project_name HEADING 'Project Name' FORMAT A26 WORD_WRAPPED
COLUMN time_log_date HEADING 'Date' FORMAT A11
COLUMN hours_logged HEADING 'Hours' FORMAT 9,999
COLUMN dollars_charged HEADING 'Dollars|Charged' FORMAT $999,999.99
--Breaks and Computations
BREAK ON REPORT -
ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES -
ON project_id SKIP 2 NODUPLICATES -
ON project_name NODUPLICATES
CLEAR COMPUTES
COMPUTE SUM LABEL 'Project Totals' OF hours_logged ON project_name
COMPUTE SUM LABEL 'Project Totals' OF dollars_charged ON project_name
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT
COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT
--Execute the query to generate the report.
SELECT P.PROJECT_ID,
P.PROJECT_NAME,
TO_CHAR(PH.TIME_LOG_DATE,'dd-Mon-yyyy') time_log_date,
PH.HOURS_LOGGED,
PH.DOLLARS_CHARGED,
E.EMPLOYEE_ID,
E.EMPLOYEE_NAME
FROM EMPLOYEE E,
PROJECT P,
PROJECT_HOURS PH
WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
AND P.PROJECT_ID = PH.PROJECT_ID
ORDER BY E.EMPLOYEE_ID, P.PROJECT_ID, PH.TIME_LOG_DATE;Here is the output produced by the final version of this script. Notice that the “Grand Totals” label does appear in the project_id column at the end of the report.
The Fictional Company
I.S. Department Project Hours and Dollars Detail
=======================================================================
Employee: 101 Jonathan Gennick
Dollars
Proj ID Project Name Date Hours Charged
------------ -------------------------- ----------- ------ ------------
1001 Corporate Web Site 01-Jan-1998 1 $169.00
01-Mar-1998 3 $507.00
01-May-1998 5 $845.00
01-Jul-1998 7 $1,183.00
01-Sep-1998 1 $169.00
01-Nov-1998 3 $507.00
************************** ------ ------------
Project Totals 20 $3,380.00
...detail for several projects 1005 TCP/IP Implementation 01-Jan-1998 5 $845.00
01-Mar-1998 7 $1,183.00
01-May-1998 1 $169.00
01-Jul-1998 3 $507.00
01-Sep-1998 5 $845.00
01-Nov-1998 7 $1,183.00
************************** ------ ------------
Project Totals 28 $4,732.00
************ ------ ------------
116 $19,604.00
...several pages of output The Fictional Company
I.S. Department Project Hours and Dollars Detail
=======================================================================
Employee: 113 Jacob Marley
Dollars
Proj ID Project Name Date Hours Charged
------------ -------------------------- ----------- ------ ------------
------ ------------
Grand Totals 786 $110,779.00When printing totals and grand totals, be sure that the summarized columns are wide enough to accommodate the final totals. None of the individual “Dollars Charged” values in this report required more than four digits to the left of the decimal, but the final total required six.