45 Building a Report with a Text Pluggable Data Source

In this chapter, you will learn about reports that include data from a Text pluggable data source (PDS), which is provided with Oracle Reports. By following the steps in this chapter, you can generate the report output shown in Figure 45-1.

Figure 45-1 Report output using a Text PDS

Description of "Figure 45-1 Report output using a Text PDS"

Concepts

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, such as character-delimited text. 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 multidimensional database to compare trends and performance.
Oracle Reports enables you to leverage capabilities, such as aggregation, summarization, formatting, and scheduling, on data from any data source. You can leverage the pluggable data source (PDS) architecture to connect to your own data source, as well as to the data sources available with Oracle Reports (XML, JDBC, Text, and OLAP).
For more information on pluggable data sources, refer to the Oracle Reports online Help. If your data source cannot use an existing PDS, the PDS API enables you to write your own PDS and incorporate it into Oracle Reports to access your own unique data sources. The API is documented in Oracle Reports Java API Reference, available on the Oracle Technology Network Oracle Reports Documentation page ().

Example Scenario

In this example, you have downloaded the US Census Bureau data in CSV (comma-separated values) format and want to generate a report. You will learn how to use character-delimited text as a data source to create a paper report.

As you build this example report, you will:

Set up the textpds.conf file to Configure Reports Builder to recognize your text file as a pluggable data source.
Use the Report Wizard to create a report based on the text data source.

To see a sample report that uses a Text PDS, open the examples folder named TextPDS, then open result\censusreport.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 45-1.

Table 45-1 Example report files

File	Description
`Examples\TextPDS\result\censusreport.rdf`	The finished RDF for your report.
`Examples\TextPDS\result\censusreport.pdf`	A PDF version of your finished report.
`Examples\TextPDS\scripts\census_csv.txt.`	The character-delimited data downloaded from the US Census Bureau Web site.
`Examples\TextPDS\scripts\config.txt`	Code for the `TextPDS.conf` file.

45.1 Prerequisites for this example

To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above).

45.2 Set up the textpds.conf file

Before you can use a text file as a data source, you must set up the Text PDS configuration file (textpds.conf) with your text file's format information. With this data definition in the configuration file, Reports Builder can recognize your text file as a valid data source.

Note:

You must edit your configuration file before you launch or restart Reports Builder for the changes to take effect.

To set up your textpds.conf:

In a text editor, such as Notepad, open the file textpds.conf, located in the ORACLE_HOME/reports/conf directory.

Note:
ORACLE_HOME is where Reports Builder is installed on your computer.
In another text editor window, open the config.txt file we have provided in the Examples/TextPDS/Scripts directory.

Copy the text we have provided in config.txt and paste it into textpds.conf before the </textPDS> entry, so that the resulting textpds.conf file contains an entry like this:

Figure 45-2 Snapshot of the textpds.conf entry

Description of "Figure 45-2 Snapshot of the textpds.conf entry"

Note:

The fileFormat entry in textpds.conf enables Reports Builder to recognize your text file as a data source. When you choose Text PDS in the Report Wizard, the defined format (CensusCSV, in this example) displays as an option. In the fileFormat entry, you also define the properties of each column in the file.

Save the textpds.conf file.

You have set up your textpds.conf file for the character-delimited text data source.

45.3 Use the Report Wizard to create a report

When you create a report, you can either use the Report Wizard to assist you or create the report yourself. To build the simple report in this example, you can use the Report Wizard.

Before you use a Text pluggable data source, you might want to examine the text file first to see what it looks like. You can open the census_csv.txt file in a text editor, like Notepad or WordPad, to see the data we will be using in this example.

To create a simple report:

Now that you have updated the textpds.conf file, launch Reports Builder.

Tip:

If Reports Builder was already open when you modified the textpds.conf file, you must shut down Reports Builder and restart it for Reports Builder to recognize your text file as a valid data source.

In the Welcome dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create Paper Layout only, then click Next.
On the Style page, type a title for your report and select Group Above, then click Next.
On the Data Source page, click Text Query, then click Next.
On the Data page, click Query Definition to display the Define Text Query dialog box.

If you get an error message and cannot display the Define Text Query dialog box, check your configuration file (textpds.conf) to confirm the code you added.
Under Data Definition, click the CensusCSV format in the list.

Note:
The CensusCSV format displays in this list because you added it to the textpds.conf file.
Under Data Source, click Browse to find the census_csv.txt file we provided in the Examples/TextPDS/Scripts directory.

If you do not see the file listed in your directory, make sure you have selected TXT files in the Files of Type list in the Open dialog box.
Click the file, then click Open.
When the census_csv.txt file displays in the Location field, click OK.

In the Report Wizard, the data source definition displays in the Data Source definition field.
Click Next.
On the Groups page click CATEGORY in the Available Fields list and click the right arrow (>) to move this field to the Group Fields list, then click Next.
On the Fields page, click the double right arrows (>>) to move all available fields to the Displayed Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, click Next.
On the Template page, select Predefined Template and click Beige, then click Finish to display your report output in the Paper Design view. Make whatever modifications you wish. The report should look like the following image:

Figure 45-3 Paper Design view for the Text PDS report

Description of "Figure 45-3 Paper Design view for the Text PDS report"

Save the report as censusreport_your_initials.rdf.

Note:

You can compare your results against the report we have provided in the examples directory. Before you can run the report, however, follow these steps:

In the Data Model view, right-click the query and choose Data Wizard.
On the Data page, click Query Definition.
Update the location of census_csv.txt to its location on your local drive.

45.4 Summary

Congratulations! You have successfully used a Text pluggable data source for a paper report. You now know how to:

set up your textpds.conf file.
use the Report Wizard to create a paper report based on the text data source.

For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".