REPORTS Introduction

Introduction

In this case study we look at reports, which will show you how to enhance your skills in using Oracle Report Builder.

In these Hands-On, your client is a stock broker that keeps track of its customer stock transactions. You have been assigned to write them reports based on their reports layout requirements.



Before using the Report Builder tool

When you use the Report Builder tool, there are few modules that you may use them over and over. They are basics for your understanding how to use the tools.



Object Navigator

In the Report Builder tool, use the Object Navigator window, to view report components and navigate through the report objects. It gives you a hierarchical listing of all the modules open in your current session. You use this listing to navigate to, and work on, those objects. It gives you access to all database objects you own or have grant to, as well as a list of all the built-in functions and procedures you can use in Oracle Form Builder. Clicking on the plus sign next to an object in the Object Navigator will expand the object and clicking on the minus sign will collapse the object. Within the Object Navigator window, you have:

· Reports

· Templates

· External SQL Queries

· PL/SQL Libraries

· Debug Actions

· Stack

· Built-in Packages

· Database Objects.



Reports Object

In the Reports object, you have other important objects that are very handy to create, delete, and modify a report such as Live Pre-viewer, Data Model, Layout Model, Parameter Form, Report Triggers, Program Units, and Attached Libraries.

· Live Pre-viewer

· Data Model

· Layout Model

· Parameter Form

· Report Triggers

· Program Units

· Attached Libraries

Live Pre-viewer

You use the Live Pre-viewer object to view what the report will look like when it is executed.



Data Model

You use the “Data Model” to display the data content and layout appearance of the report. It’s very important when you use this object that you understand SQL statement and relationship between tables such foreign key and primary key.



Layout Model

You use the Layout Model object to show you how your report will appear. When you run your report, Report Builder fills in the objects in the Layout Model with actual data. The Layout Model will show you several types of layout objects and the relationships that exist between them. The Layout Model uses symbols and graphical representations to show these relationships. You can add, modify or delete part of your layout from here.



Parameter Form

You use Parameter Form to define, remove, and modify your report parameters. You will use the “Parameter Form Builder” tool to design your runtime parameter form. There are two types of parameters: user parameters and system parameters.



User Parameters

“User parameters” will be used to manage the behavior and output of your reports.



System Parameters

“System parameters” will be used to manage the report runtime behavior of your reports such as defining your output destination. Remember that when you run your report, the Runtime Parameter Form will be appeared.



Report Triggers

You use Report Triggers to define PL/SQL to fire at various points during the execution of your report. You will have:

· BEFORE REPORT,

· AFTER REPORT,

· BETWEEN PAGES,

· BEFORE PARAMETER FORM, and

· AFTER PARAMETER FORM.

Program units and Attached Libraries

You can use Program Units and Attached Libraries as the same way we use them in the Forms Builder tool.



Property Palette

In the course of our hands-on, you will see that we use Property Palette over and over. In fact you can’t do much without Property Palette. It is very important to understand it. It contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. You use the Property Palette to modify object properties. To open the Property Palette of an object, go to the object and right click on the mouse then select Property Palette.





Questions:

Q: What is an Object Navigator in the Report Builder tool?

Q: What does the Object Navigator contain?

Q: What is a Report Object in the Object Navigator?

Q: What is a Pre-viewer in the Report Builder?

Q: What is a Data Model in the Report Builder?

Q: What is a Layout Model in the Report Builder?

Q: What is a Parameter Form in the Report Builder?

Q: What are the types of parameters?

Q: What is a user parameter?

Q: What is a system parameter?

Q: What are the report triggers?

Q: Describe program units and attached libraries in the report builder?

Q: Describe property palette in the report builder?Q: What is the property sheet for an object

How to write a Tabular report

(FIGURE-1)

Tabular

Introduction
Tabular report shows data in a table format. It is similar in concept to the idea of an Oracle table. Oracle, by default, returns output from your select statement in tabular format.

Hands-on
In this Hands-On, your client is a stock broker that keeps track of its customer stock transactions. You have been assigned to write the reports based on their reports layout requirements.
Your client wants you to create a simple listing report to show list of the stock trades by using stocks table for their brokerage company
The user layout format mask requirements are:
For date MM-DD-RR (Year 2000 complaint).
For dollars $99,999.90.
For quantity NNN.N0.
See Figure 1.

Your tasks are:
1- Write a tabular report.
2- Apply user layout Format mask.
3- Run the report.
4- Test the repot.

You will learn how to: use report wizard, object navigator, report builder, “date model”, property palette, work on query and group box, see report style, use tabular style, navigating through report’s record, change the format mask for dollar, numeric and date items.

Figure 1


Open Report Builder tool
Open the "Report Builder" tool.

Connect to database
In the Object Navigator, highlight "Database Objects,” choose "File," then select the "Connect" option.
In the ‘Connect’ window, login as “iself” password schooling, then click “CONNECT.”

Save a report
In the Object Navigator, highlight the "untitled" report, choose “File,” and select the “Save as” option.
In the ‘Save as’ window, make sure to save the report in the ISELF folder and name it "rpt01_stock_history,” report number 1 stock history.

Data Model
In the Object Navigator, double click on the "Data Model" icon.

Create SQL box
In the Data Model window, click on the "SQL Query" icon. Then drag the plus sign cursor and click it anywhere in the “Data Model” screen where you wish your object to be.

In the ‘SQL Query Statement’ window, write a query to read all the stocks record sorted by their symbol.
(SQL Query Statement)
SELECT * FROM stocks
ORDER BY symbol
Click “OK.”

Change SQL box’s name
In the Data Model window, in the “SQL” box, right click on the ‘Q_1’ and open its property palette.
In its property palette, change the name to Q_STOCKS. Then close the window.

