1 / 43

Chapter 7 Reports

Chapter 7 Reports. 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. Objectives. Add a title to a report Group data in a report Include totals and subtotals in a report

wayde
Download Presentation

Chapter 7 Reports

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related