Creating a Report Manually Using Reports Builder

Purpose

In this tutorial, you learn how to create a report manually, first building the data model and then defining the layout for the data model.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

	Overview
	Scenario
	Prerequisites
	Creating a Data Model using the Data Wizard
	Creating a Paper Layout for the Data Model
	Generating Report Output to a PDF File
	Summary
	Related information

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

A report definition, or module, consists of objects that collectively define a report. Two key components in a report definition are the data model and the layout. The data model is a relational model that defines the data to be fetched from the data source(s), the values to be computed, and the order in which the data should appear in a report. Reports Builder objects that define the data model are queries, groups, columns, parameters, and links. The layout contains the formatting information about how the data should appear in the output. Layout objects include repeating frames, frames, fields, boilerplate, and anchors.

With Reports Builder, you can create a new report using the Report Wizard, or create a new report manually. Using the Report Wizard, you are guided through the steps to build a report for both Web and paper layouts: creating a new report definition, defining the data model, and specifying a layout. To learn more about creating a report using the Report Wizard, see the tutorial Creating a Report Using the Report Wizard.

When you choose to create a new report manually, Reports Builder creates a new default report definition for you. You then create the data model and layout separately.

In this tutorial, you will create a new report manually. You will learn how to use the Data Wizard to create a query for the data model. Then you will learn how to define a paper layout for the data model using the Report Block Wizard.

Back to Topic List

Scenario

In this tutorial, you will create a report for an international company. The report will display department and employee information for the company's regional facilities located in each country.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.	Have access to or have installed Oracle Reports Developer.
2.	Have access to or installed the sample schema. This tutorial uses the HR schema included in the Oracle10g database.
3.	Download reportsOBE.zip and unzip into your working directory.

Back to Topic List

Creating a Data Model Using the Data Wizard

To begin this tutorial, you will create a new report module and use the Data Model Wizard to define your query. Specifically, you will access Query Builder from the wizard to generate a query with multiple tables, restrict the data returned by adding a condition to the query, sort the data.

Back to Topic List

1.
If it is not already open, start Oracle Reports by clicking the Reports Builder icon on your desktop. The welcome screen displays. Select the option Build a new report manually. Click OK.

If Reports Builder is already open, create a new report by selecting File > New > Report from the menu.

Select the option Build a new report manually. Click OK.

In both cases, the Data Model view of the Report Editor displays.

2.
The Data Model view is a work area in which you create, define, and modify data model objects to be used in your report. Data model objects include queries, groups, columns, parameters, and links. Reports Builder uses the data model to determine what data to retrieve for the report.

Add a query to the data model by selecting Insert > Query from the menu. This action invokes the Data Wizard.

Click Next.

3.
Reports Builder assigns a default name to each query you build with the Data Wizard. Give your query a meaningful name by entering Departments_Query in the Query name field.

Be sure that the Matrix query check box is cleared. Click Next.

4.
For the data source, accept SQL Query as the data source type by clicking Next.

5.
On the data source definition page, click Connect to connect to the database.

In the Connect dialog box, enter HR for the User Name, HR for the Password, and the name of your database for the Database field.

Note: If you were previously working in this Reports Builder session using a different schema, you will have to connect to the HR schema in order to complete this tutorial.

6.
The Data Source definition dialog box displays. You can now type in a SQL statement, define a query visually using Query Builder, or import an existing file.

For this tutorial, you will use Query Builder to define your query. Query Builder is an easy-to-use data access tool that provides a logical and intuitive means to access information from your organization's databases for analysis and reporting. You can use Query Builder to define almost any query that you would build using a SQL SELECT statement. Query Builder automatically generates the appropriate SELECT FROM [table.column] clause based on columns displayed in the Query Builder workspace.

Click the Query Builder button to define your query. The Query dialog box displays, as does a window displaying the data tables, views, snapshots, and synonyms to which you have access.

