|
“The direction in
which education starts a man will determine his future life.”
Plato (427 BC - 347 BC), The Republic |
Read
first then play the video:
RPT007(VIDEO)-(Hands-On
06) How to use Group Above
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.
F 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).
|
"We succeed only
as we identify in life, or in war, or in anything else, a single
overriding objective, and make all other considerations bend to
that one objective." Dwight D. Eisenhower (1890 - 1969),
speech, April 2, 1957 |
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.
|