|
Group above report
Introduction
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.
Hands-on
Your users
want to send to their customers their
portfolio information at the end of each
month. Based on their requirements, you
should create a master-detail report that
shows the portfolio holdings of each
customer of the stock broker company,
including their current stock market value.
As a user
requirement, you should use format mask
NNN,NN0 for shares owned and $99,999.09 for
their stock market value.
You should
also print each customer stock�s information
on a separate page so company can mail them
to its customer.
See Figure 6.
Your tasks
are:
1- Write a
�Group above� report.
2- Print
detail share holders record (symbol, shares
owned, and its current market value.
3- Apply user
layout Format mask.
4- Calculate
the customer�s current stock market value.
5- Run the
report.
6- Test the
repot.
You will learn how to: use a master-detail
report, build a new report manually, create
multiple �SQL Query,� create data link, use
the formula column, use the summary column,
use the report �group above� style, use
object navigator, layout model, main
section, and use property palette
Figure 6
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.
Open the
Reports Builder.
In the Object
Navigator, highlight the Reports item, and
click on the "create" icon (Green �+�) to
create a new report.
Build a new
report Manually
In the New
Report window, choose the �Build a new
report manually� option, and click �OK.�
Create a SQL
box
In the �Data
Model,� click on the SQL icon on the
vertical toolbar. Drag the �+� sign in to
the Data Model and click any where that you
wish to have your object.
In the �SQL
Query Statement� box, write a SQL statement
to query all customers.
(SQL Query
Statement)
SELECT *
FROM customers
Then click
�OK.�
Create a
second SQL box
In the �Data
Model,� click again on the SQL icon on the
vertical toolbar. Drag the �+� sign in to
the Data Model and click any where that you
wish to have your object.
In the �SQL
Query Statement� box, write a SQL statement
to query all customers� portfolio.
(SQL Query
Statement)
SELECT *
FROM portfolio
Then click
�OK.�
Change SQL
boxes� property
Right click on
the �Q_1� SQL box, and open its property
palette. In its property palette, change its
name to Q_CUSTOMERS, and close the window.
Right click on
the �G_ID� Group box, and open its property
palette. In its property palette, change its
name to G_CUSTOMERS, and close the window.
Right click on
the �Q_2� SQL box, and open its property
palette. In its property palette, change its
name to Q_PORTFOLIO, and close the window.
Right click on
the �G_CUSTOMER_ID� Group box, and open its
property palette. In its property palette,
change its name to G_PORTFOLIO, and close
the window.
Create a
database relationship
In the Report
Editor, click on the �Data Link� icon on the
vertical toolbar. Notice that the cursor is
changed to a cross. Move the cross cursor to
the "ID" item and click and drag it to the
"customer_id" item of the portfolio group.
Now, the link was established.
Create a
Control Break
Drag the
"customer_id" item to the outside of the
group box. This will create a control break
on customer_id.
Create a
Formula Column
Click on the
"Formula Column" icon. Drag the cross to the
Group portfolio and click on it again. Right
click on it and open its property palette.
Change the name to CF_1 to CF_MARKET_VALUE.
In the
Property Palette, double click on the PL/SQL
Formula box.
PL/SQL block
for Formula Column
In the PL/SQL
window, write a PL/SQL block to calculate
the customer stock market value. Get the
current price for the current stock then
calculate the market value.
(PL/SQL)
FUNCTION
cf_market_valueFormula RETURN NUMBER
IS
v_current_price NUMBER:
BEGIN
SELECT
current_price INTO v_current_price
FROM stocks
WHERE
trade_date =
(SELECT
max(trade_date) FROM stocks)
AND
Symbol =
:stock_symbol;
RETURN
:shares_owned * v_current_price;
END;
Compile the
PL/SQL block
Compile it.
It should be
successfully compiled.
Close the
PL/SQL window.
Close the
property palette.
Create a
Summary Column
In the Data
Model window, select the Summary Column icon
and move the cross cursor to the "group
customer id" and click on the box. Right
click on it (CS_1), and open its property
palette. In the property palette window,
change name to CS_TOTAL_MARKET_VALUE, change
"source" to "CF_MARKET_VALUE," change the
"Reset At" to the "G_CUSTOMER_ID." Then
close the window.
Style, Group,
Fields, Labels, and Template tabs
In the Data
Model window, click on the Report Wizard
icon.
In the Style
tab on the Report Wizard window, type the
title �Customer Portfolio,� and checkmark
the "Group above" style. Then click Next.
In the Group
tab, move all the items to "displayed
groups" using the down arrow option. Click
on the �Next� push button.
In the Field
tab, move last_name, stock_symbol,
shares_owned, cf_market_value, and
cs_total_market_value to "Displayed fields."
Click Next.
In the Label
tab, change the Labels if needed. Click
�NEXT.�
In the
Template tab, select a template, and then
click �Finish.�
Change
appearance of the report
In the Live
Pre-viewer, change the column size and their
format mask. Align their labels. Highlight
the �Last Name� label and press the "delete"
function key. Align the customer last name.
Change its color.
Create a
Control Break on customer
Go to the
"Object Navigator." Expand the "Layout
Model" item. Expand the Body item. Right
click on the first repeating group and open
its Property Palette from the �Body� item.
In its
property palette, change "Maximum Records
Per Page" to 1, and then close the window.
Now, it is one
customer per page. Navigate through the
output report. Make an adjustment if needed.
Navigate through it again.
After testing
the report output, highlight the report and
save the changes as the report number 6
(REPT06).
Questions:
Q: Describe a
GROUP-ABOVE report in the report builder.
Q: How do you
use a Master-Detail report in the Data
Model?
Q: How do you
create multiple SQL queries in the Data
Model?
Q: How do you
create a data link in the Data Model?
Q: What is the
formula column and how do you create it?
Q: What is the
summary column and how do you create it?
Q: What is a
main section in the Object navigator?
Q: Your users
want to send to their customers their
portfolio information at the end of each
month. Based on their requirements, you
should create a master-detail report that
shows the portfolio holdings of each
customer of the stock broker company,
including their current stock market value.
As a user
requirement, you should use format mask
NNN,NN0 for shares owned and $99,999.09 for
their stock market value.
You should
also print each customer stock�s information
on a separate page so company can mail them
to its customer.
See Figure 6.
Your tasks
are:
1- Write a
�Group above� report.
2- Print
detail share holders record (symbol, shares
owned, and its current market value.
3- Apply user
layout Format mask.
4- Calculate
the customer�s current stock market value.
5- Run the
report.
6- Test the
repot. |