Change GROUP box’s name
In the Data Model, right click on the group box (G_SYMBOL) and open its property palette.
In the Group property palette, change the name to ‘G_STOCKS,’ and close the window.

Open Report Wizard
In the Data Model, click on the ‘Report Wizard’ icon on the horizontal tool bar.
In the Style tab, on the Report Wizard window, type ‘Stock History’ in the Title box and choose the report style as ‘Tabular.’
Notice that when you change the report style a layout of that report will be displayed on the screen.
Choose a different style to display its layout of its report style.

Data, Fields, Totals, Labels and Template tabs
Click “NEXT” to go to the Data tab. In the ‘SQL Query Statement’ verify your query.
Click “NEXT” to navigate to the Fields tab, select the fields that you would like to be display in your report. Select all the columns to be display.
Click “NEXT” to navigate to Totals tab, select the fields for which you would like to calculate totals. We have none in this hands-on exercise.

Click “NEXT” to open the Labels tab, modify the labels and widths for your fields and totals as desired.
Click “NEXT” again to go to the Template tab, and choose a template for your report. Your report will inherit the template’s colors, fonts, line widths, and structure.
Use the default template and click “finish.”

Running a report
Now, you should have your output report on the screen.

Resize an object
Maximize the output report and format the report layout. To resize an object , select it and drag its handler to the preferred size.

Move an object
To move an object, select and drag it while the cursor is on the object.

This is a simple report.
Navigate through the output
To navigate through the output report in the Report Editor - Live Pre-viewer, click on the "next page" or "previous page" icon on the horizontal toolbar.
Do the same with the "first page" or "last page" icon.

Use the “zoom in” and “zoom out” icon to preview the report.
Know report’s functions
To know each icon functionalities, drag your cursor on it and a tooltip will display its function.

Change Format Mask
To change the "format mask" of a column, the column should be selected. Then go to the toolbar and click on the “$” icon, "add decimal place," and the “right justify” format to the all currency columns (Todays Low, Todays High, and current price)

Select the “traded today” column, and click on the ‘,0’ icon (apply commas), and make it right justify.
Also, you can change any attributes of field by opening its property palette. To open an object’s property palette, right click on it and select the Property Palette option.
Right click on the "trade date" column and open its "property palette."
Change the date "Format Mask" property and make it “year 2000 complaint (MM-DD-RR).”

Navigate through the report pages.
Save a report
Save the changes (rpt01_stock_history).



Questions
Q: How can you use the "Report Wizard" tool in the "Report Builder" tool?
Q: How can you use the "Object Navigator" window?
Q: How can you write a report?
Q: How can you save a report?
Q: How can you use the "Data Model" tool?
Q: What is the "Property Palette" sheet?
Q: What is the "Query" box in the "Data Model" window?
Q: What is the "Group" box in the "Data Model" window?
Q: How can you change a report style?
Q: Use a tabular style for a report.
Q: How can you navigate through your report?
Q: How can you change a format mask for an item in the "Report Builder" tool?
Q: What is a Tabular report?
Q: Your client is a stock broker that keeps track of its customer stock transactions. You have been assigned to write the reports based on their reports layout requirements.
Your client wants you to create a simple listing report to show list of the stock trades by using stocks table for their brokerage company
The user layout format mask requirements are:
For date MM-DD-RR (Year 2000 complaint).
For dollars $99,999.90.
For quantity NNN.N0.
See Figure 1.

Your tasks are:
1- Write a tabular report.
2- Apply user layout Format mask.
3- Run the report.4- Test the repot.

How to write a Mailing Label report

Mailing Label

Introduction
A Mailing Label report is a list of addresses designed to be generated on mailing labels. The addresses can be printed across the page, and then down or vice versa.

Hands-on
Your client has to mail their customers to inform them of the status of their current market value position. They ask you to create a report that outputs mailing labels. First they ask you to generate mailing labels with three columns and then change it to 1.5” X 3.5” label format.
See Figure 3.

Your tasks are:
1- Create a report to generate three column labels.
2- Extract customer’s name and address from their database.
3- Change the report to generate two column labels.
4- Apply template to the user report.
5- Run the report.
6- Test the repot.

You will learn how to: use report style “mailing label,” use the “query builder,” generate three columns label, change the font and size, change it to two columns label, use handlers on the layout model.


Create a new report
In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report.

Report Wizard
In the New Report window, choose the ‘Use the Report Wizard’ option, and click ‘OK.’
In the Report Wizard window, choose the Mailing Label Style, and click the ‘Next’ push button.

Mailing Label
A Mailing Label report is a list of addresses designed to be generated on mailing labels. The addresses can be printed across the page, and then down or vice versa.
In the ‘SQL Query Statement’ box, write a SQL statement to query all customers name from customers table.
(SQL Query Statement)
SELECT ALL first_name, last_name, address_line_1, address_line_2, address_line_3
FROM customers

Then click Next.
You can create your mailing label by selecting fields and by typing directly into the Mailing Label text. Select the columns into "mailing label text."
Use "New line", "space", "Dash", "comma", and "period" if you need them in the "mailing label text." Notice the following selection.
(Mailing Label text)
&, &
&
&
&
Click “NEXT.”

Select the draft template; then click “NEXT,” and “Finish.”

This is an example of a three column label.
In the Live Pre-viewer, you can select the output object and change its font type and size. To change the font, go to toolbar that has B, I, U, type of font, and font size. It is at the top of the report.

Change the mailing label
Change the three column label to the two column label.
In the Object Navigator, click on the “Layout Model” icon.

Make the “Object Navigator” window and the “Layout Model” window side by side.
Find a repeating group
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 1.5 by 3.5 inches.
Click on the text boilerplate. Notice the handlers; drag them to the "repeating group" frame.

Select the text and change the font type and size.
Run a report
Click on the Run icon to run the report.
This is an example of a two column label.

You can select the customer name and change their font.

