Transcript Week 6 March 1 • Oracle Reports: Master-Detail Report R. Ching, Ph.D. • MIS Area • California State University, Sacramento.

1
Week 6
March 1
• Oracle Reports: Master-Detail Report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Report Styles
A tabular report is the most basic
type of report. Each column
corresponds to a column selected
from the database.
A form-like report displays
one record per page,
inserting field values to the
right of field labels.
A mailing label report prints
mailing labels in multiple
columns on each page.
You can print the labels
across the page and then
down, or down and then
across.
A form letter report contains database
values embedded in boilerplate text.
(Boilerplate text is any text that you
enter or import into a Report Editor.)
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
2
Report Styles
3
A group left report divides the rows of a table into "sets," based on a
common value in one of the columns. Use this type of report to restrict a
column from repeating the same value several times while values of
related columns change.
A group above report contains two or more
groups of data. For every value of the
master group, the related values of the
detail group(s) are fetched from the
database.
A matrix (cross-tabulation) report
contains one row of labels, one
column of labels, and information in
a grid format that is related to the
row and column labels. A
distinguishing feature of matrix
reports is that the number of
columns is not known until the data
is R.fetched
from the database.
Ching, Ph.D. • MIS Area • California State University, Sacramento
A matrix with group report is a group
above report with a separate matrix
for each value of the master group.
Types of Reports
•
•
•
•
Tabular
Master-detail
Cross-tabulation
Drill-down (and roll-up)
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
4
Tabular Report
Columns
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
5
Group (Master-Detail) Report
6
Master
Detail
Group totals
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Matrix Report
7
Row totals
Category 1
Category 2
Column totals
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Drill-Down Report
Highest Level Report
Drill-down
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
8
Drill-Down Report
Next Highest Level Report
Drill-down
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
9
Drill-Down Report
Lowest Level Report
Drill-down
Year

Quarter

Months
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
10
11
Oracle Reports
• Object Navigator
• Data Model
• Layout Model
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Basic Report Objects
• Data Model
• Layout Model
• Triggers
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
12
Data Model
13
SQL
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Data Model Objects
• Component objects:
– Queries
• SQL statements
– Links
• Establishes the relationship between the data of two
queries (e.g., master/detail)
– Groups
• Similar to control groups in control break reports
– Columns
• Data on the report (vertical arrangement)
• Summary, formulas or placeholders
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
14
Report Layout Model
15
Header
Frame
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Field
Body
Layout Model Objects
• Frames - contains groups of data and objects (similar to a
block)
• Repeating frames - represent one record in a group
• Fields -placeholders for data
• Boilerplate - labels, lines or graphics
• Anchors - used for relative positioning of child to parent
• Buttons
• OLE2 objects
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
16
Basic Tabular Report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
17
Reports
18
• Purpose - Format a large amount of data in a reasonable fashion in a
very timely manner
• Components
Page
Header
1
Body
1
Data Model
3
Margin
2
Appears only at the
beginning of the report
Logical pages reside
within a component
4
Trailer
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
1
Appears only at the end
of the report
Creating A Report
•
•
•
•
•
Build the data model
Assign the fields to the report
Arrange the report in the layout model editor
Test the report
Use the report layout editor to format the report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
19
Report Styles
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
20
Data Model
Build the query in SQL Plus and
paste it into the data model
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
21
Select the Columns
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
22
Select Columns to Aggregate
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
23
Specify Column Widths
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
24
Select Report Background
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
25
Basic Report Layout
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
26
Working with Frames
27
Page Heading
Page
Field
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Repeating Group
Master-Detail Report
Control
heading
Control
group
Control footing
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
28
Page
heading
Data Model
29
Two Queries
Master group
Host variable
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Data
Model
Data link
Detail group
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
30
Data Model
31
Queries
Master group
Detail group
Data link
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Layout Model Editor
Repeating
Frame
Frames cannot overlap!
Body
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
32
Test the Report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
33
Enter a Value for the Host Variable
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
34
Runtime Previewer
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
35
Run
Live Previewer
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
36
37
Creating a Master-Detail Report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Steps
• Create the data model
– Master query
– Detail query
– Link the queries
• Create the report layout
– Use the Report Wizard
• Test
• Format the report layout
– Test
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
38
Basic Report Objects
Data Model
Layout Model
Triggers
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
39
Calling the Report
40
Host (bind)
variable
This will appear automatically
when a host (bind) variable
appears in the query (data
model)
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Parameter
Form
Anatomy of a Master-Detail Report
Group header (master)
Detail
lines
Group footer
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
41
Master-Detail Report
42
One-to-many
relationship
Master
Detail
Manufacturers
Have
Products
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
43
Create the Data Model
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Master-Detail Report Data Model
44
First Query
Host (bind) variable
Create and test the query in SQL Plus without the host variable
then paste it into the data model.
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Master-Detail Report Data Model
Data Link
Second Query
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
45
46
Create the Report Layout
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Create the Initial Report Layout
47
Designate fields and values
Master-detail
report
Wizard used in a different report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Data Model
48
Queries
Master group
Detail group
Data link
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Layout Model Editor
Repeating
Frame
Frames cannot overlap!
Body
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
49
Test the Report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
50
Enter a Value for the Host Variable
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
51
Runtime Previewer
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
52
Run
Live Previewer
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
53