Select the EMPLOYEES table from the list of tables. Click Include. Click Close. A graphical representation of the Employees table displays in the right panel of the Query dialog box. This panel is the Data Source panel.

7.
The Departments_Query will contain several tables. Click the Select Related Tables icon on the Query Builder icon bar to display tables related to the Employees table.

Select the HR.DEPARTMENTS table.

Click Include and then click Close. A graphical representation of the Departments table is added to the Data Source panel, and the primary key/foreign key relationships between the two tables are shown.

8.
Now display the tables in the database that are related to the DEPARTMENTS table. With the focus on the DEPARTMENTS table, click the Select Related Tables icon .

Select the HR.LOCATIONS table.

Click Include and then click Close. A graphical representation of the LOCATIONS table is added to the Data Source panel and the primary key/foreign key relationship between the DEPARTMENTS and LOCATIONS tables is shown.

9.
Include one more table in this query. With the focus on the LOCATIONS table, click the Select Related Tables icon .

Select the HR.COUNTRIES table.

Click Include and then click Close. A graphical representation of the COUNTRIES table is added to the Data Source panel and the primary key/foreign key relationship between the LOCATIONS and COUNTRIES tables is shown.

10.
Now that you have selected the tables for the query, choose and order the columns that will appear in the report.

Select the Column Sequence icon from the icon bar in Query Builder.

The Column Sequence dialog box displays all the available columns in the left panel, and the displayed columns in the right panel. Notice that the columns are prefixed with their respective table names.

Select DEPARTMENTS.DEPARTMENT_NAME from the Available Columns list and click the >>Copy>> button to move the column to the Displayed Columns list. As an alternative, you can double-click the column name to move the column to the Displayed Columns list.

Repeat this process for EMPLOYEES.FIRST_NAME, EMPLOYEES>LAST_NAME, EMPLOYEES.SALARY, LOCATIONS.CITY, COUNTRIES.COUNTRY_NAME, and EMPLOYEES.HIRE_DATE.

Click OK.

11.
Next, you want to ensure that the data returned from the query is sorted by the column DEPARTMENT_NAME.

Select the Sort icon in the icon bar in Query Builder. The Sort dialog box displays.

Select DEPARTMENTS.DEPARTMENT_NAME from the Available Columns list and click the >>Copy>> button to move the column to the Sorted Columns list. As an alternative, you can double-click on the column name to move the column to the Sorted Columns list.

Now highlight DEPARTMENT_NAME in the Sorted Columns list and ensure that the Ascending option, the default value for Sorting Order, is selected. Notice the arrow to the left of DEPARTMENT_NAME. An upwards arrow indicates Ascending sort order, while a downwards arrow indicates Descending sort order.

Click OK.

12.
For this report, you want to display data for those employees hired after January 1, 1990. To restrict the data retrieved from the database, you can use Query Builder to add a condition to the query.

Thus far in the tutorial, you have worked in the Data Source panel on the right side of the Query dialog box. Now you will work in the Conditions panel on the left.

In the Query window, click the blank conditions field in the Conditions panel.

Next, click HIRE_DATE in the EMPLOYEES table. This action causes the column EMPLOYEES.HIRE_DATE to be entered into the condition field.

Complete the expression by entering the criteria: EMPLOYEES.HIRE_DATE > '01-JAN-90'. Be sure to include the single quotation marks.

Finally, click the Accept icon when you are satisfied with the condition expression.

13.
You've completed the definition of the query for this report. Click OK. The resulting SQL statement is displayed for you.

Click Next.

14.
For this report, you will group department and employee information by country.

Select COUNTRY_NAME in the Available Fields list and click [>] to move it to the Groups Fields list.

Click Next

15.
You do not have to calculate any totals for this report. Click Next.

You've completed the definition of your data model for this report. Click Finish. A graphical representation of your data model displays in the Data Model view of the Report Editor. Notice the hierarchical structure of the data model: the master group is G_COUNTRY_NAME and the detail group, containing department and employee information, is G_DEPARTMENT_NAME.

