|
Form Letter Report
Introduction
The Form
letter report will complement the
mailing label report. You can pull several
database columns plus your mailing message
to produce form mailings.
Hand-on
Your client
needs to write a letter to all its customers
who own Oracle shares for good news of split
of their shares. You are asked to write a
�Form Letter� to send to each customer who
own Oracle (orcl) shares. You are supposed
to notify them of a stock split and tell
them the number of shares the broker hold
for them, their size of the split and the
post-split amount and their current stock
market values.
See Figure 5.
Your tasks
are:
1- Create a
�Form Letter� report.
2- Extract
customer�s information from their database.
3- Generate
the report only for Oracle�s share holders.
4- Apply
template to the user report.
5- Run the
report.
6- Test the
repot.
You will learn how to: use the report style
�form letter,� and the �query builder,� set
table relationships, define column from the
�query builder,� use sub-query, use of data
model, layout model, use of boilerplate,
write the letter and pass the column to the
letter.
Figure 5
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.�
Choose a
report style
In the Report
Wizard, choose the Form Letter style and
click Next.
Form Letter
The Form
letter report will complement the
mailing label report. You can pull several
database columns plus your mailing message
to produce form mailings.
SQL Query
Statement
In the SQL
Query Statement, write a SQL statement to
query customers� information (Name and
address), and their portfolio information
(Number of shares, double_shares_owned, and
current stock market value).
(SQL Query
Statement)
SELECT ALL
first_name, last_name,
address_line_1, address_line_2,
address_line_3,
Shares_owned,
shares_owned * 2 as double_shares_owned,
(shares_owned
* 2) * current_price as stock_market_value
FROM
customers, portfolio, stocks
WHERE (( id =
customer_id)
and (
stock_symbol = symbol))
and
trade_date = ( SELECT
max(trade_date)
FROM
stocks
Then click
Next.
Create a Form
Letter
Create your
form letter by selecting fields and by
typing directly into the Form Letter text.
(Form Letter
Text)
&<FIRST_NAME>
&<LAST_NAME>
&<ADDRESS_LINE_1>
&<ADDRESS_LINE_2>
&<ADDRESS_LINE_3>
Dear &<
FIRST_NAME>,
Please �.
&<SHARES_OWNED>
&<DOUBLE_SHARES_OWNED>
&<STOCK_MARKET_VALUE>
We selected
all the columns that will be used in the
letter.
In the Report
Wizard, select the template of your choice
and click on �FINISH.� In this hands-on, use
the default template.
Data Model
In the Live
Pre-viewer, click on the �Data Model� icon.
Right click on the �Q_1� and open its
property palette and change its name to
Q_CUSTOMER.
Report Editor
In the Data
Model, click on the Report Editor icon to
open the �Report Editor.�
Make the
Object Navigator and the Report Editor side
by side.
Resizing a
object
In the �Object
Navigator,� expand "Layout Model," �Main
Section,� �Body,� and select the repeating
group (R_G_FIRST_NAME).
The frame item
can be selected either by using the �Object
Navigator� or the �Layout Model� in the
report editor.
Drag the frame
to a size of letter page.
Click on the
text boilerplate in the Body item. Notice
the handlers; drag them to the "repeating
group" frame.
Write a letter
In the Report
Editor, write the company letter.
(Letter)
&<FIRST_NAME>
&<LAST_NAME>
&<ADDRESS_LINE_1>
&<ADDRESS_LINE_2>
&<ADDRESS_LINE_3>
Dear &<
FIRST_NAME>,
Please be
advised that the Oracle Company (ORCL) has
announced a stock split for shareholders of
record.
The split will
be 2 for 1.
Our records
indicate that you now hold &<SHARES_OWNED>
after the split that amount will be &<DOUBLE_SHARES_OWNED>.
Please contact us if this amount does not
agree with your record.
Your stock�s
market value as of today is: &<STOCK_MARKET_VALUE>
Thank you,
Customer
Service.
Run the
report.
Navigate
through the report.
You can also
change the text on the �live pre-viewer.�
Notice that
the number of shares got double.
Put a dollar
sign ($) next to the stock market values.
Navigate
through all the Oracle's stock's holders.
After testing
the output report, highlight the report and
save it as report number 5 in your folder
(REPT05).
Questions:
Q: What is a
Form Letter report?
Q: When do you
use the query builder tool?
Q: How do you
set a table relationship in the Layout
Module?
Q: How do you
define a column from the Query Builder?
Q: How do you
use the Data Model tool?
Q: How do you
use the Layout Model tool?
Q: What is the
boilerplate in the Layout Editor?
Q: Your client
needs to write a letter to all its customers
who own Oracle shares for good news of split
of their shares. You are asked to write a
�Form Letter� to send to each customer who
own Oracle (orcl) shares. You are supposed
to notify them of a stock split and tell
them the number of shares the broker hold
for them, their size of the split and the
post-split amount and their current stock
market values.
See Figure 5.
Your tasks
are:
1- Create a
�Form Letter� report.
2- Extract
customer�s information from their database.
3- Generate
the report only for Oracle�s share holders.
4- Apply
template to the user report.
5- Run the
report.
6- Test the
report. |
