Creating a Report Using the Text Pluggable Data Source

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

Approximately 1 hour

Topics

This tutorial covers the following topics:

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.

Back to Topic List

 

In this tutorial, you will create two reports using the text pluggable data source. In the first part of the tutorial, you will create a report using data in an Apache log file. The Apache log file format is provided as one of the default text PDS file formats in Reports Builder. In the second part of the tutorial, you will define a custom file format for the text PDS and then create a report using a custom text file as the data source. For both reports, you will generate the output to a PDF file.

Back to Topic List

Prerequisites

1.

Have access to or have installed Oracle Reports Developer.

2. Download reportsOBE.zip and unzip into your working directory.

Back to Topic List

Creating a Report Using a Default Text PDS File Format

In this topic, you create a report using a text file that contains Web server access data in a format following one of the file formats defined by default in Reports Builder.

Back to Topic List

1.

Start Oracle Reports by double-clicking the Reports Builder icon on your desktop. The welcome screen displays.

You have several choices for designing your report. For this tutorial, you will use the Report Wizard (the default).

Move your mouse over this icon to see the image

Click OK.

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


Move your mouse over this icon to see the image

Select the option Use the Report Wizard. Click OK.

Move your mouse over this icon to see the image


2.

Click Next on the Report Wizard welcome page.

Move your mouse over this icon to see the image

 

3.

The default option is Create both Web and Paper Layout. Click Next to accept this option.

Move your mouse over this icon to see the image

 

4.

On the next page of the Report Wizard, you specify the style of the report that you want to build and can include a title for your report.

Keep the default style as Tabular and enter Web Server Access Details as the title.

Move your mouse over this icon to see the image

Click Next.

 

5.

For the data source, select Text Query and click Next.

Move your mouse over this icon to see the image

 

6.

On the data source definition page, click Query Definition to display the query editor for the selected PDS.

Move your mouse over this icon to see the image

The Define Text Query dialog box displays.

Move your mouse over this icon to see the image

 

7.

Oracle Reports provides three default formats for text files: Apache Log File, Example Variable Width Comma Delimited, and Example Fixed Width Space Delimited. For this report, your data source will follow the standard Apache logging specifications.

Ensure that Apache Log File is selected in the as the format of the text file in the Data Definition section of the Define Query Text dialog box.

Move your mouse over this icon to see the image

In the Data Source section, you specify the location of the Apache log file that contains the data you will use in your report. Click the Browse button to locate the file in your working directory: access_log.log. Select the file by clicking Open in the Open dialog box.

Move your mouse over this icon to see the image

Click OK.

Now that you have defined the text query, the Data Source definition page of the Report Wizard shows a text summary of the query definition generated by the PDS and reflects the data definition, data source file name, and the available columns.

Move your mouse over this icon to see the image

 

8. You now need to decide on the fields you want to display in the report. From the Available Fields list, select Remote_Host and click the [>] button. Repeat this process for Remote_User, time, and bytes fields.

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

Move your mouse over this icon to see the image

Click Next.

 

9.

You won't calculate totals for this report, so click Next to advance to the label and width panel of the wizard.

The wizard now allows you to modify the default labels and widths. For this tutorial, keep the defaults as they are.

Move your mouse over this icon to see the image

Click Next.

 

10.

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.

Move your mouse over this icon to see the image

 

11.

Save your report as WebServerReport_<your initials>.jsp by selecting File > Save As from the menu.

Move your mouse over this icon to see the image

Click Save.

Back to Topic List

 

Creating a Report Using a Custom File Format

In this topic, you create a report using a custom text file as the data source. The file contains information about various cities and their populations. Earlier in this tutorial, you learned that Oracle Reports provides three default formats for the text PDS. If the text file that you want to use as your data source does not adhere to one of these formats, you must define a new file format in the text PDS configuration file. Once you have added this format information to the configuration file, Reports Builder can then recognize your custom text file as a valid format.

Defining a custom file format
Creating a report using a custom text file as the data source

Back to Topic List

Defining a Custom File Format

Before you can use a custom text file as a data source, you need to define a new file format in the text PDS configuration file, textpds.conf.

1.

Locate the text PDS configuration file: ORACLE_HOME\reports\conf\textpds.conf.

