| Developer: Oracle E-Business Suite
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.
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:
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.
This section explains the technical setup to successfully run 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.
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.
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.)
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:
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.
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.
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.)
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.)
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.)
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.)
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.
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
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.
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
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.