iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Developers - REPORTS

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 |

 

Lesson 07

“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:

   RPT-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.

 

FYou 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.