Developer: Oracle E-Business Suite
   DOWNLOAD
 Oracle BI Publisher
   TAGS
bipublisher, bi, All

Getting Started with BI Publisher ToolBox


by Ike Wiggins

Learn how this free utility can make running BI Publisher reporting components incredibly easy.

Published October 2009

Ever wished that using the Oracle BI Publisher APIs could be easier to use? Ever wanted a single-click template load application for EBS Template Builder? Ever wanted to be able to test your reports with a single click? Look no further! These features and many more are provided in the BI Publisher ToolBox, a free configuration management tool I created for Oracle BI Publisher .

For those of you unfamiliar with it, Oracle BI Publisher is a tool that generates high-fidelity documents and reports. Also, it provides a rich set of APIs that can generate and deliver content to a variety of platforms ubiquitously.

Figure 1a
Figure 1 Oracle BI Publisher overview

ToolBox increases the speed of development because a developer can now connect to any environment, any disparate data source, or any ERP system. This functionality has the added benefit of not requiring you to create or key any system config, copy files to servers, or wait for concurrent manager requests (a plus for E-Business Suite developers).

Not only will ToolBox make your life easier, it will also save you valuable development time—a lot of it. To illustrate this point, below is an example of how a typical BI Publisher report is developed and unit tested for Oracle E-Business Suite developers:

  1. Copy the Reports6i report to the server or upload a Data Template: 5 seconds
  2. Upload the Format Template (assuming you are logged-in already): 5 seconds
  3. Copy a concurrent request and resubmit: 10 seconds
  4. Wait for the concurrent request to start and finish: 30 seconds
  5. Review the log and output file: 10 seconds
  6. Repeat
With all those non-value added steps, you will waste over a minute clicking, keying, copying files and waiting. With ToolBox, you will just click one button. The report will run in real-time, the log file will be displayed on the screen automatically, and the output will go to the configured directory. ToolBox can do the same work in half the time.

What Is It?

ToolBox is a lightweight Swing app that allows developers to run the BI Publisher APIs locally on their PC with a nice, front-end GUI. (See Figure 2.) There is nothing to deploy and no OAF or Java knowledge is required. As a result, it allows a developer to quickly create and test BI Publisher Data Templates, Format Templates, Burst Control Files, etc.

Figure 1
Figure 2 Sample configuration to run BI Publisher templates

The most important aspect of ToolBox is its ease of use. A novice BI Publisher developer can easily set up the necessary configurations to run any type of BI Publisher report. Below is a list of the core functionality ToolBox provides:
  • Format Templates - rtf conversion, applying xsl templates to xml files
  • Data Templates - generating xml files (Reports6i replacement)
  • ETEXT - Generating EFT and EDI documents
  • Bursting - Delivery documents via http, email, ftp, filesystem, etc.
  • Uploading Format Templates (E-Business Suite)
  • Uploading Data Templates (E-Business Suite)

Installation

This section explains the technical setup to successfully run BI Publisher ToolBox.

  1. Install the Java runtime environment (JRE) version 6. The download for the JRE version 6 can be found here.
  2. Verify that your PC has 500+ MB of memory. (Note: 1 GB is preferable)
  3. Download BIPublisherToolBox.zip
  4. Download BIPublisherToolBoxSrc.zip (optional)
  5. Extract the BI Publisher ToolBox.

ToolBox can be extracted into any directory. DO NOT move the executable, Batch File, or Shell Script to another directory. You may not copy the executable out of the extracted directory, either. In doing so, the jar files and their paths will not be found for this application. With that in mind, you may move the entire extracted folder (BIPublisherToolBox) to your choice of locations.

Examples

ToolBox comes pre-packaged with basic examples that a developer can run. In order to run the Data Template examples, the scott/tiger schema must be installed and you must have a working data source. It is recommended that the developer load the examples before proceeding to the "Configuration" section.

  1. Run the executable - Navigate to the BIPublisherToolBox\bin directory. Double click on the BIPublisherToolBox.exe. The ToolBox screen should come up.
  2. Open the BIPublisherToolBox_SampleConfig.xml file - Click the Open Folder button, navigate to the BIPublisherToolBox\corporate_examples directory. Double click on the BIPublisherToolBox_SampleConfig.xml. The ToolBox screen should now be populated with the examples. (See Figure 3.)

    Figure 3
    Figure 3 Loaded BIPublisherToolBox_SampleConfig.xml file

    Overview of each configuration line:

    Line 1: Bursting with a Data Template generating the xml
    Line 2: RTF template working with data generated from a Data Template
    Line 3: RTF Template working with a previously generated xml file
    Line 4: Data Template (Stand alone) generating xml
    Line 5: ETEXT document with a previously generated xml file
    Line 6: Bursting a document with an ETEXT format. Note that the output format is ignored. It is just there for clarity.

