|
“We make a living
by what we get, we make a life by what we give.” Sir Winston
Churchill (1874 - 1965) |
Read
first then play the video:
RPT011(VIDEO)-(Hands-On
09) How to use Parameters
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.
F 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.
|
“There is no remedy
for love but to love more.” Henry David Thoreau (1817 - 1862),
Journal, July 25, 1839 |
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.
|