Week 8 March 22

1
Week 8March 22

• Creating a Matrix and Drill-down/Roll-up Report

2
Create the SQL
Tables
SQL
3
Create the Data Model
4
Report Wizard
?
Select Matrix
5
Report Wizard
?
6
Report Wizard
?
Rows
7
Report Wizard
?
Columns
8
Report Wizard
?
9
Report Wizard
?
10
Report Wizard
?
Change to small column width
11
Report Wizard
?
12
Initial Report Layout
Column totals
Redundant
13
Report Layout
Eliminate
14
Layout Model
Remove
15
Layout Model
Push button
Separate frames
16
Push Button Property Palette
Leave at least a space
17
Live Previewer
18
Programming the Drill-Down/Roll-Up Report
Master Report (Annual)
Data Model 1
Parameter-list1

Parameters names cannot be the same!
Drill-down Report (Quarters)
Data Model 2
Parameter-list2
Drill-down Report (Months)
Data Model 2
19
(No Transcript)
20
(No Transcript)
21
p_class_description
p_year
From previous report
22
Transfer values from parameters and columns to
local variables
Transfer values from parameters and columns to
local variables
Parameter names should be different from those
defined for this report
Warning! Following this code verbatim will be a
detriment to your grade
23
p_year
p_class_description
p_quarter3
From previous report
24
Creating a Lexical Reference Variable

• Create a lexical reference variable under User
  Parameters of the Data Model (in the Object
  Navigator)

? Select
?
Create
25
Create a User Parameter
? Select the new parameter, right-mouse click and
select Property Palette
26
Change the Properties
Change the name
Change the data type to character
Assign an initial value (optional)
27
Warning!
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 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 and
s.product_class_code upper(p_product_class_code
3) group by manufacturer_name, to_char(sales_month
_98,'fmmm'), to_char(sales_month_98,'Mon') order
by to_char(sales_month_98,'fmmm')
Lexical reference variables
Otherwise, ORDER BY ignored
28
During Runtime...
Host variable
Lexical reference variables (default values)
Host variable