Configuration


Step 1: Running the executable

Navigate to the BIPublisherToolBox\bin directory. Double click on BIPublisherToolBox.exe. The ToolBox screen should come up.

Before running ToolBox, the first step is to set up the data sources. To configure the data sources, simply key in the required information for the data source and then click the Add button. All the fields are required and there is no validation for the data source setup. If information is entered incorrectly, resolve this by creating a new file or edit the existing xml configuration file. (See Figure 4.)

Figure 4
Figure 4 Configuring and adding a data source

After clicking the Add button, the data source will be available to run against any report. When creating a new data source for the first time, you will be prompted to create and save a configuration file (if you have not already done so). Also, when initially opening the configuration file, the data source combo-box should auto-populate.

There is an outstanding issue with the drop-down not refreshing after adding a data source. To resolve this issue, close the application and re-open your configuration file. There is no limit to the number of data sources that can be saved or created.

Note: This application has not been tested against non-Oracle databases, so it is unlikely that a SQL Server and DB2 data sources will run without the right jdbc drivers.

Step 2: Overview of Required Fields

There are very few steps needed to run BI Publisher components such as Data Templates, Format Templates, and Bursting. This can be done without creating or updating any new configuration in the e-Business Suite or other relevant systems; the template configuration allows for all variants of BI Publisher technologies. There are hard-coded business rules that enforce the process execution. Below is a listing of the required fields. (Note: There are tool tips for every field. The tool tips display a brief message stating what should be entered into the highlighted field. )

The following fields are required for all configurations:

  • Template Type
  • Output Format (N/A for bursting)
  • Output Directory
  • Output File

General rules:

  • Never leave blank spaces in the Output directory path to your output files.
  • The Output directory field should end with a slash ( \ ).
  • Never leave spaces in the Format and Data Templates names.
  • The Output directory must contain the Format and Data Templates. There are no multiple directory configurations. Do not append file paths to these files (Data/Format Templates). It will not work.
  • Never have file extensions appended on the Output file. The extensions will be ignored or appended with relevant extensions.
  • To save you must either click the disk or hit CTRL-S (click out of the field for CTRL-S)

Step 3: Creating/Removing a configuration row

New Button - In order to create a new configuration row, the New button must be clicked. Defaults are initially loaded. Clicking New will always create a new row at the bottom of the table and, in some instances, you may need to scroll down to see it.

Duplicate Button - Clicking the Duplicate button will duplicate the current row selected. You may need to scroll down to see the new row at the bottom of the table.

Delete Button - Clicking the Delete button will remove the existing row. Clicking the Save button will make the row permanent.

Clear Log Button - Clicking the Clear Log button will clear the output console. This does not clear the existing log file and it will be redisplayed. (If you know how to solve this, please email me at the address in my bio. This is a known issue.)

Step 4: Configuring Templates

An assumption is made in the following sections that the developer is trying to figure out what the possible configuration combinations are after selecting the Template Type. The selected Template Type has very specific and applicable business rules for the execution of configuration line. Below is an overview of the Template Type field and configuration row.

Template Types

The second step of setting up your report is selecting the applicable Template Type. There are three options to select: RTF, BURST, or ETEXT. There is a business rule that is common across all Template Types, so whenever a Data Template is available, that will be the source generating the xml output file. Figure 5 shows an overview of what happens with each Template Type.

Figure 5
Figure 5 The template types that can be run are RTF, BURST, ETEXT. There is no type for Data Templates, any can be selected.

RTF Template (Format Template)

If a Data Template is provided, the ToolBox will generate the specified output file. If a Data Template is not provided, it is assumed the output xml file exists. The ToolBox will call the RTFProcessor & FOProcessor API and process the output file. (See Figure 6.)

Figure 6
Figure 6 Highlighted line is a sample configuration of RTF - Format Template

