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.

     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net