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.
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
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.
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.
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
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.
No comments:
Post a Comment