Totals and Subtotals
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 with the master/detail report last shown in Chapter 5, in Example 5-8. It will show you how to modify that master/detail report so it totals the hours and dollars by project and by employee. You will see how to print grand totals for these columns at the end of the report.
To refresh your memory, Example 7-1 repeats the Project Hours and Dollars Detail report from Example 5-8.
Example 7-1. Master/detail report showing a breakdown of time billed to projects
SET ECHO OFF SET RECSEP OFF --Set up pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55 --Set the linesize, which must match the number of equals signs used --for the ruling lines in the headers and footers. SET LINESIZE 66 --Set up 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 "DollarsCharged" FORMAT 9,999.99 --Breaks and computations BREAK ON employee_id SKIP PAGE NODUPLICATES - ON employee_name NODUPLICATES - ON project_id SKIP 2 NODUPLICATES - ON project_name NODUPLICATES --Execute the query to generate the report. SELECT e.employee_id, e.employee_name, p.project_id, p.project_name, TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date, ph.hours_logged, ph.dollars_charged FROM employee e INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id ORDER BY e.employee_id, p.project_id, ph.time_log_date; EXIT
The output from Example 7-1 looks like this:
The Fictional Company I.S. Department Project Hours and Dollars Detail ================================================================== Employee: 101 Marusia Churai Dollars Proj ID Project Name Date Hours Charged ------- -------------------------- ----------- ------ ------------ 1001 Corporate Web Site 01-Jan-2004 1 9.00 01-Mar-2004 3 7.00 01-May-2004 5 5.00 01-Jul-2004 7 ,183.00 01-Sep-2004 1 9.00 01-Nov-2004 3 7.00 1002 Enterprise Resource 01-Feb-2004 7 ,183.00 Planning System 01-Apr-2004 1 9.00
7.1.1 Printing Subtotals
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. To total these for each project and employee would be desirable. To accomplish that goal, you can use the COMPUTE command, which is a SQL*Plus command to define summary calculations in a report. The following four commands generate subtotals for hours and dollars, by project and employee:
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 two COMPUTE commands summarize the hours and dollars by project. Those project totals print whenever the project_id value changes. To help you understand this, let me break the first COMPUTE command down into more digestible pieces:
COMPUTE SUM
Generate a running total incremented for each detail row that SQL*Plus outputs as part of the report. The OF clause specifies the source of the values to be totaled.
LABEL 'Totals '
Use this text to identify that total when it prints on the report.
OF hours_logged
Take values from this column.
ON project_id
Reset the accumulated total to zero whenever the project_id changes.
The COMPUTE command controls the accumulation of running totals, but if you read this detailed breakdown carefully , you'll see no mention of COMPUTE causing anything to print. It is the BREAK command that causes the accumulated values to print. You have to interpret COMPUTE commands in the context of the BREAK setting for the report in question. That BREAK setting for the Project Hours and Dollars Detail report is as follows :
BREAK ON employee_id SKIP PAGE NODUPLICATES - ON employee_name NODUPLICATES - ON project_id SKIP 2 NODUPLICATES - ON project_name NODUPLICATES
The report breaks once for each employee. For each employee, the report breaks once per project. The first two COMPUTE commands are ON project_id , and a change in project_id corresponds to a new project. At the end of each project, then, SQL*Plus does the following:
- Displays the accumulated totals from hours_logged and dollars_charged
- Resets those accumulators to zero in preparation for the next project
The last two COMPUTE commands accumulate hours_logged and dollars_charged , not for each project but for each employee. Those COMPUTEs are ON employee_id , which means the accumulators will be reset to zero each time the employee changes. The BREAK ON employee_id causes the just finished employee's totals to print before the accumulators are reset.
Example 7-2 shows the Project Hours and Dollars Detail report with the COMPUTE commands added.
Example 7-2. Master/detail report with totals and subtotals
SET ECHO OFF SET RECSEP OFF --Set up pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55 --Set the linesize, which must match the number of equals signs used --for the ruling lines in the headers and footers. SET LINESIZE 66 --Set up 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 "DollarsCharged" FORMAT 9,999.99 --Breaks and computations BREAK ON employee_id SKIP PAGE NODUPLICATES - ON employee_name NODUPLICATES - ON project_id SKIP 2 NODUPLICATES - ON project_name NODUPLICATES 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 --Execute the query to generate the report. SELECT e.employee_id, e.employee_name, p.project_id, p.project_name, TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date, ph.hours_logged, ph.dollars_charged FROM employee e INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id ORDER BY e.employee_id, p.project_id, ph.time_log_date; EXIT
Here's how the output from Example 7-2 looks:
The Fictional Company I.S. Department Project Hours and Dollars Detail ================================================================== Employee: 101 Marusia Churai Dollars Proj ID Project Name Date Hours Charged ------- -------------------------- ----------- ------ ------------ 1001 Corporate Web Site 01-Jan-2004 1 9.00 01-Mar-2004 3 7.00 01-May-2004 5 5.00 01-Jul-2004 7 ,183.00 01-Sep-2004 1 9.00 01-Nov-2004 3 7.00 ******* ************************** ------ ------------ Totals 20 ,380.00 ... 1005 VPN Implementation 01-Jan-2004 5 5.00 01-Mar-2004 7 ,183.00 01-May-2004 1 9.00 01-Jul-2004 3 7.00 01-Sep-2004 5 5.00 01-Nov-2004 7 ,183.00 ******* ************************** ------ ------------ Totals 28 ,732.00 ------ ------------ 116 ,604.00
Notice that the label "Totals" appears in the project_id column. SQL*Plus places the label you specify into the ON column of the associated COMPUTE command. The label is formatted according to the rules specified in that column's COLUMN command.
|
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. You can do nothing if you aren't happy with this behavior. You 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 won't hold a longer, more descriptive label such as "Project Totals". However, you can do a couple of things to make room for a longer label. The first and most obvious thing is 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 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. This, of course, presumes that no two projects share the same name. The necessary COLUMN commands would be the following:
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:
Proj ID Project Name Date Hours Charged ------- -------------------------- ----------- ------ ------------ 1001 Corporate Web Site 01-Jan-2004 1 9.00 01-Mar-2004 3 7.00 01-May-2004 5 5.00 01-Jul-2004 7 ,183.00 01-Sep-2004 1 9.00 01-Nov-2004 3 7.00 ******* ************************** ------ ------------ Project Totals 20 ,380.00
You now have room for a more descriptive label, and the added benefit is that the report looks better with the label indented closer to the printed totals.
7.1.2 Printing Grand Totals
Use the REPORT keyword in the ON clause of a COMPUTE statement to define report-level computations. For example, you might wish to generate a grand total for hours and dollars columns in the Project Hours and Dollars Detail report. For report-level computations to print, you need to generate a report-level break, which you do using the REPORT keyword in the BREAK command.
To print grand totals for the project_hours and dollars_charged columns, add the following two lines to the script shown in Example 7-2:
COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT
Instead of specifying a column in the ON clause, I've used the keyword REPORT. This tells SQL*Plus to sum the data over the entire report. Notice the use of the LABEL clause. Normally, the label would print in the column specified in the ON clause. In cases like this where no ON column exists, 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 NODUPLICATES
The 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.
Example 7-3 shows the report as it stands now.
Example 7-3. The report script modified to generate grand totals
SET ECHO OFF SET RECSEP OFF --Set up pagesize parameters SET NEWPAGE 1 SET PAGESIZE 55 --Set the linesize, which must match the number of equals signs used --for the ruling lines in the headers and footers. SET LINESIZE 66 --Set up 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 "DollarsCharged" FORMAT 9,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 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 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 e.employee_id, e.employee_name, p.project_id, p.project_name, TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date, ph.hours_logged, ph.dollars_charged FROM employee e INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id ORDER BY e.employee_id, p.project_id, ph.time_log_date; EXIT
Run the report in Example 7-3, and 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 Mykhailo Verbytsky Dollars Proj ID Project Name Date Hours Charged ------- -------------------------- ----------- ------ ------------ ------ ------------ 786 0,779.00
Notice three things about how the report totals are printed. First, they print on a page by themselves. Next, the page with the grand totals still shows an employee name in the page header. Finally, the "Grand Totals" label does not print as expected in the first column. I'll explain all of these oddities next.
7.1.2.1 Grand totals and pagination
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 for the project break and will skip to a new page for the employee break. Then SQL*Plus prints 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 check his hours. Because the grand total doesn't "belong" with any one employee, you don't want it on the pages you are giving out.
7.1.2.2 Grand totals and the final detail record
The last employee's name printed on the page header 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 doesn't happen. This is an issue on master/detail reports only when you use variables to include report data in the header.
One possible solution to this problem of the last set of detail values being carried forward into the grand total row is to use UNION ALL to forcibly add a row of nulls to the end of your result set. For example, you can replace the query in Example 7-3 with the following:
SELECT e.employee_id, e.employee_name, p.project_id, p.project_name, TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date, ph.hours_logged, ph.dollars_charged FROM employee e INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id UNION ALL SELECT NULL, 'Grand Totals', NULL, NULL, NULL, NULL, NULL FROM dual ORDER BY employee_id NULLS LAST, project_id, time_log_date;
If you have downloaded the example scripts for this book, you'll find this solution in ex7-3b.sql . The result of executing that script is shown in the following two pages at the end of the report:
The Fictional Company I.S. Department Project Hours and Dollars Detail ================================================================== Employee: Grand Totals Dollars Proj ID Project Name Date Hours Charged ------- -------------------------- ----------- ------ ------------ ******* ************************** ------ ------------ Totals ------ ------------ ... The Fictional Company I.S. Department Project Hours and Dollars Detail ================================================================== Employee: Grand Totals Dollars Proj ID Project Name Date Hours Charged ------- -------------------------- ----------- ------ ------------ ------ ------------ 786 0,779.00
This solution is a bit of a hack. It does work, but it generates one extra page of output. The use of NULLS LAST in the query's ORDER BY clause will force the null record to the end of the result set. The second-to-last page of the report represents that record as SQL*Plus attempts to report the hours and dollars for the nonexistent employee named "Grand Totals". Of course, there is no detail for that employee, so that page is blank except for the page and column titles and some horizontal rules related to the COLUMN command.
As before, the final page of the report displays a grand total. The employee name is still carried forward from the final record in the result set, but that final record is the one manufactured by the UNION ALL query. Just to add clarity to the report, I gave that null record one non-null column; I specified the employee_name as ' Grand Totals ' because I knew it would print on this final page where it helps identify the final totals as two grand totals.
|
7.1.2.3 Grand totals and the lack of a label
The final item to notice about the output from Example 7-3 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 printed. When this report was converted to a master/detail report, printing the first two columns was suppressed using the NOPRINT clause of the COLUMN command. No employee_id column, no "Grand Totals" label.
|
Because 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) and 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.
Example 7-4 shows the final listing, complete with the changes that allow the "Grand Totals" label to print. The listing incorporates the solution described in the preceding section, so the final employee's name doesn't carry over to the grand total page. To widen the project_id column to accommodate 12 characters, five extra leading spaces were inserted into the project_id column title. The linesize was adjusted from 66 to 71, and five equal sign characters were added to the ruling lines in the header and footer.
Example 7-4. A grand total solution that labels the grand totals
SET ECHO OFF SET RECSEP OFF --Set up pagesize parameters SET NEWPAGE 1 SET PAGESIZE 55 --Set the linesize, which must match the number of equals signs used --for the ruling lines in the headers and footers. SET LINESIZE 71 --Set up 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 "DollarsCharged" FORMAT 9,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 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 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 INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, NULL, 'Grand Totals' FROM dual ORDER BY employee_id NULLS LAST, project_id, time_log_date; EXIT
Following is the final page of output produced by Example 7-4. The "Grand Totals" label does indeed appear in the project_id column when the grand totals print. The employee name is listed as "Grand Totals", which is the result of the UNION ALL solution described in the previous section.
The Fictional Company I.S. Department Project Hours and Dollars Detail ======================================================================= Employee: Grand Totals Dollars Proj ID Project Name Date Hours Charged ------------ -------------------------- ----------- ------ ------------ ------ ------------ Grand Totals 786 0,779.00
When printing totals and grand totals, be sure 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.
