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 .

5.3.1 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 output will look like:

EMPLOYEE_NAME ---------------------------------------- PROJECT_NAME SUM(PH.HOURS_LOGGED) ---------------------------------------- -------------------- SUM(PH.DOLLARS_CHARGED) ----------------------- Marusia Churai Corporate Web Site 20 3380 Marusia Churai Enterprise Resource Planning System 24 4056 EMPLOYEE_NAME ---------------------------------------- PROJECT_NAME SUM(PH.HOURS_LOGGED) ---------------------------------------- -------------------- SUM(PH.DOLLARS_CHARGED) ----------------------- Marusia Churai Accounting System Implementation 24 4056 Marusia Churai Data Warehouse Maintenance 20 ...

Looks ugly, doesn't it? I wouldn't want to hand that to a client or my boss. It's a start though. At least now you can see what the data looks like, and you know what you have to work with.

5.3.2 Step 2: Format the Columns

Now that you have the data, you can begin to work through the formatting process. Look again at the listing produced in step 1. At least three things can be done to improve the presentation of the data:

• Get each record to fit on just one line.
• Use better column headings.
• Format the numbers .

The first thing that probably leaps out at you is the need to avoid having report lines so long that they wrap around onto a second line and become difficult to read. This is often a result of SQL*Plus allowing for the maximum width in each column. Another cause is that for calculated columns, the entire calculation is used for the column heading. That can result in some long headings.

5.3.2.1 Column headings

Use the COLUMN command to format the data returned from a SELECT statement. It allows you to specify heading, width, and display formats. The following commands use COLUMN's HEADING clause to specify more readable column headings for our report:

COLUMN employee_name HEADING "Employee Name" COLUMN project_name HEADING "Project Name" COLUMN SUM(PH.HOURS_LOGGED) HEADING "Hours" COLUMN SUM(PH.DOLLARS_CHARGED) HEADING "DollarsCharged"

You can refer to the calculated columns in the query by using their calculations as their names . However, doing so is cumbersome, to say the least, and requires you to keep the two copies of the calculation in sync. There is a better way. You can give each calculated column an alias and use that alias in the COLUMN commands. To give each column an alias, the changes to the query look like the following:

SUM(PH.HOURS_LOGGED) hours_logged , SUM(PH.DOLLARS_CHARGED) dollars_charged

The commands to format these two columns then become:

COLUMN hours_logged HEADING "Hours" COLUMN dollars_charged HEADING "DollarsCharged"

The heading for the dollars_charged column has a vertical bar separating the two words. This vertical bar tells SQL*Plus to place the heading on two lines and allows you to use two rather long words without the need for an excessive column width.

5.3.2.2 Numeric display formats

Next , you can specify more readable display formats for the numeric columns via the FORMAT clause. COLUMN commands are cumulative, so one approach is to execute the COLUMN commands to set the headers followed by some more COLUMN commands to set the display formats:

COLUMN employee_name HEADING "Employee Name" COLUMN project_name HEADING "Project Name" COLUMN hours_logged HEADING "Hours" COLUMN dollars_charged HEADING "DollarsCharged" COLUMN hours_logged FORMAT 9,999 COLUMN dollars_charged FORMAT 9,999.99

This cumulative approach is handy in an interactive session because you can continually refine your column formatting without having to respecify formatting that you're already happy with. In a script file, though, you're better off issuing just one COLUMN command per column, followed by all formatting options that you wish to specify for that column. This way, all formatting for a given column is in one place:

COLUMN employee_name HEADING "Employee Name" COLUMN project_name HEADING "Project Name" COLUMN hours_logged HEADING "Hours" FORMAT 9,999 COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99

5.3.2.3 Text display formats

Finally, you can use FORMAT to specify a shorter length for the employee_name and project_name columns. The database allows each of those columns to contain up to 40 characters, but a visual inspection of the output shows that the names are typically much shorter than that. The format clauses shown next make these columns each 20 characters wide:

COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED

Normally, SQL*Plus will wrap longer values onto a second line. The WORD_WRAPPED keyword keeps SQL*Plus from breaking a line in the middle of a word.

5.3.2.4 Report output after formatting the columns

Example 5-2 pulls together all the formatting and SQL changes discussed so far.

Example 5-2. Hours and dollars report with the columns nicely formatted

--Format the columns COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED COLUMN hours_logged HEADING "Hours" FORMAT 9,999 COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99 --Execute the query to generate the report. SELECT e.employee_name, p.project_name, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) 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; EXIT

Here is what the output will look like:

Employee Name Project Name Hours Charged -------------------- -------------------- ------ ------------ Marusia Churai Corporate Web Site 20 ,380.00 Marusia Churai Enterprise Resource 24 ,056.00 Planning System Marusia Churai Accounting System 24 ,056.00 Implementation Marusia Churai Data Warehouse 20 ,380.00 Maintenance

This is a great improvement over step 1. The headings are more readable. The numbers, particularly the dollar amounts, are formatted better. Most records fit on one line, and when two lines are needed, the data are wrapped in a much more readable format.

A blank line has been inserted after every record with a project name that wraps to a second line. That blank line is a record separator , and it's added by SQL*Plus every time a wrapped column is output as part of a report. I suppose it is added to prevent confusion because, in some circumstances, you might think that the line containing the wrapped column data represented another record in the report. I usually turn it off; this is the command:

SET RECSEP OFF

The next step is to add page headers and footers to the report.

5.3.3 Step 3: Add Page Headers and Footers

Page headers and footers may be added to your report through the use of the TTITLE and BTITLE commands. TTITLE and BTITLE stand for "top title" and "bottom title," respectively.

5.3.3.1 The top title

TTITLE commands typically end up being a long string of directives interspersed with text, and they often span multiple lines. Let's say you want a page header that looked like this:

The Fictional Company I.S. Department Project Hours and Dollars Report =============================================================

This heading is composed of the company name centered on the first line, two blank lines, a fourth line containing the department name and the report title, followed by a ruling line made up of equal sign characters. You can begin to generate this heading with the following TTITLE command:

TTITLE CENTER "The Fictional Company"

The keyword CENTER is a directive telling SQL*Plus to center the text that follows . In their documentation, Oracle sometimes uses the term printspec to refer to such directives.

To get the two blank lines into the title, add a SKIP printspec as follows:

TTITLE CENTER "The Fictional Company" SKIP 3

SKIP 3 tells SQL*Plus to skip forward three lines. This results in two blank lines and causes the next report line to print as the third line. To generate the fourth line of the title, containing the department name and the report name, you again add on to the TTITLE command:

TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Report"

The text "I.S. Department" will print flush left because it follows the LEFT printspec, and the report title will print flush right because it follows the RIGHT printspec. Both strings will print on the same line because there is no intervening SKIP printspec. The last thing to do is to add the final ruling line composed of equal sign characters, giving you this final version of the TTITLE command:

TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Report" SKIP 1 - LEFT "============================================================="

This is actually one long command. The hyphens at the end of the first three lines are SQL*Plus command continuation characters. There are 61 equal sign characters in the last line of the title.

5.3.3.2 The bottom title

The BTITLE command works the same way as TTITLE, except that it defines a footer to appear at the bottom of each page of the report. As with TTITLE, you should always begin a BTITLE command with a printspec such as LEFT or RIGHT, as opposed to text or a variable name. If you want a footer composed of a ruling line and a page number, you can use the following BTITLE command:

BTITLE LEFT "=============================================================" - SKIP 1 - RIGHT " Page " FORMAT 999 SQL.PNO

This BTITLE command introduces two features that haven't been shown in previous examples. The first is the FORMAT parameter, which in this case specifies a numeric display format to use for all subsequent numeric values. The second is the system variable SQL.PNO, which supplies the current page number. Table 5-1 lists several values, maintained automatically by SQL*Plus, that you can use in report headers and footers.

Table 5-1. SQL*Plus system variables

The values in Table 5-1 have meaning only to SQL*Plus and can be used only when defining headers and footers. They cannot be used in SQL statements such as INSERT or SELECT.

5.3.3.3 Setting the line width

One final point to bring up regarding page titles is that the directives RIGHT and CENTER operate with respect to the current line width. The default line width, or linesize as it is called in SQL*Plus, is 80 characters. So by default, a centered heading will be centered over 80 characters. A flush right heading will have its last character printed in the 80th position. This presents a slight problem because our report, using the column specifications given in step 2, is only 61 characters wide. The result will be a heading that overhangs the right edge of the report by 19 characters, and that won't appear centered over the data. You could choose to live with that, or you could add this command to the script:

SET LINESIZE 61

Setting the linesize tells SQL*Plus to format the headings within a 61-character line. It also tells SQL*Plus to wrap or truncate any lines longer than 61 characters, but the column specifications in this report prevent anything like that from occurring.

5.3.3.4 Report output with page titles

Example 5-3 shows our report generation script with the addition of the TTITLE, BTITLE, and SET LINESIZE commands.

Example 5-3. Hours and dollars report with page titles added

--Set the linesize, which must match the number of equal signs used --for the ruling lines in the headers and footers. SET LINESIZE 61 --Setup page headings and footings TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Report" SKIP 1 - LEFT "=============================================================" BTITLE LEFT "=============================================================" - SKIP 1 - RIGHT "Page " FORMAT 999 SQL.PNO --Format the columns COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED COLUMN hours_logged HEADING "Hours" FORMAT 9,999 COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99 --Execute the query to generate the report. SELECT e.employee_name, p.project_name, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) 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; EXIT