Select the “untitled” report and save it as report number 3 (REPT03) in the "iself" folder



Questions:
Q: Describe a Mailing Label report.
Q: How do you use the query builder?
Q: How do you generate columns label in the query builder?
Q: How do you change a report’s font and size?
Q: What are the handlers in the layout model?
Q: Your client has to mail their customers to inform them of the status of their current market value position. They ask you to create a report that outputs mailing labels. First they ask you to generate mailing labels with three columns and then change it to 1.5” X 3.5” label format.
See Figure 3.

Your tasks are:
1- Create a report to generate three column labels.
2- Extract customer’s name and address from their database.
3- Change the report to generate two column labels.
4- Apply template to the user report.
5- Run the report.
6- Test the repot.

Matrix Report

Figure 4

Matrix Report

Introduction
A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results.

Hands-on
You client needs to get the summary report of their customers shares owned. They like to have the output format be the same as spreadsheet format. You are assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock.
This report should show the number of shares of stock’s holders by each customer in each of the stocks. Make sure the cell format mask is NNN,NN0. You should use the customer and portfolio table, and put grid around each number of shares for easy reading. You should also get the totals for each column and row.
They want you to change the cell color to red if its value is greater than 2000 and do the same for rows and columns sub-total.
See Figure 4.

Your tasks are:
1- Create a matrix report.
2- Put grid around each cell.
3- Use user layout format mask for cells and sub-totals as NNN,NN0.
4- Calculate the subtotal for rows and columns.
5- Highlight the cells with any color (ex: red) if their values are significant.
6- Highlight the sub-total cells with any color (ex: pink) if their values are significant.
7- Apply template to the report.
8- Run the report.
9- Test the repot.

You will learn how to: use query builder, set table relationship, change properties from property palette, use report style matrix, make subtotal for rows and columns, use the “select parent frame” icon, create grid, use the conditional formatting.

Create a new report
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 with their stocks’ symbol and number of shares owned.
(SQL Query Statement)
SELECT last_name, stock_symbol, shares_owned
FROM portfolio
WHERE ( id = customer_id)
Then click ‘OK.’

Change a SQL box’s property
In the Data Model window, right click on the SQL box (Q_1) and open the property palette option.
In the Property Palette window, change the name to Q_PORTFOLIO. Then close the window.

Report Wizard
In the Data Model window, on the toolbar, select the Report Wizard icon.

Matrix Report
A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results.

Style, Data, Rows, Columns, Cell, Totals, and Template tabs
In the Style tab in the Report Wizard, choose the report style as Matrix with a title of ‘Customer Portfolio.’ Then click on the ‘Next’ icon.
In the Data tab, select the data that you will use in your report. You should have already had that SQL statement. Don’t change anything and click Next.
In the Rows tab, select LAST_name as a "Matrix Row Field" and click Next.

In the Columns tab, use “Stock_symbol” as a "Matrix Column Field" and click Next.
In the Cell tab, select the sum of the shares_owned as a "Matrix Cell Fields" and click Next.
In the Totals tab, to make subtotals for rows and columns, select SumShares_OWNED to calculate the sum of rows or columns {Sum(SumShares_OWNED)}. Then click Next.
In the Labels tab, delete the label for the sum of the shares owned, change Last_name to name, change stock_symbol to symbol, and then click Next.
In the Template tab, use ‘Cyan Grid’ template report and click ‘Finish.’

Navigate through a report
Now, you created a customers portfolio report. Navigate through the report.
Layout Report Editor
Use the layout report editor to change the report layout.

Layout Model
Click on the “Layout Model” icon on the top of the horizontal toolbar.
In the Layout Model window, you can change the size of each item. Use the "select parent frame" icon to go to the parent's column and resize it. When finished resizing, click on the “run” icon to run the report.

Live Pre-viewer
In the Live Pre-viewer, select the name item and then change its alignment to left or right. You can also right click on it, open its property palette, and change its alignment.

Change appearance of a report
Select the cells and change their alignment and format mask. Do the same for the columns' subtotals.

Navigate through the report.
Conditional Formatting
In the Live Pre-viewer, right click on the cells, and open the "Conditional Formatting" option.
In the Conditional Formatting window, you can define exceptions to highlight data for specified conditions with different formatting such as color.
In the Conditional Formatting window, click “New” and In the Format Exception window, change the shares owned value to red if it is greater than or equal 2000.
SHARES_OWNED >= 2000

Click “OK.”
Check the report.

Go to the last page, change the alignment and mask format for the row's subtotals; and change the color if their values are more than 7,000.

Go to the first page.
Do the same for the columns' subtotal; and change the color if their values are more than 10,000.

After testing the report output, highlight the report and save it as report number 4 in the iself folder (REPT04).




Questions:
Q: What is a Matrix Report?
Q: How do you set a table relationship in the report builder?
Q: How do you change an object using its properties palette?
Q: How can you make a sub-total for rows and columns in a matrix report?
Q: What does the Select Parent Frame icon?
Q: What is a conditional formatting in the report builder?
Q: You client needs to get the summary report of their customers shares owned. They like to have the output format be the same as spreadsheet format. You are assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock.
This report should show the number of shares of stock’s holders by each customer in each of the stocks. Make sure the cell format mask is NNN,NN0. You should use the customer and portfolio table, and put grid around each number of shares for easy reading. You should also get the totals for each column and row.
They want you to change the cell color to red if its value is greater than 2000 and do the same for rows and columns sub-total.

Your tasks are:
1- Create a matrix report.
2- Put grid around each cell.
3- Use user layout format mask for cells and sub-totals as NNN,NN0.
4- Calculate the subtotal for rows and columns.
5- Highlight the cells with any color (ex: red) if their values are significant.
6- Highlight the sub-total cells with any color (ex: pink) if their values are significant.
7- Apply template to the report.
8- Run the report.
9- Test the repot.

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.

