42
Building a Report with a Text Pluggable Data Source

Figure 42-1 Report output using a text PDS

Text description of the illustration orbrtext_fin.gif

Reports Builder enables you to use any data source you wish. In this chapter, you will learn how to use character-delimited text as a data source.

About Pluggable Data Sources

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 XML, OLAP, and the like. 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 PDS (pluggable data source) architecture to connect to your own data source, as well as to the data sources available with Oracle Reports (XML, JDBC, text, and Express).

For more information on pluggable data sources, refer to the Reports Builder online help and the Javadoc documentation for the PDS APIs.

Example Scenario

Suppose you downloaded the US Census Bureau data in CSV (comma-separated values) format and want to generate a report. In this example, you will create a paper report that queries this data.

Table 42-1 Features demonstrated in this example

Feature	Location
Configure Reports Builder to recognize your text file as a pluggable data source.	Section 42.2, "Set up the textpds.conf file"
Use the Report Wizard to create an paper report based on the text data source.	Section 42.3, "Use the Report Wizard to create a report"

42.1 Prerequisites for this example

To build the examples in this manual, you must have the example files we've provided. If you haven't already done so, you can download the files you'll need to complete this example from the Oracle Technology network (http://otn.oracle.com/products/reports/) and install them on your machine.

To download and install the example files:

1. Go to the Oracle Technology Network Web site (http://otn.oracle.com/products/reports/).

2. Click Getting Started with Oracle Reports.

3. Click Index, then find the "Building a Report Using a Text Pluggable Data Source" example.

4. Download the file textpds.zip into a temporary directory on your machine (e.g., d:\temp).

5. Unzip the contents of the file, maintaining the directory structure, into an examples directory on your machine (e.g., d:\orawin90\examples).

   This zip file contains the following files:

   Table 42-2 Files necessary for building this Text PDS example

   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.

42.2 Set up the textpds.conf file

Before you can use a text file as your pluggable data source, you must set up the text PDS configuration file (textpds.conf) with the definition of the values in the text file. When you add this format information to the configuration file, Reports Builder can then recognize your entries as a valid format.

Note: You must edit your configuration file before you launch Reports Builder in order for the changes to take effect.

To set up your textpds.conf:

1. In a text editor, such as UltraEdit, 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.

2. In another text editor window, open the config.txt file we've provided in the Examples/TextPDS/Scripts directory.

3. Cut the text we've 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 42-2 Snapshot of the textpds.conf entry

Text description of the illustration orbrtext_conf.gif

Note: This entry enables Reports Builder to recognize a text file as a PDS. When you choose your PDS in the Report Wizard, the text file displays as an option. Here, you also define the properties of each column in the file

4. Save the textpds.conf file.

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

42.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.conf file in a text editor, like UltraEdit or WordPad, to see the data we'll be using in this example.

To create a simple report:

1. Now that you've updated the textpds.conf file, launch Reports Builder.

   Tip: If Reports Builder was already open when you modified the textpds.conf file, you should shut down Reports Builder and relaunch it.

2. In the New Report dialog box, select Use the Report Wizard, then click OK.

3. If the Welcome page displays, click Next.

4. On the Report Type page, select Create Paper Layout Only, then click Next.

5. On the Style page, type a title for your report and select Group Above, then click Next.

6. On the Data Source page, click Text Query, then click Next.

7. On the Data page, click Query Definition.

   If you get an error message and cannot display the Query Definition dialog box, check your configuration file (textpds.conf) to confirm the code you added.

8. Under Data Definition, click the CensusCSV format in the drop-down list.

   Note: The CensusCSV format displays in this list because you added it to the textpds.conf file.

9. 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've selected TXT from the drop-down list.

10. Click the file, then click Open.

11. 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.

12. Click Next.

13. 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.

14. On the Fields page, click the double right arrows (>>) to move all available fields to the Displayed Fields list, then click Next.

15. On the Totals page, click Next.

16. On the Labels page, click Next.

17. 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 42-3 Paper Design view for the text PDS report

Text description of the illustration orbrtext_fin.gif

18. Save the report.

    Note: You can compare your results against the report we've 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.

42.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 Reports Builder Online Help, which you can access in two ways:

• From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

• From Reports Builder, choose Help > Help Contents.

  Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.