iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Developers - REPORTS

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 |

 

Lesson 10

“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:

   RPT-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.

 

FYou 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.