Chapter 7 Reports

1. Chapter 7Reports

2. Objectives • Use concatenation in a query • Create a view for a report • Create a query for a report • Change column headings and formats in a report

3. Objectives • Add a title to a report • Group data in a report • Include totals and subtotals in a report • Send a report to a file that can be printed

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

5. Concatenating Columns • Problem: • List the number and name of each sales rep. The name should be a concatenation of the FIRST_NAME and LAST_NAME columns. • Solution: • SELECT REP_NUM, FIRST_NAME||LAST_NAMEFROM REP;

6. Concatenating Columns • When the first name does not include sufficient characters to fill the width of the column, SQL inserts extra spaces • To remove these extra spaces, the RTRIM (right trim) function can be used

7. Creating and Using Scripts • When entering report formatting commands, it is a good idea to save the commands in script files for future use • Otherwise, the commands must be reentered every time you want to produce the same report

8. Creating and Using Scripts • In SQL*Plus, an editor can be used to create script files by typing: • The EDIT command • The name of the file to be created • Oracle assigns the file the extension .sql • To run the command(s) in the file from SQL*Plus, type @ followed by the name of the file

9. Creating and Using Scripts • In SQL*Plus Worksheet, a script file can be created by: • Typing the command(s) in the upper pane of the SQL*Plus Worksheet window • Selecting the Save Input As command • Specifying the name and location for the file • Oracle assigns the file the extension .sql

10. Creating and Using Scripts • In SQL*Plus Worksheet, to run the command(s) in a file: • Select the Open command • Select the file • Click the Open button • Click the Execute button

11. Running the Query for the Report • The data for a report can come from either a table or a view • Using a view is preferable to using a table, particularly if the report involves data from more than one table

12. Running the Query for the Report • Problem: • Create a script file named SLSR_REPORT.sql that defines a view named SLSR_REPORT with five columns for the report • Name first column SLSR: concatenation of the sales rep number, first name, and last name for each sales rep • Insert a hyphen between sales rep number and name, separate first and last names with a single space, and trim the values

13. Running the Query for the Report • Problem (Continued): • Name second column CUST: concatenation of the customer number and the customer name • Insert a hyphen between the customer number and name and trim the name • Name the third column BAL: contains the balance

14. Running the Query for the Report • Problem (Continued): • Name the fourth column CRED: credit limit • Name the fifth column AVAIL: available credit (CREDIT_LIMIT - BALANCE) for each customer • Run the script file to create the view

15. Running the Query for the Report

16. Creating the Data for the Report • To produce a report, a SELECT command must be run to create the data to be used in the report • Problem: • List all data in the SLSR_REPORT view • Order rows by the SLSR and CUST columns

17. Creating the Data for the Report • Solution: SELECT *FROM SLSR_REPORTORDER BY SLSR, CUST;

18. Changing Column Headings • To change a column heading: • Type the COLUMN command followed by the name of the column heading to change • Use the HEADING clause to assign a new heading • To display the heading on two lines, separate the two portions of the heading with a single vertical line (|)

19. Changing Column Headings • Problem: • Change the column headings in the report so they are more descriptive of the columns’ contents

20. Changing Column Headings • Solution: CLEAR COLUMNSCOLUMN SLSR HEADING 'Sales Rep|Number/Name‘COLUMN CUST HEADING 'Customer|Number/Name‘COLUMN BAL HEADING 'Current|Balance‘COLUMN CRED HEADING 'Credit|Limit‘COLUMN AVAIL HEADING 'Available|Credit‘/

21. Changing Column Headings • CLEAR COLUMNS: clears any previous column changes made to column headings or formats in the current work session • The COLUMN commands change the column headings • The slash (/ ) on the last line reruns the last query and displays the data with the new column headings

22. Changing Column Formats in a Report • The COLUMN command is used to: • Change column headings • Change the width of a column • Change the way entries appear in a column

23. Changing Column Formats in a Report • Problem: • Change the format of the columns to allow the SLSR and CUST columns to display 20 and 30 characters, respectively • Display the data in the other columns with dollar signs and two decimal places

