Form Letter Report

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.

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.

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)
& &
&
&
&
Dear &<>,
Please ….
&
&
&
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)
& &
&
&
&
Dear &<>,
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 & after the split that amount will be &. Please contact us if this amount does not agree with your record.
Your stock’s market value as of today is: &
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).

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.