Summary Reports
Sometimes you are interested only in summarized information. Maybe you only need to know the total hours each employee has spent on each project, and you don't care about the detail of each day's charges. Whenever that's the case, you should write your SQL query to return summarized data from Oracle.
Here is the query used in the master/detail report shown in Example 7-4:
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;
This query brings down all the detail information from the project_hours table, and is fine if you need that level of detail. However, if all you are interested in are the totals by employee and project, you can use the following query instead:
SELECT p.project_id, p.project_name, TO_CHAR(MAX(ph.time_log_date),'dd-Mon-yyyy') time_log_date, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) 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 GROUP BY e.employee_id, e.employee_name, p.project_id, p.project_name UNION ALL SELECT NULL, NULL, NULL, NULL, NULL, NULL, 'Grand Totals' FROM dual ORDER BY employee_id NULLS LAST, project_id, time_log_date;
You can practically plug this second query into Example 7-4 in place of the first. You would need to make only two other changes. First, you would eliminate the project breaks and computations , changing the BREAK and COLUMN commands:
BREAK ON REPORT - ON employee_id SKIP PAGE NODUPLICATES - ON employee_name NODUPLICATES 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
Then you might change the title of the date field, which represents the most recent date an employee worked on a project, to something more descriptive:
COLUMN time_log_date HEADING 'Last DateWorked' FORMAT A11
Finally, you might change the title from Project Hours and Dollars Detail to Project Hours and Dollars Summary . Example 7-6 shows the resulting script.
Example 7-6. A summary report
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 "Last DateWorked" 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 COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id 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 Summary" 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 oracle@gennick02:~/sqlplus/ExampleScripts> oracle@gennick02:~/sqlplus/ExampleScripts> cat ex7-6.sql 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 Summary" 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 "Last DateWorked" 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 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(MAX(ph.time_log_date),'dd-Mon-yyyy') time_log_date, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) 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 GROUP BY e.employee_id, e.employee_name, p.project_id, p.project_name 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
The resulting output would look like this:
The Fictional Company I.S. Department Project Hours and Dollars Summary ======================================================================= Employee: 101 Marusia Churai Last Date Dollars Proj ID Project Name Worked Hours Charged ------------ -------------------------- ----------- ------ ------------ 1001 Corporate Web Site 01-Nov-2004 20 ,380.00 1002 Enterprise Resource 01-Dec-2004 24 ,056.00 Planning System ... The Fictional Company I.S. Department Project Hours and Dollars Summary ======================================================================= Employee: Grand Totals Last Date Dollars Proj ID Project Name Worked Hours Charged ------------ -------------------------- ----------- ------ ------------ ------ ------------ Grand Totals 786 0,779.00
By letting the database handle the project-level summarization, you save time and paper. You save time because SQL*Plus doesn't need to pull all that data down from the database, and you save paper because you don't print all the unneeded detail.