Group above report

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.

oracle reports Template

Figure 7

Template

Introduction
Templates are generic objects that allow you to force a set of standard features and attributes onto your reports. They help you impose uniformity on your.

Hands-on
They need their logo to be displayed at each new or old report. They ask you to create a template that contains their logo. Then run a report.
See Figure 7.

Your tasks are:
1- Write a template containing your client logo.
2- Run the report against the new created template.
3- Test the report.

You will learn how to: create or modify an existing template, save the template with new name, change the default, delete the logo, replace the new logo, save your template.

Open a Report Builder
Login to the Report Builder.

Connect to a database
In the Object Navigator, highlight the Database object, go to the Main menu, choose File, select the Connect option, and in the Connect window enter your USERID and PASSWORD.

Open a Report Module
In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (REPT06) from the “iself” folder.

Open an existing Template
In the Object Navigator, highlight the “Template” item, choose File from the Main menu, and select the Open option to open an existing template.

Templates
Templates are generic objects that allow you to force a set of standard features and attributes onto your reports. They help you impose uniformity on your.
You can find all the Templates in you PC or Share Server. Look for all the objects that their extensions are ‘.tdf.’ Select one that are very similar to your template layout, and start from there.
In this hands-on, the selected template is in:
C:.tdf
It is easier to change an existing “template” rather than to create it from scratch.

Save a template
Open the template and save it as a new name (ISELF_TEMPLATE) in the "ISELF" folder.

Modify a template
In the Object Navigator, double click on the "Layout Model" icon of ISELF_TEMPLATE.
In the ‘Template Editor - Layout Model’ window, you can zoon-out or zoon-in to view the template; and then click on the Down Arrow next to the “Report Style” box to select your report style. You report style can be: Tabular, Group Left, Group Above, Form-like, Mailing Label, Form Letter, matrix, and Matrix with Group.
Notice that a template has so many different report styles. To view them, click on each report style and browse through them.

Import company logo
In this hands-on, select the default template in the Template editor and delete the report layout logo and import your own organization logo. To import, go to the Main menu, and choose File > Import > Image… In the Import Image window, select your image file, its format and Quality. The click OK.

Resize company logo
In the Template Editor, resize your logo if needed. If you are done with your template layout, save the template. To save the template, highlight ISLEF_TEMPLATE and then click on the Save icon on the toolbar. Or, go to the Main menu, choose File > Save option.

Save Template and remove it from workstation
Once you save the template, you can delete it from your Object Navigator. Highlight ISELF_TEMPLATE, click on the ‘X’ icon (Delete). This will remove the object from Object Navigator and the template is saved.
Then remove the template from the "object navigator."

Now, you can run the REPT06 using the new template. Make sure that you are in the REPT06 report. Run the report with its old template.

Assign the new template
In the Live Pre-viewer, click on the "Report Wizard" icon. In the ‘Report Wizard’ window, open the Template tab. Your template is not in the Predefined template; therefore, you should us ‘Template file’ by check marking its radio button. Type or find your template and then click ‘Apply’ and/or ‘Finish.’

Run the report
Now, your report has its new template. Navigate through the report pages.
After testing the report output, highlight the report and save the changes as the report number 7 (REPT07).

Questions:
Q: What are the templates in the report module?
Q: How can you create or modify an existing template?
Q: Your client needs their logo to be displayed at each new or old report. They ask you to create a template that contains their logo. Then run a report.
See Figure 7.

Your tasks are:
1- Write a template containing your client logo.
2- Run the report against the new created template.
3- Test the report.

Layout Appearances (Boilerplate)

Figure 8

Layout Appearances (Boilerplate)

Now, you client needs to improve on report appearance. They ask you for any artistic changes.
Be creative and artistic to change the appearance of their reports. Use boilerplate to change the title and draw graphic to their reports. Your client wants you to use your imagination that has no limits.
See Figure 8.

Your tasks are:
1- Modify the appearance of a report.
2- Use your creative imagination.
3- Run the report.
4- Test the report.

You will learn how to: use boilerplate; use your imagination that has no limit.

Login to the Report Builder.
Connect to a database
In the Object Navigator, highlight the Database object, go to the Main menu, choose File, select the Connect option, and in the Connect window enter your USERID and PASSWORD.
Open a Module
In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (REPT07) from the “iself” folder. Run the report.

Layout Model
In the Live Pre-viewer, click on the "Layout Model" icon.
In the ‘Report Editor - Layout Model’, click on the "Edit Margin" icon on the horizontal toolbar. Your report heading margin will be appeared. You can change anything on that margin.

Change appearance of a report
Use your creative imagination and change the report title. There is no limit on your creativities. By now, you should know how to use the Boiler plate objects. We have done that in one of Forms’ hands-on. Review that and do the same here.

Run the report.
Navigate through the report pages.

After testing the report output, highlight the report and save the changes as the report number 8 (REPT08).

Questions:
Q: What is the boilerplate in the Layout Editor?
Q: How do you select an object or objects in the layout editor?
Q: How do you move an object or objects in the layout editor?
Q: How do you delete an object or objects form the layout editor?
Q: When do you use the Edit Margin icon?
Q: Now, your client needs to improve on report appearance. They ask you for any artistic changes.
Be creative and artistic to change the appearance of their reports. Use boilerplate to change the title and draw graphic to their reports. Your client wants you to use your imagination that has no limits.
See Figure 8.

Your tasks are:
1- Modify the appearance of a report.
2- Use your creative imagination.
3- Run the report.
4- Test the report.

How to use Parameters in oracle reports 6i

FIGURE-9

Parameters

Introduction
There are two types of parameters: user parameters and system parameters.

User Parameters
“User parameters” will be used to manage the behavior and output of your reports.

