Report Formatting
While a DBA or an application developer who is familiar with the data can interpret terse column names such as MGR_NO and ST_ID, these column names may not be very intuitive for employees in the Accounting department. Similarly, consider a query like this:
select last_name from employees where department_id = 80;
Its output does not make it clear that the query output is only for the Sales department, unless you have all the department numbers memorized!
Reports generated from SQL queries are much more readable and understandable when you use descriptive column names and report headers and footers. The added features of the iSQL*Plus, SQL*Plus, and SQL*Plus Worksheet environment provide this functionality.
In this section, you’ll learn how to add headers and footers. You’ll also find another way to create descriptive column names. In previous chapters, the examples used column aliases to change column names in the SQL query output. Using the COLUMN command, you can provide the column alias function along with other formatting. Next, you’ll see how the BREAK command can suppress the output of duplicate column values, making a report much more readable. Finally, you’ll learn how the COMPUTE command gives totals in a report.
Defining column aliases, changing system variables, and computing totals stays in effect only for the duration of the iSQL*Plus, SQL*Plus or SQL*Plus Worksheet session. You’ll see how to save and retrieve some of these settings later in this chapter in the “Saving and Running Scripts” section.
| Note | Unless specified otherwise in this chapter, all command formats and options are valid in all three environments: iSQL*Plus, SQL*Plus, and SQL*Plus. However, the examples throughout the chapter focus on the iSQL*Plus environment. |
Headers and Footers
The TTITLE and BTITLE commands provide a flexible way to generate report headers and footers. In addition to specifying text to appear in the header and footer, this text can be centered, left-justified, or right-justified. Header and footer text can also extend to two or more lines.
Using TTITLE
The syntax of the TTITLE command is as follows:
TTI[TLE] [option [text] ...] [ON|OFF]
The option part of the TTITLE command specifies what you’re doing with the header, such as justifying the text. The text part of the command is where you specify the text to be placed in the header. You can specify ON or OFF to turn the header on or off. Even if you temporarily turn off the header, the values you specified with the TTITLE command will be retained and will be back in effect the next time you turn the header back on.
At Scott’s widget company, Janice, the application developer and DBA, has been reviewing some of her old queries to see if she can use some of the reporting capabilities to better advantage when she generates reports for King, the boss. Janice digs up the query that produces the salary report by department, sorted by descending salary within department:
select department_id "Dept", last_name || ‘, ‘ || first_name "Employee", salary "Salary" from employees order by department_id asc, salary desc; Dept Employee Salary ----- ------------------------------ ---------- 10 Whalen, Jennifer 4400 20 Hartstein, Michael 13000 20 Fay, Pat 6000 30 Raphaely, Den 11000 30 Khoo, Alexander 3100 30 Baida, Shelli 2900 30 Tobias, Sigal 2800 30 Himuro, Guy 2600 30 Colmenares, Karen 2500 40 Mavris, Susan 6500 ... 100 Chen, John 8200 100 Urman, Jose Manuel 7800 100 Sciarra, Ismael 7700 100 Popp, Luis 6900 110 Higgins, Shelley 12000 110 Gietz, William 8300 Grant, Kimberely 7000 107 rows selected.
Janice wants to make the report more readable by using some of the reporting features of iSQL*Plus. She also knows that King usually wants to see only departments 30 and 60 in the report. She adds an IN clause to the query plus a left-justified report title:
ttitle left ‘Department Salary Report’ select department_id "Dept", last_name || ‘, ‘ || first_name "Employee", salary "Salary" from employees where department_id in (30,60) order by department_id asc, salary desc;
The LEFT option in the TTITLE command left-justified the header above the report. Notice also that there is no semicolon after the TTITLE command; since TTITLE is an iSQL*Plus command, it is terminated automatically at the end of a line, unless the - continuation character is specified.
Using BTITLE
The BTITLE command has the same syntax as the TTITLE command. It specifies the text to appear at the end of an iSQL*Plus report. Janice adds a report footer to the report she has been so diligently revising for the boss, in addition to removing the feedback returned from the SELECT query:
set feedback off ttitle left ‘Department Salary Report’ btitle left ‘End Salary Report’ skip 1 - left ‘Widgets-R-Us, Inc.’ select department_id "Dept", last_name || ‘, ‘ || first_name "Employee", salary "Salary" from employees where department_id in (30,60) order by department_id asc, salary desc;
In the BTITLE command, notice how Janice not only splits the iSQL*Plus command to a second line, but also specifies more than one line in the report footer by using the SKIP n option to skip to the next line. In other words, the report output will skip to the next line before displaying additional text in the report footer. The BTITLE command would also work just fine if it were all on one line. Janice split it up so that the report specification was more readable to whomever may modify this report in the future.
Column Formatting
The COLUMN command in iSQL*Plus has the following syntax:
COL[UMN] [{column|expr} [option ...]] You can specify aliases for column headings in a query when an alias specified as part of a SELECT statement itself is not sufficient. For example, you might want the column alias to appear on two lines above the column’s data instead of on just one. The column values themselves can be formatted as left-justified, right-justified, or centered. Numeric values that represent dollar amounts can be formatted with the dollar sign character ($).
Janice makes some additional changes in the iSQL*Plus report she has been working on all morning. She adds two COLUMN commands: one to specify a new column alias for the department number column and the other to format the salary amounts with a dollar sign.
set feedback off ttitle left ‘Department Salary Report’ btitle left ‘End Salary Report’ skip 1 - left ‘Widgets-R-Us, Inc.’ column Dept heading ‘Dept|Number’ column salary format $999,999.99 select department_id "Dept", last_name || ‘, ‘ || first_name "Employee", salary "Salary" from employees where department_id in (30,60) order by department_id asc, salary desc;
In the first COLUMN command, Janice is using a heading separator. When iSQL*Plus formats this column heading, the heading separator splits the heading so it appears on multiple lines. The default heading separator is the vertical bar character (|), but you can change this on the System Variables page in iSQL*Plus or by using the SET HEADSEP command in iSQL*Plus, SQL*Plus, or SQL*Plus Worksheet. Notice that the heading separator character does not appear in the output.
heading separator
A single character embedded in an iSQL*Plus column alias that indicates where the alias is split to appear on multiple lines in the output. The heading separator itself does not appear in the output.
Note that the iSQL*Plus column alias operation is being applied to the alias in the SELECT statement itself ("Dept"). The COLUMN command does not care if the column heading coming from the SELECT statement is the actual column name or an alias applied by the SELECT statement; it will substitute its own new alias to matching column names from the SELECT statement.
The second COLUMN statement applies a numeric format to the "Salary" column, displaying it as a dollar amount.
BREAK Processing
The values in a particular column may repeat, for example, in a report containing employees with their department numbers. To make the report more readable, it’s often desirable to suppress duplicate values in columns like these until the value in this column changes. The iSQL*Plus BREAK command facilitates the suppression of duplicate values for a given column in a report. The syntax for the BREAK command is as follows:
BRE[AK] [ON report_element]
| Tip | BREAK commands are almost always applied to columns that are sorted. |
Janice knows that there is always room for improvement. She also knows that, at some point, the boss will be asking her to make it clearer when the department number changes on her most recent iSQL*Plus report. To remove the extra department numbers, she adds a BREAK command, as follows:
set feedback off ttitle left ‘Department Salary Report’ btitle left ‘End Salary Report’ skip 1 - left ‘Widgets-R-Us, Inc.’ column Dept heading ‘Dept|Number’ column salary format $999,999.99 break on Dept select department_id "Dept", last_name || ‘, ‘ || first_name "Employee", salary "Salary" from employees where department_id in (30,60) order by department_id asc, salary desc;
The report is significantly more readable, and the boss can easily spot where the rows for department 60 begin in the report.
Summary Operations (Totals)
iSQL*Plus provides the capability to provide running and final totals to any report by using the COMPUTE command. The COMPUTE command has the following format:
COMP[UTE] [function [LAB[EL] text] ... OF {expr|column|alias} ... ON {expr|column|alias|REPORT|ROW} ...] You can attach specific labels to each subtotal by using the LABEL subclause. The function clause can be any of a number of aggregate functions, such as SUM, AVG, MIN, MAX, and so forth. The summary operation can occur when a column value changes or at the end of the report.
Janice is anticipating the next request from her boss, and decides to modify her report further to provide the sum of salaries by department and across all departments specified in the report. She will need two new COMPUTE statements and a change to the BREAK statement:
set feedback off ttitle left ‘Department Salary Report’ btitle left ‘End Salary Report’ skip 1 - left ‘Widgets-R-Us, Inc.’ column Dept heading ‘Dept|Number’ column salary format $999,999.99 break on Dept on Report compute sum label ‘Dept Total’ - of salary on Dept compute sum label ‘All Depts’ - of salary on Report select department_id "Dept", last_name || ‘, ‘ || first_name "Employee", salary "Salary" from employees where department_id in (30,60) order by department_id asc, salary desc;
The on Report clause was added to the BREAK command so that totals would be generated by the COMPUTE statement that follows it. Janice only "breaks" on the report once, but she still needs to specify it, because the COMPUTE statement performs the aggregate operation only at a BREAK in a report. The COMPUTE statements in Janice’s revised report perform a sum of the salary amounts and provide a custom label when the department salary sum is displayed on the report.
