Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Chapter 7. Advanced Reports

Beyond master/detail reports, which you learned about in Chapter 5, there are several other advanced and less often used reporting techniques that you should know about to exploit the full power of SQL*Plus. This chapter shows you how to design totals and subtotals into a report. You'll also learn some other useful tricks and techniques involving SQL*Plus and SQL.

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 "========================================" - "==========================" ...