System Parameters
“System parameters” will be used to manage the report runtime behavior of your reports such as defining your output destination.

Parameter Form Builder
“Parameter Form Builder” tool will be used to design your runtime parameter form. The Runtime Parameter Form appears whenever you run your report.

Hands-on
The user wants to execute this report just against a specific company’s symbol. They want to use company’s symbol as a parameter to pass to the report. Also the report should calculate the total and average of shares sold for the company at the end of report.
You should create a simple report to list the name of customers including their shares owned.
See Figure 9.

Your tasks are:
1- Create a new portfolio report for each company.
2- Use parameter to run the report for a specific symbol.
3- Print the list of their customers and their number of shares.
4- Print the total and average of shares sold for the company.
5- Run the report.
6- Test the report.

You will learn how to: use the “group above,” use the “query builder,” set table relationship, create and use binding variable, calculate total and average shares, delete the object from layout.

Figure 9

Open the Report Builder.
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.’

Select a style
In the Report Wizard, type ‘Customer List’ in the Title box, choose the ‘Group Above’ style and click Next.

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.

SQL Query Statement
In the SQL Query Statement, write a SQL statement to query customers’ information (Name), and their portfolio information (Number of shares, and stock’s symbol) with an binding variables to be used as parameter field.
(SQL Query Statement)
SELECT ALL first_name, last_name,
stock_symbol, shares_owned
FROM customers, portfolio
WHERE id = customer_id and
stock_symbol = :symbol

Use of a Colon
The colon next to the symbol (:symbol) means: pass a symbol as a parameter to the report.

Click OK. You get This message:
Note: The query ‘Q_1’ has created the bind parameter(s) ‘SYMBOL’.
Click "OK" to confirm it.
Group fields
In the Report Wizard, in the Group Field section, select the "Stock_Symbol" item as a "Group Fields."

Display Fields
In the Display Fields section, select all the items to display in your report. You may add other fields later.

Total and Average …
Calculate the total and average shares sold for a company (Sum(SHARES_OWNED), Average(SHARES_OWNED)).

Label and Template Sections
In the Label section, modify the labels and widths for your fields and totals as desired.
In the Template section, use the default template and click Next.
In the congratulation section, click Finish to run the report.

Parameter Form Prompt
The parameter form will be opened. In the Runtime Parameter Form, enter the company’s symbol such as ORCL, MSFT, etc. The click on the Run icon to run the report.

Remove an object or objects
Hold the control key and click on the duplicate Total and Average objects. Then press the "delete" function key to remove them. Objects will be removed.

Format Masking and Layout adjustment
Do all the items format mask and layout adjustment, if needed! Navigate through the report pages. After testing the report output, highlight the “UNTITLED” report and save the changes as the report number 9 (REPT09).
Run a report and pass a parameter
In the Object Navigator on the vertical toolbar, click on the ‘Run’ icon to run the report with different company symbol. In the Runtime Parameter Form, enter the company’s symbol that does not exist. The click on the Run icon to run the report.

Questions:
Q: How do you create a binding variable?
Q: How do you use a binding variable?
Q: How do you remove an object from Layout Editor?
Q: The user wants to execute this report just against a specific company’s symbol. They want to use company’s symbol as a parameter to pass to the report. Also the report should calculate the total and average of shares sold for the company at the end of report.
You should create a simple report to list the name of customers including their shares owned.
See Figure 9.

Your tasks are:
1- Create a new portfolio report for each company.
2- Use parameter to run the report for a specific symbol.
3- Print the list of their customers and their number of shares.
4- Print the total and average of shares sold for the company.
5- Run the report.
6- Test the report.

Drill Down or Push Button Report

Figure-10

Introduction
A drill-down report lets you get more detailed information at a different level of data aggregation. The most common methods of drilling-down use the mouse or a button to call another report, graph, or form.

Hands-on
You client likes what you did on the previous Hands-On and ask you to use the report on the “Stock History” report. This should give them an option to get customers list (Customers List) for each company.
They ask you to create a drill down report on the company’s stock information so they get more detailed information about that company’s shares holders. Your report (Stock History) should have push button to call company’s shares holder from previous Hands-On (Customers List). The report should show for the security shown on the screen all customers of that security, the amount of their position, and the total number of shares held.
See Figure 10.


You will learn how to: use push button to execute another report, use or pass parameter, use the “select parent frame” icon, use or open objects property, compile report, use the boilerplate, write and compile PL/SQL trigger, use “runtime pre-viewer.”

Drill-Down Report
A drill-down report lets you get more detailed information at a different level of data aggregation. The most common methods of drilling-down use the mouse or a button to call another report, graph, or form.
Open the Reports Builder tool.

Open a Report Module
In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (REPT02) from the “iself” folder.

Save a Report Module
Save it as report number 10 (REPT10). You do this to leave the REPT02 module untouched.

Run a report
In the Object Navigator, on the vertical toolbar, click on the ‘Run’ icon to run the report.

Browse a report
Navigate through the report. Notice that there are black spaces under the SYMBOL and COMPANY column. That is a perfect place to add a push button to list the company's shares holders.

Create a push button
To do that: In the Live Pre-viewer, click on the "Layout Model" icon. In the ‘Report Editor - Layout Model’ window, select the company item, and click on the "Parent Frame" icon until the handlers do not change. Then drag the handler to make more free space to work with.
Once again, click on the COMPANY item, and click only once on the "Parent Frame" icon. Then drag the handler as far as the first one.

Click on the Push Button icon on the vertical toolbar and move the cross cursor to the “Layout Editor” under the SYMBOL and COMPANY columns and then click and drag to make a push button on a suitable size.
Right click on it and open its property. In its property palette, type “Customer's list” on the text box, change the “Type” item to "PL/SQL” and then double click on the “PL/SQL trigger’ item.