Note: ORACLE_HOME is the directory in which Reports Builder is installed on your computer.

Open the file using a text editor, such as Notepad or WordPad.

Move your mouse over this icon to see the image


2.

Scroll down towards the end of the file and locate the text PDS file formats that Reports Builder provides by default, that is, Apache Log File, Example Variable Width Comma Delimited, Example Fixed Width Space Delimited.

Move your mouse over this icon to see the image

Locate the closing text PDS tag: </textPDS>. You will add your custom file format just before this tag.

Move your mouse over this icon to see the image

 

3.

For your convenience, the data definition for your custom file format is provided for you in the file textpds.conf.txt. Open this file from your working directory in another text editor window.

Move your mouse over this icon to see the image

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 text editor window displaying the textpds.conf file and paste the code just before the closing text PDS tag </textPDS> by using Ctrl+V.

Move your mouse over this icon to see the image

Notice the name of the custom format: My Custom Format_MyInitials. Replace MyInitials with your own initials.

Move your mouse over this icon to see the image

Save textpds.conf by selecting File > Save from the menu. Close the text editor window.

 

Back to Topic

Creating a Report Using a Custom Text File as the Data Source

1.

If Reports Builder is running, exit the session. Restart Reports Builder by double-clicking the Reports Builder icon on your desktop. This ensures that the Reports Builder session will be configured with the modified text PDS definitions in the textpds.conf file.

Select the option Use the Report Wizard. Click OK.

Move your mouse over this icon to see the image

Click Next on the Report Wizard welcome page.

 

2.

The default option is Create both Web and Paper Layout. Click Next to accept this option.

Move your mouse over this icon to see the image

 

3.

On the next page of the Report Wizard, keep the default style as Tabular and enter Cities and Populations as the title.

Move your mouse over this icon to see the image

Click Next.

 

4.

For the data source, select Text Query and click Next.

Move your mouse over this icon to see the image

 

5.

On the data source definition page, click Query Definition to display the query editor for the text PDS.

Move your mouse over this icon to see the image

 

6.

In the Data Definition section of the query editor, select your custom format from the drop down list of available formats for a text file. This is the custom file format you defined previously.

Move your mouse over this icon to see the image

In the Data Source section of the query editor, specify the location of the file that contains the data for your report. The data for this report is contained in the file cities.txt. Click the Browse button to locate this file in your working directory. Select the file by clicking Open in the Open dialog box.

Move your mouse over this icon to see the image

Click OK.

Now that you have defined the text query, the Data Source definition page of the Report Wizard shows a text summary of the query definition generated by the PDS and reflects the data definition, data source file name, and the available columns.

Move your mouse over this icon to see the image

Click Next.

 

7.

You now need to decide on the fields you want to display in the report. From the Available Fields list, select STATE and click the [>] button. Repeat this process for CITY and POPULATION fields.

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

Move your mouse over this icon to see the image

 

8.

You won't calculate totals for this report, so click Next to advance to the label and width panel of the wizard.

The wizard now allows you to modify the default labels and widths. For this tutorial, keep the defaults as they are.

Move your mouse over this icon to see the image

Click Next.

 

9.

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.

Move your mouse over this icon to see the image

 

10.

Save your report as CitiesReport_<your initials>.jsp by selecting File > Save As from the menu.

Move your mouse over this icon to see the image

Click Save.

 

Back to Topic

 

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

Move your mouse over this icon to see the image

Select File > Generate to File > PDF from the menu. Save your report output as CitiesReport_<your initials>.pdf, such as CitiesReport_nhg.pdf.

Move your mouse over this icon to see the image

Click Save.

 

2.

Do the same for your Web Server report.

Open WebServerReport_<your initials>.jsp in Reports Builder. You should find the file in the c:\ReportsOBEs\UsingTextPDS\files folder.

Select WEBSERVERREPORT_<your initials> in the Object Navigator.

Move your mouse over this icon to see the image

Select File > Generate to File > PDF from the menu. Save your report output as WebServerReport_<your initials>.pdf, such as WebServerReport_nhg.pdf.

Move your mouse over this icon to see the image

Click Save.

 

Back to Topic List

In this lesson, you've learned how to:

Back to Topic List

Back to Topic List

Place the cursor over this icon to hide all screenshots.