16.
Save your report as **DepartmentsReport_< your_initials >.jsp .

To save a report definition, click the Save icon in the toolbar, or select File > Save from the menu. The first time you save your report definition, the Save dialog box displays, giving you the opportunity to enter a meaningful name for your report.

Click Save**.

Back to Topic List

Creating a Paper Layout for the Data Model

Now that you have defined the data model for the report using the Data Wizard, you will create a layout to specify how the data should display in the report.

In this part of the tutorial, you will work with the Paper Layout view of the Report Editor. You will add a report block to the layout. A report block automatically inserts the data model and business logic into the report. Each time the report is run, the report block dynamically pulls the data defined by the data model into your report.

Back to Topic List

1.
Select the Paper Layout icon on the Report Editor toolbar to invoke the Paper Layout view.

Select Insert > Report Block from the menu. The Report Block Wizard displays.

Note: The Report Block Wizard is very similar to the Report Wizard. You can specify a title and report style, select the group or groups as desired, select the fields you want to display in the report, modify the labels, and choose a template. However, the Report Block Wizard enables you to retain the existing paper and Web layouts defined for the particular data model, while, at the same time, enhance both. If you invoke the Report Wizard from the Paper Layout view, however, you will overwrite all paper and Web layout specifications previously defined for the report. Another feature of the Report Block Wizard is that it enables you to include multiple data blocks in a single report.

2.
Enter a report title of Departments Report and choose Group Above as the report style.

Click Next.

3.
You now select the group(s) you want to display in your report and the direction in which the records should repeat. Your data model contains two groups, one containing country information and the other containing department and employee information.

Select G_COUNTRY_NAME from the Available Groups list and click the Down > button. This moves the master group to the Displayed Groups list.

Repeat the same action for the detail group, G_DEPARTMENT_NAME.

Click Next.

4.
You now need to decide on the fields you want to display in the report. From the Available Fields list, select COUNTRY_NAME and click the [>] button. Repeat this process for CITY, FIRST_NAME, LAST_NAME, SALARY, HIRE_DATE, and DEPARTMENT_NAME fields.

Ensure that the displayed fields are in the order shown below. You can drag and drop a field to modify the order.

Click Next.

5.
On the next panel of the wizard, you can modify the default labels and widths. For this tutorial, keep the defaults as they are.

Click Next.

6.
As the final step in the Report Block Wizard, you can include a template in the report definition. Templates allow you to create a standard look and feel, and also promote reusability. In this tutorial, you will use one of the predefined templates for your report.

Select Beige from the Predefined template list.

Click Finish. You've successfully created the paper layout for your data model.

Save your report definition by clicking Save in the toolbar.

7.
To preview the paper layout, click the Run Paper Layout icon on the toolbar, or select Program > Run Paper Layout from the menu.

The paper layout for your Departments Report displays in the Paper Design view.

Back to Topic List

Generating Report Output to a PDF File

Reports Builder can generate report output to a PDF file, containing the formatted data and all objects. When you generate your report output to a PDF file, you can distribute the output to any PDF destination, including e-mail, printer, OracleAS Portal, and Web browser.

1.
Select the report in the Object Navigator.

2.
Select File > Generate to File > PDF from the menu. Save your report output as **DepartmentsReport_<your initials>.pdf, such as DepartmentsReport_sk.pdf**.

Back to Topic List

Summary

In this lesson, you've learned how to:

	Create a data model using the Data Wizard
	Create a paper layout for the data model
	Generate report output to a PDF file

Back to Topic List

Related Information

	To learn more about Oracle Reports 10g, refer to the additional Reports OBEs.
	Visit the OTN Web site for technical papers, presentations, frequently asked questions, demonstrations, and more.

Back to Topic List