Executing this script will produce the following output:

The Fictional Company I.S. Department Project Hours and Dollars Report ============================================================= Dollars Employee Name Project Name Hours Charged -------------------- -------------------- ------ ------------ Marusia Churai Corporate Web Site 20 ,380.00 Marusia Churai Enterprise Resource 24 ,056.00 Planning System ============================================================= Page 1 The Fictional Company I.S. Department Project Hours and Dollars Report ============================================================= Dollars Employee Name Project Name Hours Charged -------------------- -------------------- ------ ------------ Marusia Churai Accounting System 24 ,056.00 Implementation ============================================================= Page 2 ...

Only a few things are left to clean up before you can print this report; one obvious improvement is to fix the pagination in order to get more than 14 lines per page.

5.3.4 Step 4: Format the Page

Most of the work to produce this report is behind you. Step 4 involves adjusting two SQL*Plus settings that control page size and pagination. These two settings are:

pagesize

Controls the number of lines per page. SQL*Plus prints headings and advances to a new page every pagesize lines.

newpage

Controls the size of the top margin, or tells SQL*Plus to use a formfeed character to advance to a new page.

The SET command is used to define values for each of these settings. The values to use depend primarily on your output device, the paper size being used, and the font size being used. Because SQL*Plus is entirely character-oriented, these settings are defined in terms of lines. The first question to ask, then, is how many lines will your printer print on one page of paper.

5.3.4.1 How many lines on a page?

Years ago, before the advent of laser printers with their multiplicity of typefaces , typestyles, and typesizes (i.e., fonts), this was an easy question to answer. The standard vertical spacing for printing was six lines per inch, with eight lines per inch occasionally being used. Thus, an 11-inch-high page would normally contain 66 lines. Most printers were pinfeed printers taking fanfold paper, and would permit you to print right up to the perforation, allowing you to use all 66 lines if you were determined to do so.

Today's printers are much more complicated, yet most will still print six lines per inch if you send them plain ASCII text. However (and this is important), many printers today will not allow you to print right up to the top and bottom edges of the paper. This is especially true of laser printers, which almost always leave a top and bottom margin. You may have to experiment a bit with your printer to find out exactly how many lines you can print on one page.

The other issue to consider is the font size you will be using to print the report. I typically just send reports to a printer as plain ASCII text, and that usually results in the use of a 12-point, monospaced font, which prints at six lines per inch. Sometimes, however, I'll load the file containing a report into an editor, change the font size to something larger or smaller, and then print the report. If you do that, you'll need to experiment a bit to find out how many lines will fit on a page using the new font size.

5.3.4.2 Setting the pagesize

You set the pagesize with the SQL*Plus command SET PAGESIZE as follows:

SET PAGESIZE 55

This tells SQL*Plus to print 55 lines per page. Those 55 lines include the header and footer lines as well as the data. As it prints your report, SQL*Plus keeps track of how many lines have been printed on the current page. SQL*Plus knows how many lines make up the page footer. When the number of remaining lines equals the number of lines in your footer, SQL*Plus prints the footer and advances to the next page. How SQL*Plus advances the page depends on the NEWPAGE setting.

5.3.4.3 Setting the page advance

There are two methods SQL*Plus can use to advance the printer to a new page. The first method, and the one used by default, is to print exactly the right number of lines needed to fill one page. Having done that, the next line printed will start on a new page. Using this method depends on knowing how many lines you can fit on one page, and switching printers can sometimes cause your report to break. One laser printer, for example, may have a slightly larger top margin than another.

A more reliable method is to have SQL*Plus advance the page using the formfeed character. The command to do this is:

SET NEWPAGE 0

The NEWPAGE setting tells SQL*Plus how many lines to print to advance to a new page. The default value is 1. Setting NEWPAGE to 0 causes SQL*Plus to output a formfeed character when it's time to advance the page.

The examples in this chapter use a NEWPAGE setting of 0 and a PAGESIZE of 55 lines, so you can add the following three lines to the script file:

--Set up pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55

You are free to put these lines anywhere you like so long as they precede the SELECT statement that generates the report. Example 5-4 shows them added to the beginning of the script file.

Example 5-4. Hours and dollars report with pagination

