Transcript chap07

CHAPTER 7:
Creating Database Reports
Guide to Oracle10G
7
1
Reports
Snapshot summary of database data
Viewed on screen
Printed on paper
Printed to a file and distributed
electronically
7
2
Reports
A report can:
Display SQL query data
Display summary columns
Display calculated values based on
formulas applied to database data
Be formatted to look like an invoice or
other business document
7
3
Type of Reports
Tabular (table-format)
Form-like (looks like a form)
Mailing labels
Form letters
Matrix
7
Data value appears at the intersection of
a column and row heading
4
Types of Reports
 Master-detail: Group left
Figure 7-2
7
5
Types of Reports
 Master-detail: Group above
Figure 7-3
7
6
Creating a Report Using the
Report Wizard
7
1. Enter the SQL query to retrieve the
report data
2. Choose the display fields
3. Specify the report master-detail
groups
4. Specify the field prompts
5. Select a predefined template
7
Predefined Templates
 Specifies report appearance characteristics





7
Font sizes, types and colors
Graphic images
Background shading
Page numbering
Date report is created
8
Report file types
 Reports: .rdf
 Templates: .tdf
7
9
Using Format Masks in
Reports
 Format masks can be applied to:
 NUMBER fields
 DATE fields
 Format masks cannot be applied to text
fields
7
10
Report Builder Windows
 Live Previewer: Shows how report will
look when displayed or printed
7
11
Report Builder Windows
 Layout Model: Shows reports components
symbolically
7
12
Report Builder Windows
 Data Model: Shows report data
components
Query
Record
groups
7
13
Data Model Components
 Record group: Set of records with the
same column headings
 Master-detail reports have multiple record
groups
 Individual fields are
Column
called columns
7
 Default record group
name: G_<first column name>
14
Report Sections
Clearwater Traders
Header: optional title page
Sales Report
Main Section:
report data
and computations
7
*** End of Report ***
Trailer:
optional final
page
15
Report Sections
 Main Section has margins for
values that are displayed on
each report page
 Titles
 Dates
 Page numbers
Margins
7
Page 1 of 5
16
Navigating in the Report
Sections
 Section navigation buttons
Header
Main
Section
7
Main Section
Margins
Trailer
17
Running a Report from a Form
 Reports are often run from Form Builder
applications in integrated database systems
 Steps for running a report from a form:
7
1. Create a bind parameter(s) in the report to accept an
input parameter(s) from the form
2. Create a parameter list in the form to pass the input
parameter(s) from the form to the report
3. Run the report from the form using the
RUN_PRODUCT procedure
18
Running a Report from a Form
 Creating a bind parameter
 Substitute one or more search conditions in the
report SQL query with a parameter name prefaced
with a colon (:)
Figure 9-95
bind parameters
7
19
Running a Report from a Form
 Creating a parameter list
1. Declare the parameter list variable in the DECLARE
section
2. Create the list
declaring
the list
bind parameters
7
creating
the list
20
Running a Report from a Form


Parameter lists can only pass character or record group data
To add parameters to the parameter list using the
ADD_PARAMETER procedure:
ADD_PARAMETER(<list>, <key>, <paramtype>, <value>);



7

List: ID of the parameter list
Key: Name of the parameter in the report
Paramtype: Values can be TEXT_PARAMETER (for character
values) or DATA_PARAMETER (for record groups)
Value: Data value to be passed in parameter list
21
Running a Report from a Form
 Example of the ADD_PARAMETER procedure:
List
Key
Paramtype
Value
Figure 9-96
7
22
Running a Report from a Form

Running the report using the RUN_PRODUCT procedure
RUN_PRODUCT(<product>, <document>,
<communication mode>, <execution mode>,
<location>, <parameter list ID>, <display>);

Product: Application to run



Document: Complete path and filename of the report .rdf file
Communication mode

7
Value can be REPORTS or GRAPHICS

SYNCHRONOUS: Control returns to the form only after report is
closed
ASYNCHRONOUS: User can toggle between report and form
23
Running a Report from a Form

Running the report using the RUN_PRODUCT procedure
RUN_PRODUCT(<product>, <document>,
<communication mode>, <execution mode>,
<location>, <parameter list ID>, <display>);

Execution mode



Location


7


RUNTIME: Called product's runtime environment is started
BATCH: Called product is displayed directly on the form
FILESYSTEM: Report file stored in the filesystem
DATABASE: Report file stored in the database
Parameter list ID: ID of the parameter list to be passed
Display: Only used when running a GRAPHICS product

Value is always NULL when running a report
24
Running a Report from a Form
 Example of the RUN_PRODUCT procedure:
Execution
Mode
7
Product
Document
Location
Parameter
List ID
Communication
Mode
Display
Figure 9-99
25