|
Parameters
Introduction
There are two
types of parameters: user parameters
and system parameters.
User
Parameters
�User
parameters� will be used to manage the
behavior and output of your reports.
System
Parameters
�System
parameters� will be used to manage the
report runtime behavior of your reports such
as defining your output destination.
Parameter Form
Builder
�Parameter
Form Builder� tool will be used to
design your runtime parameter form. The
Runtime Parameter Form appears whenever
you run your report.
Hands-on
The user wants
to execute this report just against a
specific company�s symbol. They want to use
company�s symbol as a parameter to pass to
the report. Also the report should calculate
the total and average of shares sold for the
company at the end of report.
You should
create a simple report to list the name of
customers including their shares owned.
See Figure 9.
Your tasks
are:
1- Create a
new portfolio report for each company.
2- Use
parameter to run the report for a specific
symbol.
3- Print the
list of their customers and their number of
shares.
4- Print the
total and average of shares sold for the
company.
5- Run the
report.
6- Test the
report.
You will learn how to: use the �group
above,� use the �query builder,� set table
relationship, create and use binding
variable, calculate total and average
shares, delete the object from layout.
Figure 9
Open the
Report Builder.
Create a New
Report
In the Object
Navigator, highlight the Reports item, and
click on the "create" icon (Green �+�) to
create a new report.
Use the Report
Wizard
In the New
Report window, choose the �Use the Report
Wizard� option, and click �OK.�
Select a style
In the Report
Wizard, type �Customer List� in the Title
box, choose the �Group Above� style and
click Next.
Group-Above
A
Group-Above report is a style you use to
demonstrate a master/detail relationship in
your database. You should define a master
group, and for each master record, the
report obtains the related values from the
detail groups.
SQL Query
Statement
In the SQL
Query Statement, write a SQL statement to
query customers� information (Name), and
their portfolio information (Number of
shares, and stock�s symbol) with an binding
variables to be used as parameter field.
(SQL Query
Statement)
SELECT ALL
first_name, last_name,
stock_symbol,
shares_owned
FROM
customers, portfolio
WHERE id =
customer_id and
stock_symbol =
:symbol
Use of a Colon
The colon next
to the symbol (:symbol) means: pass a symbol
as a parameter to the report.
Click OK. You
get This message:
Note: The
query �Q_1� has created the bind parameter(s)
�SYMBOL�.
Click "OK" to
confirm it.
Group fields
In the Report
Wizard, in the Group Field section, select
the "Stock_Symbol" item as a "Group Fields."
Display Fields
In the Display
Fields section, select all the items to
display in your report. You may add other
fields later.
Total and
Average �
Calculate the
total and average shares sold for a company
(Sum(SHARES_OWNED), Average(SHARES_OWNED)).
Label and
Template Sections
In the Label
section, modify the labels and widths for
your fields and totals as desired.
In the
Template section, use the default template
and click Next.
In the
congratulation section, click Finish to run
the report.
Parameter Form
Prompt
The parameter
form will be opened. In the Runtime
Parameter Form, enter the company�s symbol
such as ORCL, MSFT, etc. The click on the
Run icon to run the report.
Remove an
object or objects
Hold the
control key and click on the duplicate Total
and Average objects. Then press the "delete"
function key to remove them. Objects will be
removed.
Format Masking
and Layout adjustment
Do all the
items format mask and layout adjustment, if
needed! Navigate through the report pages.
After testing the report output, highlight
the �UNTITLED� report and save the changes
as the report number 9 (REPT09).
Run a report
and pass a parameter
In the Object
Navigator on the vertical toolbar, click on
the �Run� icon to run the report with
different company symbol. In the Runtime
Parameter Form, enter the company�s symbol
that does not exist. The click on the Run
icon to run the report.
Questions:
Q: How do you
create a binding variable?
Q: How do you
use a binding variable?
Q: How do you
remove an object from Layout Editor?
Q: The user
wants to execute this report just against a
specific company�s symbol. They want to use
company�s symbol as a parameter to pass to
the report. Also the report should calculate
the total and average of shares sold for the
company at the end of report.
You should
create a simple report to list the name of
customers including their shares owned.
See Figure 9.
Your tasks
are:
1- Create a
new portfolio report for each company.
2- Use
parameter to run the report for a specific
symbol.
3- Print the
list of their customers and their number of
shares.
4- Print the
total and average of shares sold for the
company.
5- Run the
report.
6- Test the
report. |
