REPORTS BUILDER TUTORIAL

1. Start Oracle 9i Report Builder. You can do so by click on Start - Programs – Oracle9i Developer Suite - Reports Developer - Reports Builder.  And you will see the following welcome screen:

To create a new report, click "Ok". Click "Next" to leave the Welcome Screen.

2. The Report type page appears, which allows you to specify how the report output appears in the Reports Builder environment. Make sure that the "Create both Web and Paper Layout" option is selected, and then click "Next".

3. The Style page appears, which allows you to specify the report title and style. Type Order Information in the Title field, make sure that the "Tabular" option is selected, and then click "Next".

4. The Data source page appears, which allows you to specify the source of the report data. Make sure that "SQL Query" is selected, and then click "Next".

5. The Data page appears, which allows you to type a SQL query, import a query from a script file, or build a SQL query using Query Builder. Type the following query to retrieve the report data fields, and then click "Next". Note that you do not need to type a semicolon at the end of the command.

select s_ord.id, s_customer.name, s_emp.last_name, s_ord.total, s_ord.date_ordered
from s_ord, s_customer, s_emp
where s_ord.customer_id=s_customer.id and s_ord.sales_rep_id=s_emp.id

6. Because you have not yet connected to the database, the Connect dialog box opens. Log onto the database in the usual way, and then click "Connect". If your SQL query is correct, the Fields page appears, showing the fields the query returns. If the Fields page does not appear, debug your query until it works correctly.

7. The Fields page allows you to select one or more fields in the Available Fields list to display in the report. Click the ">>" sign to select all query fields for the report. Click "Next".

8. The Totals page appears, allowing you to specify one or more fields for which you might want to calculate a total. Select "total" in the available fields, then click "Sum>" and "Average>" to calculate the sum and average of the order total. Click "Next".

The Labels page appears, which allows you to specify the report labels and field widths. Modify the field labels as follows, and then click "Next".
Field            Label
name           Customer Name
last_name     Sales Rep Name

9. The template page appears, which allows you to select a report template to define the characteristics of the report appearance. To select a predefined template, make sure the "Predefined template" option is selected. Choose a template, and click "Next", and then click "Finish". The report appears in the Report Editor - Page Design window. Save the report as "OrderInfo.rdf".

Click here to download the completed report.

Create a Master-Detail Report

Now we have created a simple report using the reports builder. Now we will learn to create reports that show master-detail relationships in which one record has many associated detail records through a foreign key relationship. And we will use the Reentrant Mode of report wizard to change the report we created before to a master-detail report.

1. Make sure that you select the ORDERINFO report in the OBJECT NAVIGATOR and right click to select "Report Wizard".

2. Now go to the Style tab, select the "Group Left" option.

3. Click "Next" until you see the Groups tab. Data in a master-detail report has multiple levels, and each data level represents a group. The top-level (master) group is Level 1, the next level is Level 2, and so forth. To specify report groups, you move the fields for each group from the Available Fields list to the Group Fields list. When you first move a field, Reports Builder automatically places the selection in Level 1. To add additional fields to Level 1, you select one of the fields currently in the Level 1, and add the new field. To create a Level 2 group, you select the Level 1 heading, and then move the new field. Level 2 will be automatically created with the selection in it. If you move all of the available feilds to the Group Fields list, an error occurs. So, you must leave the most detailed data fields in the Available Fields list.

Our example only has two levels, so we only need to move one field. Make sure the name field is selected, and then click the "Move one item to target" button. The Level 1 group appears, with the name field in it.

4. Click "Finish". The report appears with a master-detail format.

1. Open the report node in the Object Navigator, open the Data Model node, and then select the User Parameters node.

2. Click the "Create" button to create a new user parameter objects.

3. Select the new parameter if necessary, and then click it again so that the background of the parameter name turns blue. Change the names of the new user parameter to REP_ID, right-click, and then click "Property Inspector".

4. Make sure that the Datatype property value is Number.

Create the Parameter List

1. In the REP_ID Property Inspector, select the "List of values" property, and then click the "More" button. The Parameter List of Values dialog box opens.

2. Select the "SELECT Statement" option button.

3. Type the following query in the SQL Query Statement field.
select id, last_name from s_emp

4. To configure the list so it changed dynamically as users add new values to the S_CUSTOMER table, clear the "Restrict List to Predetermined Values" check box.

5. Click "OK", close the Property Inspector, and then save the report.

Modifying the Report Query

1. In the Object Navigator, double-click the Data Model icon to open the Data Model, and then double-click Q_1 to view the Data Model query.

2. Modify the query by adding the following code: and s_ord.sales_rep_id=:rep_id.

3. Click "OK" to save the changes and close the dialog box.

Run the Report

1. Click the "Run Paper Layout" button to run the report.

2. The report parameter form opens and displays a Rep ID list for selecting a representative ID from the list.

3. Open the parameter list, select rep ID 11, and then click the "Run Report" button to display the report.

4. The order information for rep ID 11 appears.

Click here to download the completed report.