Data Template

A maximum of 20 parameters can be configured for Data Templates. (This is a limitation of ToolBox, not BI Publisher.) If the Format Template field is blank, the xml output file will only be created by calling the DataProcessor API. If a Format Template is provided, ToolBox will generate the specified output file and apply the Format Template using the RTFProcessor & FOProcessor API (see RTF Template). (See Figure 7.)

Figure 7
Figure 7 Highlighted line is a sample configuration of a Data Template. The Template Type has no bearing on the processing because a Format Template or Bursting file is not provided.

Bursting Template (Format Templates)

If a Data Template is provided, ToolBox will generate the specified output file using the Data Template. If a Data Template is not provided, it is assumed that the output xml file exists. ToolBox will call the BI Publisher DocumentProcessor API and process the output file. (See Figure 7.) The Format Template field should be populated with the bursting configuration file. (Note: Do not append an xml file extension to the output file; it will cause the ToolBox bursting engine to error out.)

Figure 8
Figure 8 Highlighted line is a sample configuration of Bursting.

ETEXT Template

If a Data Template is provided, the ToolBox will generate the specified output file using the Data Template. If a Data Template is not provided it's assumed the output xml file exists, ToolBox will call the EFTProcessor API and process the output file. ETEXT must be the selected output format and the file extension will be always be .txt. (See Figure 9.)

Figure 9
Figure 9 Highlighted line is a sample configuration of ETEXT.

Step 5: Executing the Configuration

To execute the selected configuration row (blue row), click the Execute button. Be sure to save before executing the configuration (by clicking the disk icon).

Step 6: Review Error Messages

The logging feature of the BI Publisher APIs is very helpful in troubleshooting template issues. ToolBox captures and redirects all system messages and displays them in the output console. (See Figure 10.) The output console messages are redirected from an output log file (errors.txt). ToolBox MUST run in its existing directory structure, otherwise the log file will not be able to be parsed and displayed.

Figure 10
Figure 10 Sample error message log

Step 7: Upload your templates (Optional)

As I mentioned previously, ToolBox allows E-Business Suite developers to upload their Data Templates and Format Templates into the database without logging into the e-Business Suite. (See Figure 11.) The following fields are required for template upload configurations.

Upload Lob Code
Upload Short Name

Figure 11
Figure 11 Highlighted line is a sample configuration of Format Template/Data Template upload.

Note: If you are wondering how to find the lob_code for your Data Template and Format Template, you may use the following query:

select * from xdo_lobs where lob_code like :1

Uploading Data Templates

Clicking the Upload Data Template button will upload the Data Template in the database. By default only [00 - 00] is supported.

Uploading Format Templates

Clicking the Upload Format Template button will upload the Format Template in the database. Only en-US is supported for US templates. If there is a need for multi-locale template upload configurations, it can be coded. As for now, the current coding scheme will support a majority of needs.

Troubleshooting

Configuration File

If the configuration file cannot be loaded, then it is likely corrupted. The file is in xml format and can be manipulated easily. If the xml document is invalid, the first approach would be to create a back-up and try to repair it. If you cannot repair it, then a new configuration must be created. If the issue occurs infrequently, maintain a backup. Keep in mind that there is no limit to the number of configuration files that can be saved and stored.

Other Known Issues


Issue

Workaround

Configuration file fails to load

Attempt to repair the file.

After adding a Data Source the combo-box does not refresh.

Close the application and re-open. (Note: Regardless of the combo-box populating, ToolBox will take what is populated in the data source row when clicking the Execute button.)

Cannot remove a Data Source or no Data Source removal button.

There is no button to remove a data source. The workaround is to edit the Data Source in the configuration file.

Cannot upload my Format or Data template.

There are a couple of potential reasons for this to occur. See the Uploading Data Templates/Format Templates section above.


Conclusion

In this article you learned how to setup, install, and run the BI Publisher ToolBox. The ability to run BI Publisher reporting components has never been simpler. With ToolBox, the majority of your development can now be done locally on your PC.


Ike Wiggins [ http://bipublisher.blogspot.com] is an Oracle Applications Developer out of Minneapolis, Minn. Ike has been developing BI Publisher reports since 2004 and been developing free BI Publisher developer tools sets since 2006. He is also a BI Publisher Blogger and has been an active member in the BI Publisher Development Forum.