Chapter 7 Reports

1. Chapter 7Reports

2. Concatenating Columns • Concatenation • the process of combining two or more character columns into a single expression • To concatenate • type two vertical lines (||) between the column names

3. Using Concatenation

4. RTRIM Function • When the first name doesn’t include sufficient characters to fill the width of the column, SQL inserts extra spaces • To remove extra spaces, use the right trim function (RTRIM function) SELECT SLSREP_NUMBER, RTRIM(FIRST) || ‘ ‘ ||RTRIM(LAST) FROM SALES_REP;

5. Changing Column Headings

6. CLEAR COLUMNS Command • Use CLEAR COLUMNS command to clear any previous column changes • Without this command any previous changes made to column headings would still be in place

7. Column Headings Changed

8. COLUMN Command • Use COLUMN command to • change column headings • change width of a column • change the way entries appear in a column

9. Changing Formats

10. Report With Formatting Changes

11. Adding a Title to a Report

12. TTITLE and BTITLE Command • TTITLE command • Adds a title to the top of the report • BTITLE command • Adds a title to the bottom of the report

13. SET LINESIZE Command • In order for the table to appear appropriately, adjust the line size by using the SET LINESIZE command • Line size determines where the title appears when it is centered across the line • SET LINESIZE 70 set the line to a length of 70 characters

14. Grouping Data in a Report • BREAK Command • Identifies a column or collection of columns on which to group data • Value of column is displayed only at the beginning of the group

15. Adding a Break

16. Breaks Added to Report

17. Including Total and Subtotals in a Report • A total that appears after each group is called a subtotal • To calculate a subtotal, the BREAK command must be used to group the rows • Use the COMPUTE command to indicate the computation for the subtotal

18. Statistical Functions

19. Adding Computations to a Report

20. Report Command Summary