PL/SQL for push button
A Program Unit window will be opened. In its PL/SQL section, write a PLSQL statement to call the previous report that was created from the previous hands-on. Use the binding variable to pass the company symbol as a parameter. Write all the exception.
(PL/SQL)
PROCEDURE U_1ButtonAction
IS
BEGIN
-- make sure that REPT09 has already been compiled successfully.
srw.run_report (‘c:PARAMFORM=NO SYMBOL=’
:symbol)
EXCEPTION
WHEN srw.run_report_failure THEN
srw.message (1002, ‘Could not run the Detail Report Customers List’);
WHEN others THEN
srw.message (1003, ‘Unknown problem, please see your developer.’);
END;

Compile the PL/SQL block
Compile the trigger. If the output is successfully compiled, then close the window.
Close the property palette.
Close the “Layout Model” window.

Compile a report
Make sure that the REPT09 has already been compiled.
(File > Administration > Compile file…)
Run the new report.

Runtime Pre-viewer
To test the Push Button, you should use the "Runtime Pre-viewer” option. To use the "Runtime Pre-viewer” option, go to the Main menu, choose the ‘View’ sub-menu, select the ‘Runtime Pre-view…’ option, and the Pre-viewer screen will be appeared. Notice that your Push Button color was changed.

Now, click on "Customer's List" to run the report.

Navigate through the report pages and then click on the Customer's List to see the company's shares holders.

After the testing was done, close the windows and save the changes (REPT10).

Questions:
Q: Describe a drill-down report.
Q: How do you use a push button in the report?
Q: How do you execute a report form another report?
Q: How do you pass parameter from one report to another?
Q: How do you compile a report?
Q: How do you write a PL/SQL trigger?
Q: How do you compile a PL/SQL trigger?
Q: You client likes what you did on the previous Hands-On and ask you to use the report on the “Stock History” report. This should give them an option to get customers list (Customers List) for each company.

They ask you to create a drill down report on the company’s stock information so they get more detailed information about that company’s shares holders. Your report (Stock History) should have push button to call company’s shares holder from previous Hands-On (Customers List). The report should show for the security shown on the screen all customers of that security, the amount of their position, and the total number of shares held.
See Figure 10.

LOVs (List of Values)

LOVs (List of Values)

Introduction
A “List of Values”, can be used as a validation mechanism for values defined in user parameters. Your LOV or “List of Values” can consist either of static values or items queried from the database using select statements.

Hands-on

Since your parameter report is very handy for your client organization, they ask you to create a list of values (LOV) for the parameter so they can only select from the list rather than trying to remember the company symbol.
Also add more parameter to pass such as the destination of printer, and so on.

See Figure.
You will learn how to: use parameter, modify parameter properties using property palette, use query builder, and add more parameters to existing parameter form.

List of Values

A “List of Values”, can be used as a validation mechanism for values defined in user parameters. Your LOV or “List of Values” can consist either of static values or items queried from the database using select statements.
Open the Report Builder tool.

Open a Module
In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (REPT09) from the “iself” folder

Save a Module
Save it as report number 11 (REPT11). You do this to leave the REPT09 module untouched.
(File > Save as)

In the Object Navigator, on the vertical toolbar, double click on the "Layout Model" icon."

Open Parameter Form Builder window
In the ‘Report Editor - Layout Model’ window, click on the "Parameter Form" icon which is on the horizontal toolbar.
The Report Editor - Parameter Form window, will be opened. In the Parameter Form window, click on the "Parameter Form Builder" icon which is on the horizontal toolbar.

Add objects to Parameter Form
The Parameter Form Builder window will be displayed. In the ‘Parameter Form Builder’ window, change the Title item to ‘Customer’s Symbol List,’ click on the DESTYPE (Destination Type) parameter to select it, notice that the “Symbol” parameter has already been selected, and then click "OK."

Now in your ‘Report Editor - Parameter Form,’ you should see only two parameters such as Destination type and Symbol (PF_DESTYPE and PF_SYMBOL).

Change a Fields’ properties
Right click on the PF_SYMBOL field and open its property palette. In its parameter palette, type “ORCL” in the ‘Initial Value’ box, and double click on the "List of Values" box.
In the ‘Parameter List of Value’ window, checkmark the “Select Statement” radio button and type the following SQL statement into the ‘SQL Query Statement’ box.
SELECT DISTINCT symbol
FROM stocks

Click "OK," close the property window, and then close “Parameter Form Builder.”

Run a report
In the Object Navigator, highlight the report and click on the Run icon on the toolbar to run the report.

A parameter window will be displayed. Now, you can generate your report on a different destination type such as Screen, File, Printer, Mail, and Preview.
Select your prefer destination type, your company’s symbol, and click on the Run icon to run the report
Repeat this process for different companies.

At the end, highlight the report and save the changes (REPT11).



Questions:
Q: Describe a List of Values in the report module?
Q: How do you create a user parameter item?
Q: How do you define a system parameter item?
Q: How do you modify a user parameter properties using property palette?
Q: How do you use the Query Builder tool?
Q: Since your parameter report is very handy for your client organization, they ask you to create a list of values (LOV) for the parameter so they can only select from the list rather than trying to remember the company symbol.
Also add more parameter to pass such as the destination of printer, and so on.
See Figure 11.

Your tasks are:
1- Modify the “Customers List Parameter” Form.
2- Add LOV to the “symbol” item.
3- Add one more parameter “Destination Type” to the “customers List Parameter” Form.
4- Run the report.
5- Test the report.

IMPORTANT TIPS OF REPORTS 6I

IMPORTANT-READ CAREFULLY

Tabular report shows data in a table format. It is similar in concept to the idea of an Oracle table. Oracle, by default, returns output from your select statement in tabular format.

