How to use Parameters in oracle reports 6i

FIGURE-9

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.