Transcript Week 8 March 22 • Creating a Matrix and Drill-down/Roll-up Report R. Ching, Ph.D.

1
Week 8
March 22
• Creating a Matrix and Drill-down/Roll-up Report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Create the SQL
2
Tables
SQL
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Create the Data Model
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
3
Report Wizard
4

Select Matrix
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Report Wizard

R. Ching, Ph.D. • MIS Area • California State University, Sacramento
5
Report Wizard
6

Rows
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Report Wizard
7

Columns
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Report Wizard

R. Ching, Ph.D. • MIS Area • California State University, Sacramento
8
Report Wizard

R. Ching, Ph.D. • MIS Area • California State University, Sacramento
9
Report Wizard
10

Change to small
column width
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Report Wizard
11

R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Initial Report Layout
Column totals
Redundant
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
12
Report Layout
13
Eliminate
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Layout Model
Remove
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
14
Layout Model
15
Push button
Separate frames
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Push Button Property Palette
Leave at least a space
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
16
Live Previewer
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
17
Programming the Drill-Down/Roll-Up Report
Master Report
(Annual)
18
Data Model 1
Parameter-list1
Drill-down Report
(Quarters)
Data Model 2
Parameter-list2
Drill-down Report
(Months)
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
Data Model 2
Parameters
names
cannot be
the same!
19
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
20
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
p_class_description
From previous report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
p_year
21
22
Transfervalues
valuesfrom
from
Transfer
parametersand
andcolumns
columns
parameters
localvariables
variables
to tolocal
Parameter
names should
be different
from those
defined for
this report
Warning! Following this code verbatim will be a detriment to your grade
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
p_year
p_class_description
From previous report
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
p_quarter3
23
Creating a Lexical Reference Variable
• Create a lexical reference
variable under User
Parameters of the Data
Model (in the Object
Navigator)
 Select

Create
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
24
Create a User Parameter
 Select the new parameter, rightmouse click and select Property
Palette
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
25
Change the Properties
Change the name
Change the data type to character
Assign an initial value (optional)
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
26
Warning!
27
Add after creating the initial data and layout models
Enlargement
select manufacturer_name, to_char(sales_month_98,'fmmm') Sequence,
to_char(sales_month_98,'Mon') Month,
&p_revenue3, &p_volume3
Lexical reference variables
from sales_97_98 s, manufacturers m
where s.manufacturer_code = m.manufacturer_code
and m.manufacturer_code = upper(:p_manufacturer_code3)
and to_char(sales_month_98,'q') = :p_quarter3 Otherwise, ORDER BY ignored
and s.product_class_code = upper(:p_product_class_code3)
group by manufacturer_name, to_char(sales_month_98,'fmmm'), to_char(sales_month_98,'Mon')
order
by to_char(sales_month_98,'fmmm');
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
During Runtime...
Host variable
Lexical reference variables
(default values)
Host variable
R. Ching, Ph.D. • MIS Area • California State University, Sacramento
28