1.	If it is not already open, start Oracle Reports by clicking the Reports Builder icon on your desktop. The welcome screen displays. Select the option Build a new report manually. Click OK. If Reports Builder is already open, create a new report by selecting File > New > Report from the menu. Select the option Build a new report manually. Click OK. In both cases, the Data Model view of the Report Editor displays.
2.	The Data Model view is a work area in which you create, define, and modify data model objects to be used in your report. Data model objects include queries, groups, columns, parameters, and links. Reports Builder uses the data model to determine what data to retrieve for the report. Add a query to the data model by selecting Insert > Query from the menu. This action invokes the Data Wizard. Click Next.
3.	Reports Builder assigns a default name to each query you build with the Data Wizard. Give your query a meaningful name by entering Departments_Query in the Query name field. Be sure that the Matrix query check box is cleared. Click Next.
4.	For the data source, accept SQL Query as the data source type by clicking Next.
5.	On the data source definition page, click Connect to connect to the database. In the Connect dialog box, enter HR for the User Name, HR for the Password, and the name of your database for the Database field. Note: If you were previously working in this Reports Builder session using a different schema, you will have to connect to the HR schema in order to complete this tutorial.
6.	The Data Source definition dialog box displays. You can now type in a SQL statement, define a query visually using Query Builder, or import an existing file. For this tutorial, you will use Query Builder to define your query. Query Builder is an easy-to-use data access tool that provides a logical and intuitive means to access information from your organization's databases for analysis and reporting. You can use Query Builder to define almost any query that you would build using a SQL SELECT statement. Query Builder automatically generates the appropriate SELECT FROM [table.column] clause based on columns displayed in the Query Builder workspace. Click the Query Builder button to define your query. The Query dialog box displays, as does a window displaying the data tables, views, snapshots, and synonyms to which you have access. Select the EMPLOYEES table from the list of tables. Click Include. Click Close. A graphical representation of the Employees table displays in the right panel of the Query dialog box. This panel is the Data Source panel.
7.	The Departments_Query will contain several tables. Click the Select Related Tables icon on the Query Builder icon bar to display tables related to the Employees table. Select the HR.DEPARTMENTS table. Click Include and then click Close. A graphical representation of the Departments table is added to the Data Source panel, and the primary key/foreign key relationships between the two tables are shown.
8.	Now display the tables in the database that are related to the DEPARTMENTS table. With the focus on the DEPARTMENTS table, click the Select Related Tables icon . Select the HR.LOCATIONS table. Click Include and then click Close. A graphical representation of the LOCATIONS table is added to the Data Source panel and the primary key/foreign key relationship between the DEPARTMENTS and LOCATIONS tables is shown.
9.	Include one more table in this query. With the focus on the LOCATIONS table, click the Select Related Tables icon . Select the HR.COUNTRIES table. Click Include and then click Close. A graphical representation of the COUNTRIES table is added to the Data Source panel and the primary key/foreign key relationship between the LOCATIONS and COUNTRIES tables is shown.
10.	Now that you have selected the tables for the query, choose and order the columns that will appear in the report. Select the Column Sequence icon from the icon bar in Query Builder. The Column Sequence dialog box displays all the available columns in the left panel, and the displayed columns in the right panel. Notice that the columns are prefixed with their respective table names. Select DEPARTMENTS.DEPARTMENT_NAME from the Available Columns list and click the >>Copy>> button to move the column to the Displayed Columns list. As an alternative, you can double-click the column name to move the column to the Displayed Columns list. Repeat this process for EMPLOYEES.FIRST_NAME, EMPLOYEES>LAST_NAME, EMPLOYEES.SALARY, LOCATIONS.CITY, COUNTRIES.COUNTRY_NAME, and EMPLOYEES.HIRE_DATE. Click OK.
11.	Next, you want to ensure that the data returned from the query is sorted by the column DEPARTMENT_NAME. Select the Sort icon in the icon bar in Query Builder. The Sort dialog box displays. Select DEPARTMENTS.DEPARTMENT_NAME from the Available Columns list and click the >>Copy>> button to move the column to the Sorted Columns list. As an alternative, you can double-click on the column name to move the column to the Sorted Columns list. Now highlight DEPARTMENT_NAME in the Sorted Columns list and ensure that the Ascending option, the default value for Sorting Order, is selected. Notice the arrow to the left of DEPARTMENT_NAME. An upwards arrow indicates Ascending sort order, while a downwards arrow indicates Descending sort order. Click OK.
12.	For this report, you want to display data for those employees hired after January 1, 1990. To restrict the data retrieved from the database, you can use Query Builder to add a condition to the query. Thus far in the tutorial, you have worked in the Data Source panel on the right side of the Query dialog box. Now you will work in the Conditions panel on the left. In the Query window, click the blank conditions field in the Conditions panel. Next, click HIRE_DATE in the EMPLOYEES table. This action causes the column EMPLOYEES.HIRE_DATE to be entered into the condition field. Complete the expression by entering the criteria: EMPLOYEES.HIRE_DATE > '01-JAN-90'. Be sure to include the single quotation marks. Finally, click the Accept icon when you are satisfied with the condition expression.
13.	You've completed the definition of the query for this report. Click OK. The resulting SQL statement is displayed for you. Click Next.
14.	For this report, you will group department and employee information by country. Select COUNTRY_NAME in the Available Fields list and click [>] to move it to the Groups Fields list. Click Next
15.	You do not have to calculate any totals for this report. Click Next. You've completed the definition of your data model for this report. Click Finish. A graphical representation of your data model displays in the Data Model view of the Report Editor. Notice the hierarchical structure of the data model: the master group is G_COUNTRY_NAME and the detail group, containing department and employee information, is G_DEPARTMENT_NAME.
16.	Save your report as *DepartmentsReport_< your_initials* >.jsp . To save a report definition, click the Save icon in the toolbar, or select File > Save from the menu. The first time you save your report definition, the Save dialog box displays, giving you the opportunity to enter a meaningful name for your report. Click Save**.