Group-left reports are very useful for generating output reports from SQL statements with the group by clause. The Group-left report suppresses redundant printings of the same value in the leftmost column of the report. It is like making a control break in your report. You can use the “Group-left” report on a master-detail relationship as well.
In Hands-On 02, you will experience how to generate a group left report.

A Mailing Label report is a list of addresses designed to be generated on mailing labels. The addresses can be printed across the page, and then down or vice versa.
In Hands-On 03, you will experience how to generate a Mailing Label report.

A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results.
In Hands-On 04, you will experience how to generate a Matrix report.

The Form letter report will complement the mailing label report. You can pull several database columns plus your mailing message to produce form mailings.
In Hands-On 05, you will learn how to generate a Form Letter report.

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.

The “Data Model” will be used to display the data content and layout appearance of the report.

The “Live Pre-viewer” will be used to view what the report will look like when it is executed.

Object Navigator will be used to view report components and navigate through the report objects. It gives you a hierarchical listing of all the modules open in your current session. You use this listing to navigate to, and work on, those objects. It gives you access to all database objects you own or have grant to, as well as a list of all the built-in functions and procedures you can use in Oracle Form Builder. Clicking on the plus sign next to an object in the Object Navigator will expand the object and clicking on the minus sign will collapse the object.

Property Palette contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. You use the Property Palette to modify object properties. To open the Property Palette of an object, go to the object and right click on the mouse then select Property Palette.

Templates are generic objects that allows you to force a set of standard features and attributes onto your reports. They help you impose uniformity on your.

Layout Model shows you how your report will appear. When you run your report, Report Builder fills in the objects in the Layout Model with actual data. The Layout Model will show you several types of layout objects and the relationships that exist between them. The Layout Model uses symbols and graphical representations to show these relationships.

There are two types of parameters: user parameters and system parameters.
“User parameters” will be used to manage the behavior and output of your reports.
“System parameters” will be used to manage the report runtime behavior of your reports such as defining your output destination.
“Parameter Form Builder” tool will be used to design your runtime parameter form.
The Runtime Parameter Form appears whenever you run your report.
A drill-down report lets you get more detailed information at a different level of data aggregation. The most common methods of drilling-down use the mouse or a button to call another report, graph, or form.
A “List of Values”, can be used as a validation mechanism for values defined in user parameters. Your LOV or “List of Values” can consist either of static values or items queried from the database using select statements.

The Object Navigator will be used to view report components and navigate through the report objects. It gives you a hierarchical listing of all the modules open in your current session. You use this listing to navigate to, and work on, those objects. It gives you access to all database objects you own or have grant to, as well as a list of all the built-in functions and procedures you can use in Oracle Form Builder. Clicking on the plus sign next to an object in the Object Navigator will expand the object and clicking on the minus sign will collapse the object.

A Property Palette contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. You use the Property Palette to modify object properties. To open the Property Palette of an object, go to the object and right click on the mouse, then select property palette.

ORACLE REPORTS Questions and Answers:

Questions and Answers:

Q: What is an Object Navigator in the Report Builder tool?

A: The Object Navigator window will be used in the Report Builder tool, to view report components and navigate through the report objects. It gives us a hierarchical listing of all the modules open in our current session. We use this listing to navigate to, and work on, those objects. It gives us access to all database objects we own or have grant to, as well as a list of all the built-in functions and procedures we can use in Oracle Form Builder.



Q: What does the Object Navigator contain?

A: The Object Navigator contains the following items:

· Reports

· Templates

· External SQL Queries

· PL/SQL Libraries

· Debug Actions

· Stack

· Built-in Packages

· Database Objects.

Q: List all important objects that are handy in the Reports object.

A: The following are a list of important objects in the Reports object. These objects are very handy to create, delete, and modify a report.

· Live Pre-viewer

· Data Model

· Layout Model

· Parameter Form

· Report Triggers

· Program Units

· Attached Libraries



Q: What is a Pre-viewer in the Report Builder?

A: The Live Pre-viewer object is a tool that provides developer to view what the report will look like when it is executed.



Q: What is a Data Model in the Report Builder?

A: The “Data Model” in the Report Builder is a tool that will provide developer to display the data content and layout appearance of the report.



Q: What is a Layout Model in the Report Builder?

A: The Layout Model object is a utility in the Report Builder that shows users how their reports will appear. When you run your report, Report Builder fills in the objects in the Layout Model with actual data. The Layout Model will show you several types of layout objects and the relationships that exist between them. The Layout Model uses symbols and graphical representations to show these relationships. You can add, modify or delete part of your layout from here.



Q: What is a Parameter Form in the Report Builder?

A: The Parameter Form in the Report Builder is a utility that provides developers how to define, remove, and modify their report parameters. They will use the “Parameter Form Builder” tool to design their runtime parameter form.



Q: What are the types of parameters in the Report Builder? Describe them.

A: There are two types of parameters: user parameters and system parameters. The “User parameters” will be used to manage the behavior and output of your reports. The “System parameters” will be used to manage the report runtime behavior of your reports such as defining your output destination. Remember that when you run your report, the Runtime Parameter Form will be appeared.



Q: What are the report triggers?

A: The Report Triggers are a small unit of a program that developers write. They will be used to define PL/SQL to fire at various points during the execution of your report. You will have:

· BEFORE REPORT,

· AFTER REPORT,

· BETWEEN PAGES,

· BEFORE PARAMETER FORM, and

· AFTER PARAMETER FORM.



Q: Describe program units and attached libraries in the report builder?

A: The “Program units” section will be used by developers to write their PL/SQL programs. The developers use the “PL/SQL libraries” to store their stored procedures and “Attached libraries” to access to those created stored procedures. You will use Program Units and Attached Libraries as the same way we use them in the Forms Builder tool.



Q: Describe property palette in the report builder?