24. Changing Column Formats in a Report • Solution: CLEAR COLUMNSCOLUMN SLSR HEADING 'Sales Rep|Number/Name' FORMAT A20COLUMN CUST HEADING 'Customer|Number/Name' FORMAT A30COLUMN BAL HEADING 'Current|Balance' FORMAT $99,990.99COLUMN CRED HEADING 'Credit|Limit' FORMAT $99,990.99COLUMN AVAIL HEADING 'Available|Credit' FORMAT $99,990.99/

25. Adding a Title to a Report • Problem: • Add a title that extends over two lines to the report • The first line is “Customer Financial Report” • The second line is “Organized by Sales Rep”

26. Adding a Title to a Report • Solution: SET LINESIZE 90SET PAGESIZE 50TTITLE 'Customer Financial Report|Organized by Sales Rep‘/

27. Adding a Title to a Report • TTITLE: adds a title to the top of the report • BTITLE: adds a title at the bottom of the report • Enclose the title in single quotation marks in the TTITLE and BTITLE commands • To display the title on two lines, separate the lines with a vertical line

28. Adding a Title to a Report • Line size is the maximum number of characters each line can contain • SET LINESIZE: adjusts line size • Page size: maximum number of lines per page • SET PAGESIZE: sets page size

29. Grouping Data in a Report • BREAK: identifies a column or collection of columns on which to group data • Problem: • Group rows in the report by SLSR column • Remove message at the end of the report indicating number of rows selected

30. Grouping Data in a Report • Solution: BREAK ON REPORT ON SLSR SKIP 1SET FEEDBACK OFF/

31. Grouping Data in a Report • The 1 in the SKIP clause at the end of the BREAK command inserts one blank line between groups • SET FEEDBACK OFF: turns off the message indicating the number of rows selected by the query

32. Including Total and Subtotals in a Report • Subtotal: A total that appears after each group • To calculate a subtotal, use BREAK to group the rows • Use COMPUTE to indicate the computation for the subtotal

33. Including Total and Subtotals in a Report • The COMPUTE command uses statistical functions

34. Including Total and Subtotals in a Report • Problem: • Include totals and subtotals in the report for the BAL and AVAIL columns

35. Including Total and Subtotals in a Report • Solution: COMPUTE SUM OF BAL ON SLSRCOMPUTE SUM OF AVAIL ON SLSRCOMPUTE SUM OF BAL ON REPORTCOMPUTE SUM OF AVAIL ON REPORT/

36. Including Total and Subtotals in a Report • In the COMPUTE command: • The OF clause includes the desired computations and the column names on which the computations are to occur • The ON clause indicates the point at which the computation is to occur

37. Sending the Report to a File • The exact manner in which a report is printed depends on the DBMS • To print a report using Oracle: • The output of the query is sent to a file by using the SPOOL command • The contents of the file are printed

38. Sending the Report to a File • Problem: • Send the report created in the previous examples to a file named SLSR_REPORT_OUTPUT.SQL • Solution: SPOOL SLSR_REPORT_OUTPUT.SQL/SPOOL OFF

39. Sending the Report to a File • SPOOL SLSR_REPORT_OUTPUT.SQL sends the output of subsequent commands to a file named SLSR_REPORT_OUTPUT.SQL • The SPOOL OFF command turns off spooling and stops any further output from being sent to the SLSR_REPORT_OUTPUT.SQL file

40. Completing the Script to Produce the Report • CLEAR COLUMNS: clears any previous column definitions • CLEAR COMPUTE: clears any previously specified computations • CLEAR BREAK: clears any previous breaks • TTITLE OFF: turns off any previously specified title at the top of the report

41. Summary • To concatenate values in character columns, separate the column names with two vertical lines • You can create script files to create views and format reports • The data for a report can come from a table or a view • Use COLUMN to change a column heading • Use the HEADING clause to assign a new heading name

42. Summary • Use COLUMN with a FORMAT clause to change the format of column values • Use the TTITLE or BTITLE command to add a title at the top or bottom of a report • Use BREAK and COMPUTE and an appropriate statistical function to calculate data in a report • Use SPOOL to send a report to a file for printing or editing

43. SQL Project Seven Completed Good Luck H. Zamanzadeh