|
"For centuries,
theologians have been explaining the unknowable in terms of
the-not-worth-knowing." - Henry Louis Mencken (1880-1956) |
Read
first then play the video:
RPT006(VIDEO)-(Hands-On
05) How to write a Form Letter
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.
F 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 the
iself folder (REPT05).
|
“Education's
purpose is to replace an empty mind with an open one.” Malcolm
Forbes (1919 - 1990), in Forbes Magazine |
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 repot.
|