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

Designing a Simple Report

The first part of this chapter will lead you through the five steps involved in generating a simple, columnar report. This report will be complete with page headings, page footings, and column headings. In addition, you will learn about several settings, controlled by various SET commands, that are useful when printing and formatting reports.

Step 1: Formulate the Query

The first step to designing a report is to formulate the underlying SQL query. There is little point in doing anything else until you have done this. The remaining steps all involve formatting and presentation. If you haven't defined your data, there is no reason to worry about formatting.

For this chapter, let's look at developing a report that answers the following questions:

• To what projects is each employee assigned?

• How many hours have been charged to each project?

• What is the cost of those hours?

One way to satisfy these requirements would be to develop a report based on the query in Example 5-1, which summarizes the hours and dollars charged by employees to each of their projects.

Example 5-1. Summarizing hours and dollars by employee and project

SELECT e.employee_name,
       p.project_name,
       SUM(ph.hours_logged) ,
       SUM(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
GROUP BY e.employee_id, e.employee_name,
          p.project_id, p.project_name;

If you execute this query using SQL*Plus, here's what the ...