Creating a Report Using the JDBC Pluggable Data Source

Purpose

The goal of this tutorial is to show you how to build a report using the JDBC pluggable data source (PDS).

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

	Overview
	Scenario
	Prerequisites
	Defining a SQL Query in the Data Model
	Adding a Second Query to the Data Model Using the JDBC PDS
	Creating a Layout for Your Report
	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

The information you must publish is often derived from data in various corporate data sources. These data sources may be SQL-based (relational databases) or non-SQL-based. Often, you must combine data from one or more of these data sources to publish meaningful information. For example, you may need to combine data that exists in a relational database with data from a multi-dimensional database to compare trends and performance.

Using Oracle Reports, you can access any Oracle or non-Oracle data source, such as XML, JDBC (relational and non-relational), metadata from another system, or even a Business Component for Java (BC4J). You can even combine data from one or more of these data sources in the same report. For example, in a business intelligence scenario, you can combine and publish data that exists in a relational database along with multi-dimensional (OLAP) data, allowing the comparison of trends and performance. In a Business-to-Business supplier scenario, you can join XML data obtained from an external partner’s application with data obtained from an internal database and merge this internal and external data in real-time to display the results in a Web page.

A pluggable data source (PDS) is an Oracle Reports feature that enables data input from numerous sources through the implementation of design time and runtime Java application program interfaces (APIs). Oracle Reports provides interface definitions, which act as a translator between Oracle Reports and a PDS by redefining Oracle Reports' requests in terms your data source uses. You can leverage the PDS architecture to connect to your own data source, as well as to the data sources available with Oracle Reports. The PDS feature also enables you to leverage various capabilities in Oracle Reports such as aggregation, summarization, formatting, and scheduling not only data that is accessed through SQL, but on data from any data source.

Java Database Connectivity, or JDBC, is a Java class library that provides a common way to maintain a connection to a database and enables you to access virtually any tabular data from the Java programming language. This provides you with cross-DBMS connectivity to a wide range of databases and other tabular data sources, such as spreadsheets and flat files. With a JDBC technology enabled driver, you can easily integrate all corporate data, even in a heterogeneous environment.

Back to Topic List

Scenario

In this tutorial, you will create a report displaying the product inventory in warehouses located in various countries around the world. In the first part of the tutorial, you will create a query in the data model to retrieve warehouse and inventory data from the OE schema in the Oracle 10g database. Then, using the JDBC pluggable data source, you will connect to the HR schema of the same database and add another query to the data model of your report. This query will retrieve location information for each warehouse. You will then link the two queries in the data model, define the layout for the report, and generate the report output to a PDF file.

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 OE and HR schemas included in the Oracle10g database.
3.	Download reportsOBE.zip and unzip into your working directory.

Back to Topic List

Defining a SQL Query in the Data Model

To begin this tutorial, you will create a new report module and define a SQL query in the data model.

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 clicking the SQL query icon on the tool palette and click anywhere in the empty work area. This action invokes the SQL Query Statement dialog box.

3.
Click Connect to connect to the database.

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

4.
You can now type in a SQL statement, define a query visually using Query Builder, or import an existing file.

For your convenience, a SQL query is provided for you. Click Import SQL Query.

In the file import dialog box, click OEquery.sql located in the your working directory. Click Open. The SQL statement is displayed for you.

Click OK.

The warehouse query is created and represented graphically in the Data Model view.

5.
Your data model contains a single group. With this structure, each row of data retrieved for the report will display with the same frequency.

For this report, you want to create a hierarchy in the data model so that the data for each warehouse displays only once, followed by several inventory records displaying product data. To achieve this, you need to create two groups in the data model.

Click and drag product_id, product_name, and quantity_on_hand down into a new group.

Your data model now contains two groups, G_warehouse_name and G_product_id.

Back to Topic List

Adding a Second Query to the Data Model Using the JDBC PDS

Now you will add warehouse location information to the data model. This data is stored in the HR schema of the Oracle 10g database. You cannot connect to another schema or another Oracle database using a SQL connection in the same report. You will create a JDBC connection to the HR schema using a preconfigured JDBC driver.

The JDBC pluggable data source is preconfigured with three JDBC drivers:

Oracle Thin JDBC driver

Oracle Thick JDBC driver

Javasoft JDBC-ODBC bridge

Merant JDBC drivers are supplied with Oracle Application Server for accessing other data sources. If you wish to use these drivers with the JDBC PDS, refer to the Oracle Application Server Installation Guide and the Oracle Application Server Reports Services Publishing Reports to the Web manual available on Oracle Technology Network.

In this tutorial, you will use the Oracle Thin JDBC driver.

1.
Click the JDBC Query icon on the tool palette and click anywhere in the Data Model work area. This action invokes the JDBC Query dialog box.

Here you define a JDBC query or stored procedure, and the connection parameters for your data source.

2.
Define the JDBC query.

For your convenience, the code is provided for you in the file HRquery.sql. Open this file from your working directory using a text editor, such as Notepad or WordPad.

Select all the code in this file by using Ctrl+A, and copy it by using Ctrl+C, or by selecting Edit > Copy from the menu.

Return your focus to the JDBC Query dialog box and paste the code in the Query/Procedure window by using Ctrl+V.

3.
Next, specify the parameters that the query will use to connect to the database.

Make sure that the sign-on parameter field contains the default value P_JDBCPDS.

Click Connect to display the JDBC Query Connect dialog box.

Connect to the HR schema in the Oracle 10g database. Enter HR for the User Name and HR for the Password. For the Database field, enter <hostname>:<port>:<SID> for your database. You can get this information from your tnsnames.ora file, or from your database administrator. Finally, select oracleThin for the Driver Type field.