--Setup pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55 --Set the linesize, which must match the number of equal signs used --for the ruling lines in the headers and footers. SET LINESIZE 61 --Set up page headings and footings TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Report" SKIP 1 - LEFT "=============================================================" BTITLE LEFT "=============================================================" - SKIP 1 - RIGHT "Page " FORMAT 999 SQL.PNO --Format the columns COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED COLUMN hours_logged HEADING "Hours" FORMAT 9,999 COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99 --Execute the query to generate the report. SELECT e.employee_name, p.project_name, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) 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; EXIT

5.3.5 Step 5: Print It

Run the script file one more time and look at the output on the screen. If everything looks good, you are ready to print. To print a report, you need to have SQL*Plus write the report to a file and then print that file. When people speak of writing SQL*Plus output to a file, the term spool is often used as a verb. You are said to be spooling your output to a file. The SPOOL command is used for this purpose, and you will need to use it twice, once to turn spooling on and again to turn it off.

5.3.5.1 Spooling to a file

To send report output to a file, put SPOOL commands immediately before and after the SQL query as shown here:

SPOOL proj_hours_dollars.lis SELECT E.EMPLOYEE_NAME, . . . SPOOL OFF

The first SPOOL command tells SQL*Plus to begin echoing all output to the specified file. After this command executes, everything you see on the screen is echoed to this file. The second SPOOL command turns spooling off and closes the file.

You may wish to add two other commands to the script file before generating the report. The first is:

SET FEEDBACK OFF

Turning feedback off gets rid of the "50 rows selected" message, which you may have noticed at the end of the report when you ran earlier versions of the script. The second command you may want to add is:

SET TERMOUT OFF

This command does what it says. It turns off output to the display (terminal output) but allows the output to be written to a spool file. Your report will run several orders of magnitude faster if SQL*Plus doesn't have to deal with updating and scrolling the display, especially true if you are running the Windows GUI version of SQL*Plus. You should definitely use SET TERMOUT OFF when spooling any large report. I usually put the above two settings immediately prior to the SPOOL command. For example:

SET FEEDBACK OFF SET TERMOUT OFF SPOOL $HOME/sqlplus/reports/proj_hours_dollars.lst SELECT E.EMPLOYEE_NAME, . . . SPOOL OFF

Do make sure that you set TERMOUT off prior to executing the SPOOL command to spool the output; otherwise, the SET TERMOUT OFF command will appear in your spool file.

5.3.5.2 The final script

Example 5-5 shows the script for our report after adding the SPOOL commands and the commands to turn feedback and terminal output off. Example 5-5 begins with the SET ECHO OFF command , ensuring that you aren't bothered by having to watch all the remaining commands in the script scroll by on your terminal window.

Example 5-5. Hours and dollars report to be spooled to a file for printing

SET ECHO OFF --Set up pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55 --Set the linesize, which must match the number of equal signs used --for the ruling lines in the headers and footers. SET LINESIZE 61 --Set up page headings and footings TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Report" SKIP 1 - LEFT "=============================================================" BTITLE LEFT "=============================================================" - SKIP 1 - RIGHT "Page " FORMAT 999 SQL.PNO --Format the columns COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED COLUMN hours_logged HEADING "Hours" FORMAT 9,999 COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99 --Turn off feedback and set TERMOUT off to prevent the --report being scrolled to the screen. SET FEEDBACK OFF SET TERMOUT OFF --Execute the query to generate the report. SPOOL proj_hours_dollars.lst SELECT e.employee_name, p.project_name, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) 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; SPOOL OFF EXIT

5.3.5.3 Executing the report

If you've stored the script for the report in a text file, you can execute that file from the SQL*Plus prompt like this:

SQL> @ex5-5

The @ character in front of a filename tells SQL*Plus to execute the commands contained in that file.

5.3.5.4 Printing the file

After you run the script, the complete report will be in the proj_hours_dollars.lst file. To print that file, you must use whatever print command is appropriate for your operating system. On a Windows machine, assuming that LPT1 is mapped to a printer, you can use the following DOS command:

COPY c:aproj_hours_dollars.lis LPT1:

A typical Unix print command would be:

lp proj_hours_dollars.lis

An alternative is to load the file into a word processor such as Microsoft Word or Lotus Word Pro. These programs will interpret formfeeds as page breaks when importing a text file, so your intended pagination will be preserved. After you've imported the file, select all the text and mark it as Courier New 12 point. Then set the top and left margins to their minimum values; for laser printers, half-inch margins usually work well. Next, set the right and bottom margins to zero. Finally, print the report.

One final option, useful with Unix and Linux but not available in any Windows version of SQL*Plus, is to use the SPOOL OUT command instead of SPOOL OFF. SPOOL OUT closes the spool file and then prints that file out to the default printer, saving you the extra step of manually printing it. For whatever reason, Oracle has chosen not to implement SPOOL OUT under Windows. It is, however, available under Unix and Linux.