1.	Select the Paper Layout icon on the Report Editor toolbar to invoke the Paper Layout view. Select Insert > Report Block from the menu. The Report Block Wizard displays. Note: The Report Block Wizard is very similar to the Report Wizard. You can specify a title and report style, select the group or groups as desired, select the fields you want to display in the report, modify the labels, and choose a template. However, the Report Block Wizard enables you to retain the existing paper and Web layouts defined for the particular data model, while, at the same time, enhance both. If you invoke the Report Wizard from the Paper Layout view, however, you will overwrite all paper and Web layout specifications previously defined for the report. Another feature of the Report Block Wizard is that it enables you to include multiple data blocks in a single report.
2.	Enter a report title of Departments Report and choose Group Above as the report style. Click Next.
3.	You now select the group(s) you want to display in your report and the direction in which the records should repeat. Your data model contains two groups, one containing country information and the other containing department and employee information. Select G_COUNTRY_NAME from the Available Groups list and click the Down > button. This moves the master group to the Displayed Groups list. Repeat the same action for the detail group, G_DEPARTMENT_NAME. Click Next.
4.	You now need to decide on the fields you want to display in the report. From the Available Fields list, select COUNTRY_NAME and click the [>] button. Repeat this process for CITY, FIRST_NAME, LAST_NAME, SALARY, HIRE_DATE, and DEPARTMENT_NAME fields. Ensure that the displayed fields are in the order shown below. You can drag and drop a field to modify the order. Click Next.
5.	On the next panel of the wizard, you can modify the default labels and widths. For this tutorial, keep the defaults as they are. Click Next.
6.	As the final step in the Report Block Wizard, you can include a template in the report definition. Templates allow you to create a standard look and feel, and also promote reusability. In this tutorial, you will use one of the predefined templates for your report. Select Beige from the Predefined template list. Click Finish. You've successfully created the paper layout for your data model. Save your report definition by clicking Save in the toolbar.
7.	To preview the paper layout, click the Run Paper Layout icon on the toolbar, or select Program > Run Paper Layout from the menu. The paper layout for your Departments Report displays in the Paper Design view.