Click Connect. Click OK.

You've created the second query in your data model.

4.
Now you will link these two queries by using the common field, location_id.

Click the Data Link icon on the tool palette. Click LOCATION_ID1 in the data model group QP_1 and create a link by dragging your mouse to location_id in the data model group Q_1. Your data model should look like this:

5.
Save your report as **WarehouseRpt_<your_initials>.jsp.

To save a report definition, select the report module in the Object Navigator window and 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.

Back to Topic List

Creating a Layout for Your Report

You have successfully defined the data model for your warehouse report. Your next step is to define the layout for the data model. You will use the Report Wizard to do this.

1.
Select Tools > Report Wizard from the menu. This action invokes the Report Wizard.

On the Report Type tab, accept the default option to Create both Web and Paper Layout by clicking Next.

2.
On the Style tab, select Group Above as the report style and enter Warehouse Report as the title.

Click Next.

3.
On the Groups tab of the Report Wizard, display all groups in your report.

Select G_COUNTRY_NAME in the Available Groups list and click the [Down >] button. The field is now displayed on the right in the Displayed Groups list. Repeat this process for G_warehouse_name and G_product_id.

Click Next.

4.
Now specify the fields to display in your report. From the Available Fields list, select COUNTRY_NAME and click the [>] button. Repeat this process for warehouse_name, product_id, product_name, and quantity_on_hand 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.
The wizard now allows you to modify the default labels and widths. For this tutorial, keep the defaults as they are.

Click Next.

6.
Keep the default setting for the template. Click Finish to complete the definition of your report. You now view the paper layout for your report in the Paper Design view of the Report Editor.

Save your report definition by clicking Save .

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 WAREHOUSERPT_<your initials> in the Object Navigator.

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

Click Save**.

Back to Topic List

Summary

In this lesson, you've learned how to:

	Define a query in the data model using the SQL query data source
	Add a second query to the data model using the JDBC pluggable data source
	Create a layout for the report
	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 clicking the SQL query icon on the tool palette and click anywhere in the empty work area. This action invokes the SQL Query Statement dialog box.
3.	Click Connect to connect to the database. In the Connect dialog box, enter OE for the User Name, OE for the Password, and the name of your database for the Database field.
4.	You can now type in a SQL statement, define a query visually using Query Builder, or import an existing file. For your convenience, a SQL query is provided for you. Click Import SQL Query. In the file import dialog box, click OEquery.sql located in the your working directory. Click Open. The SQL statement is displayed for you. Click OK. The warehouse query is created and represented graphically in the Data Model view.
5.	Your data model contains a single group. With this structure, each row of data retrieved for the report will display with the same frequency. For this report, you want to create a hierarchy in the data model so that the data for each warehouse displays only once, followed by several inventory records displaying product data. To achieve this, you need to create two groups in the data model. Click and drag product_id, product_name, and quantity_on_hand down into a new group. Your data model now contains two groups, G_warehouse_name and G_product_id.

	Oracle Thin JDBC driver
	Oracle Thick JDBC driver
	Javasoft JDBC-ODBC bridge

1.	Click the JDBC Query icon on the tool palette and click anywhere in the Data Model work area. This action invokes the JDBC Query dialog box. Here you define a JDBC query or stored procedure, and the connection parameters for your data source.
2.	Define the JDBC query. For your convenience, the code is provided for you in the file HRquery.sql. Open this file from your working directory using a text editor, such as Notepad or WordPad. Select all the code in this file by using Ctrl+A, and copy it by using Ctrl+C, or by selecting Edit > Copy from the menu. Return your focus to the JDBC Query dialog box and paste the code in the Query/Procedure window by using Ctrl+V.
3.	Next, specify the parameters that the query will use to connect to the database. Make sure that the sign-on parameter field contains the default value P_JDBCPDS. Click Connect to display the JDBC Query Connect dialog box. Connect to the HR schema in the Oracle 10g database. Enter HR for the User Name and HR for the Password. For the Database field, enter <hostname>:<port>:<SID> for your database. You can get this information from your tnsnames.ora file, or from your database administrator. Finally, select oracleThin for the Driver Type field. Click Connect. Click OK. You've created the second query in your data model.
4.	Now you will link these two queries by using the common field, location_id. Click the Data Link icon on the tool palette. Click LOCATION_ID1 in the data model group QP_1 and create a link by dragging your mouse to location_id in the data model group Q_1. Your data model should look like this:
5.	Save your report as *WarehouseRpt_<your_initials>.jsp. To save a report definition, select the report module in the Object Navigator window and 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.

1.	Select Tools > Report Wizard from the menu. This action invokes the Report Wizard. On the Report Type tab, accept the default option to Create both Web and Paper Layout by clicking Next.
2.	On the Style tab, select Group Above as the report style and enter Warehouse Report as the title. Click Next.
3.	On the Groups tab of the Report Wizard, display all groups in your report. Select G_COUNTRY_NAME in the Available Groups list and click the [Down >] button. The field is now displayed on the right in the Displayed Groups list. Repeat this process for G_warehouse_name and G_product_id. Click Next.
4.	Now specify the fields to display in your report. From the Available Fields list, select COUNTRY_NAME and click the [>] button. Repeat this process for warehouse_name, product_id, product_name, and quantity_on_hand 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.	The wizard now allows you to modify the default labels and widths. For this tutorial, keep the defaults as they are. Click Next.
6.	Keep the default setting for the template. Click Finish to complete the definition of your report. You now view the paper layout for your report in the Paper Design view of the Report Editor. Save your report definition by clicking Save .