A: The Property Palette in the Report Builder contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. We use the Property Palette to modify object properties. To open the Property Palette of an object, we can go to the object and right click on the mouse then select Property Palette.



Q: What is the property sheet for an object?

A: The contents of the Property Palette are referred to as the “Property sheet” for the object.



Q: What is the "Query" box in the "Data Model" window?

A: It refers to a SQL statement.



Q: What is the "Group" box in the "Data Model" window?

A: It refers to a control break or grouping rows. A SQL statement can have one single group referring to its entire table or more than one by using the GROUP BY statement.



Q: What is a Tabular report?

A: A Tabular report shows data in a table format. It is similar in concept to the idea of an Oracle table. Oracle, by default, returns output from your select statement in tabular format.



Q: What is a Group-Left report?

A: The Group-left report suppresses redundant printings of the same value in the leftmost column of the report. It is like making a control break in your report. You can use the “Group-left” report on a master-detail relationship as well.



Q: Describe a Mailing Label report.

A: A Mailing Label report is a list of addresses designed to be generated on mailing labels. The addresses can be printed across the page, and then down or vice versa.



Q: What are the handlers in the layout model?

A: The handlers are small square boxes that have been attached to a selected object. You can use those handlers to change the size of an object by dragging the handlers.



Q: What is a Matrix Report?

A: A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results.



Q: How do you set a table relationship in the report builder?

A: 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 one item (the item that needs a link) then click and drag it to another item (the item that needs a link) of the other group. Now, you establish and set a table relationship between these two tables.



Q: What does the Select Parent Frame icon?

A: The "Select Parent Frame" icon selects the parent frame of the selected object. The position of the selected object (handlers) will change if the object has a parent.



Q: What is a conditional formatting in the report builder?

A: In the Live Pre-viewer, if you right click on the cells, and open the "Conditional Formatting" option, you can change the display output of the object. In the Conditional Formatting window, you can define exceptions to highlight data for specified conditions with different formatting such as color.



Q: What is a Form Letter report?

A: The Form letter report will complement the mailing label report. You can pull several database columns plus your mailing message to produce form mailings.

Q: What is the boilerplate in the Layout Editor?

A: The boilerplate utility in the Layout Editor will be used by developers to change the text fond or color and draw graphic on a report.



Q: Describe a GROUP-ABOVE report in the report builder.

A: 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.



Q: How do you use a Master-Detail report in the Data Model?

A: You use a Master-Detail report in the Data Model to generate a control break report.



Q: How do you create multiple SQL queries in the Data Model?

A: In the Data Model, you can create as many SQL queries as you need. To create a SQL query, just 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. Then in the ‘SQL Query Statement’ box, write your SQL statement.



Q: How do you create a data link in the Data Model?

A: 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 one item (the item that needs a link) then click and drag it to another item (the item that needs a link) of the other group. Now, you establish and set a table relationship between these two tables.



Q: What is the formula column and how do you create it?

A: The Formula Column is an object that a developer defines to establish his/her own formula. To create it, click on the "Formula Column" icon in the Data Model, drag the cross to the Group object and click on it again. Then right click on it, open its property palette, and change its name. In the Property Palette, double click on the PL/SQL Formula box. In the PL/SQL window, write a PL/SQL block to calculate your formula.



Q: What is the summary column and how do you create it?

A: The summary column is an object that a developer defines to establish the SUM, AVG, MIN, etc functions on a table. To create it, go to the Data Model window, select the Summary Column icon, move the cross cursor to the "group customer id" and click on the box. Then right click on it, and open its property palette. In the property palette window, change its name, change its "source," and change the "Reset At" options."



Q: What are the templates in the report module?

A: The Templates are generic objects that allow you to force a set of standard features and attributes onto your reports. They help you impose uniformity on your.



Q: How do you select an object or objects in the layout editor?

A: To select an object, click on the object and you will see the handlers (small boxes) attached to the object. To select multiple objects, move the cursor outside of all the objects that needs to be selected and click and drag the cursor to put all the objects in a box, and then release the cursor. Now, all the objects should have handlers attached to them.



Q: How do you move an object or objects in the layout editor?

A: Take the cursor on the selected object(s)--in side of its handlers--and then drag the object(s).



Q: How do you delete an object or objects form the layout editor?

A: Select an object and click on the delete key.



Q: When do you use the Edit Margin icon?

A: In the ‘Report Editor - Layout Model’, click on the "Edit Margin" icon on the horizontal toolbar. The report heading margin will be appeared. Now, we can change anything on that margin.



Q: How do you create and use a binding variable?

A: A colon next to an item means that the item is a binding variable. For example to define a binding variable, see the following SQL statement.

SELECT ALL first_name, last_name FROM customers, portfolio

WHERE id = :id; (:id is a binding variable)



Q: Describe a drill-down report.

A: A drill-down report lets you get more detailed information at a different level of data aggregation. The most common methods of drilling-down use the mouse or a button to call another report, graph, or form.



Q: Describe a List of Values in the report module?

A: A “List of Values”, can be used as a validation mechanism for values defined in user parameters. Your LOV or “List of Values” can consist either of static values or items queried from the database using select statements.



Q: How do you create a user parameter item?

A: A colon next to an item means that the item is a user parameter. For example to define a user parameter, see the following SQL statement.

SELECT ALL first_name, last_name FROM customers, portfolio

WHERE id = :id; (:id is a binding variable)



Q: How do you define a system parameter item?

A: In the ‘Report Editor - Layout Model’ window, click on the "Parameter Form" icon which is on the horizontal toolbar. The Report Editor - Parameter Form window will be opened. In the Parameter Form window click on the "Parameter Form Builder" icon which is on the horizontal toolbar. The Parameter Form Builder window will be displayed. In the ‘Parameter Form Builder’ window, change the Title item if you wish and then click on the DESTYPE (Destination Type) parameter, etc to select system parameters.