What’s new in the BI Publisher Toolbox
The toolbox is back and packed with more features! Every wanted to convert a report6i report, build a Burst control file or test the Delivery APIs? You now can do that with the Toolbox. Haven’t heard about the toolbox, click here.
Besides, the new tools, the toolbox is also faster, easier to use and has a new look and feel. Below is a list of the all the new features.
New features and updates
- Performance
- Cancel Long Running Tasks
- Parameters
- Global Properties
- Ad Hoc PL/SQL
- File Browser(s)
- Connection Screens
- XDO Protocol Support
- Tools:
*Bursting DesignerInstallation
*Delivery Designer
*Report
BI Publisher Toolbox - By clicking on the download link you will be redirected to a different site. The standalone client is no longer supported and the toolbox is now a java webstart application. Webstart applications allow for automatic updates and real-time fixes for developers. Don’t worry though just because it’s a webstart application doesn’t mean the performance will lack luster; the jar files are automatically loaded and saved on your PC. It's recommended to bookmark this page and the download site.
BI Publisher Toolbox 4.2
Performance
All of the performance issues in previous version of the toolbox have now been resolved. As an example, the previous version of the toolbox would perform poorly when clicking the execute button. That issue is now resolved and the performance is significantly better.
Long Running Tasks
Sometimes when you miss a join in a query a data template can run forever. Now, when clicking the execute button there is an option now to kill long running tasks. In the previous version the only option was to kill the program through windows task manager, which is less than ideal.
Parameters
One of the best improvements in the toolbox is the parameters screen. In the past, scrolling was required to enter parameters and it was a bit cumbersome (See Figure 1), now it's very intuitive. With one click of a button a parameters screen will popup (See Figure 2).
Now, if you can't remember what parameters are in your Data Template the toolbox has you covered. Click the look up button, the toolbox will automatically parse the Data Template and provide a list of available parameters to select.
Also, there is no longer a limit on the number of parameters that can be entered!
Figure 1 Old version of the parameters for the toolbox.
Figure 2 New parameters form with Data Template parameter lookup feature
Global Properties
All of the standard and non-standard features of the template manager are now available in the toolbox (See Figure 3). As an example if you wanted to set the system-temp-dir or password protect a pdf you now can!
Figure 3 Global properties tab
Ad Hoc PL/SQL
Developer can stage data, initialize sessions, etc using the “Add Hoc PL/SQL” feature. This feature will execute pl/sql statements before a Data Template executes. The purpose of the ad-hoc pl/sql is to limit hard-coding in your data template. In the E-business suite as example, most views are org secured views. With very little effort a developer can use this new feature to execute apps_initialize before your data template queries are run.
Figure 4 Ad Hoc PL/SQL - Example of apps initialize
File Browser for Data Templates, Format Templates and Burst Control Files
A file browser has now been incorporated into several fields for locating Data Templates, Format Templates and Burst control files. It’s not mandatory to use the new file browser and an absolute path to your files can be manually entered. Also, Data Templates, Format Templates and output files can be in separate directories, which was a restriction in the first release of the toolbox.
XDO Protocol Support
The toolbox also supports the XDO Protocol. To use this new feature a DBC file must be assigned. Go to the Action menu and select “Set DBC File.” The DBC file will be preloaded the next time you open the Toolbox or the Bursting designer.
By setting the DBC file developers can run format templates that have import statements in them and prevents errors from occurring. This is something that the Template Builder in msword cannot currently do. As an example in your format template if your doing and import like this: import:xdo://ONT.ONTBSAERTF.en.00/ that should work in the toolbox.
Not only is the XDO Protocol supported for import in format templates, its also supported in the toolbox itself. There is no longer a requirement to have a format template physically located on a PC. An XDO URL can be entered directly into the format template field. The toolbox will pull the format template from the database using the assigned DBC file. For the URL don’t put getSource=true. ie: xdo://ONT.ONTBSAERTF.en.00/?getSource=true
Connection creation and removal
The previous version of the toolbox was very difficult to update or remove connections and it usually required manually editing of the toolbox config. Going forward, the connection portion of the application has also been completely redone. Creating or removing a connection couldn't be easier (See Figure 5 & 6).
*Connections 10g databases are supported, 9i and earlier versions are not.
Figure 5 New connection setup screen
Figure 6 Edit/Remove connection screen
Tools
One of the new features of the toolbox is external applications integration (See Figure 1). The tools menu has a list of applications that can be run:
1. Bursting
2. Delivery Designer
3. Report Conversion
Before running one of these applications be sure to click the save button or your toolbox changes will be lost.
Figure 7 tools menu
Bursting Designer 3.7 XDO Protocol Supported
About the Bursting Designer
The Bursting Designer allows developers of any skill level to create, run and test Burst control files. The tool provides the following benefits:
- Preview a burst control file as it's being built (See Figure 8.)
- Test the burst control file (See Figure 9.)
- Test XDO Delivery configurations
- Onscreen real-time logfile (See Figure 10.)
- Review/Analyze your Bursting delivery channels
- XDO Protocol Support
- XDO Delivery Configurations (xdodelivery.cfg) & XDO_TOP support
- Authentication support (email, printer, fax)
- Printer Delivery Properties
- Data Expressions (implicitly supported)
- Template File Browser
- File History
- Improved UI (Quick Keys & Menus)
- Fixed - Crash when opening test file
Figure 8: Preview a control file
Figure 9: Request tree – Hierarchal view of request and their related deliveries. Note: Double click the link to view the file.
Figure 10: Real-time log file
How to build a control file using the Bursting Designer
Below is a list of video tutorials for the Bursting Designer. The tutorials explain how Bursting works and how to create a Burst Control file in the Bursting Designer.
Bursting Designer Lesson 1 - What is Bursting
Bursting Designer Lesson 2 - Creating a Burst Control File
The basic steps to create a control file are:
Step 1: Request setup
Enter an xpath expression for the XML you would like to be burst. Multiple requests can be added and resorted (it’s drag and drop).
Step 2: Create a delivery.
When creating a delivery your using oracle’s document delivery APIs . The following Deliveries are supported:
- File
- FTP
- Webdav
- Printer
- Fax
General rules:
- Multiple deliveries can be created for request
- Deliveries do not have to be assigned, but unassigned deliveries will not be used by the bursting engine
- Deliveries are not global available across multiple requests, a delivery is request specific.
- For file output, make sure the file name is unique. In most instances you would want to use the same value in the KEY field (see document tab).
Step 3: Assign a delivery to a document.
All the fields on this tab are required. One or more deliveries can be assigned to one document row. The Bursting Designer will allow for more than one document to be created, however, Oracles bursting engine does not support this. When choosing a key make sure the value is unique. Real world examples would be invoice number, transaction number, etc.
Step 4: Assign a template.
All the fields on this tab are required except for the filter field. The location to a format template is the absolute path. You may hand-key the path or use the template file browser button. Another option besides local format template is to use the XDO Protocol (see in section below).
One or more templates can be assigned to a document row. However, if there are multiple template rows the filter field is required (you will not be prompted though). Assign multiple templates with caution. If a filter doesn’t get applied, the last template row will be applied to the output. An alternative to filters would be to create multiple requests with predicates for each template. Neither way is a best practice and is purely a personal choice.
Previewing a control file
At any point in the development process a control file can be previewed. To preview the control file go to the Actions menu and choose “View Control File.”
Run a control file
Before running a control file be sure to save it first. To run a control file go to the Actions menu and choose “Run Control File.” You will be prompted to select a data file. Once a data file is selected it will be cached permanently unless you decided otherwise.
XDO Protocol Support
XDO Protocol support allows a control file to be seamless loaded into the Bursting Designer from the E-Business Suite. The syntax to import a format template is as follows: xdo://<appname>.<templatecode>.<language>.<territory>/?getSource=true
Example: xdo://ONT.ONTBSAERTF.en.00/?getSource=true
In order to use this new feature a DBC file must be loaded. To load a DBC file, go to the Actions menu and select “Set DBC File.” The DBC file will be preloaded the next time you open the Bursting designer. When you load a DBC file the designer will test it to see if a connection can be created. If a connection cannot be created then the likely cause is a bad DBC file. The typical reasons for bad DBC file are it’s dated or the JDBC connection needs to be manually edited. To fix the JDBC connection string, remove any slashes (\), save and retry the “Set DBC File” action.
XDO Delivery configuration support
Perhaps you didn't know this but Oracle's Bursting engine can use XDO Delivery configurations (xdodelivery.cfg). Setting the server-alias will utilize the configurations in a xdodelivery.cfg file. The sever-alias field is available on the following deliveries: Email, Print, Fax.
To load a use a XDO Delivery Configuration file, go to the Actions menu and chose set XDO_TOP(see below). XDO_TOP should be the path to the resource directory. The location will be preloaded the next time you open the Bursting designer.
Why use an XDO Delivery configuration file?
Simplified administration is the key benefit of using xdodelivery.cfg. However, there are some other advantages. As an example, what if a printer can only accept postscript output. What’s a programmer to do? BIP & Bursting only support pdf, so I guess were out of options or are we? The filter option in an XDO Delivery configuration would allow a developer to automatically convert a pdf to post script. Something like this should do the trick: filter="pdftops {infile} {outfile}." They’re many more features an XDO Delivery Configuration file can provide. For more information on how to setup one up click here.
Authentication support
The Bursting designer now supports authentication nodes under specific delivery types. To use authentication set the user name and password fields on the following tabs: email, print and fax.
Printer Delivery Properties
Below is a list of the enhanced printer options that are undocumented by Oracle (at least for Bursting). The following attributes are available on the Printer delivery tab:
1. attributes-charset
2. attributes-natural-language
3. document-format
4. job-name
5. media *this is the tray option!
6. orientation-requested
7. requesting-user-name
8. sides
Data Expressions
Data expressions are implicitly supported for e-mail, ftp and webdav. Oracles Bursting Engine uses the Delivery Manager APIs, so by default that functionality works for Bursting. For more information on data expressions see Tim Dexters Blog.
Example:
<xapi:ftp id="ftp" server="localhost" user="hidden" password="hidden" directory="/Users/ike/ " file="ftp_${TRX_NUMBER}_%y.%m.%d.%S" ftp="false">
File History
Another major time saving benefit of the Bursting Designer is file history support.
Every time a control file is opened, file history is created. Under the file menu, the last 5 five control files opened (see Figure 1) will be available.
Also, when a data file (Action Menu --> Run Control File) is selected to run against a control file, the data file will be cached and will pre-populate next time the Bursting Designer is opened.
Figure 11 File History
Crash when opening test file
This was the number one issue that developers had with the Bursting Designer. The reason for the crash was because of an invalid path to a format template. There is a bug in one BIP classes that Oracle is working on. With that said, I'm overriding oracles class with my own in order to fix the issue.
Delivery Designer 1.1
If you ever wanted to test the Document Delivery APIs or a delivery configuration this tool is for you. The Delivery Designer allows a developer to test the all of the Delivery APIs without writing any code.
The Delivery Designer’s use is not strictly related to testing how the Delivery APIs work, it also allows a developer to troubleshoot Bursting delivery problems. As an example, when trying to troubleshoot a Burst control file, it can be difficult to identify a misconfigured printer. In this situation the designer will allow repeated, lightweight troubleshooting of a printer issue without the overhead of running the Bursting Engine.
It’s also never been easier to test an XDO Delivery configuration! The Delivery Designer not only allows XDO Delivery configuration testing, it also can generate a sample XDO delivery configuration.
The Designer supports all of Oracles Documents delivery channels. Below is a list of the Delivery types that are supported:
- Printer
- Fax
- Webdav
- Ftp
- Sftp
- Http
- AS2
- External Command
- File
There is an abundance of properties that can be set for a particular delivery channel. The Delivery Designer needs the literal value to be entered for delivery channels. It’s because of this fact the every property in the Delivery Designer has note in the info column. The purpose of the note is not only for informational purposes but what values can be entered. As an example, the IPP_AUTHTYPE (Figure 12) shows the valid values that can be entered are none, basic or digest.
Figure 12 IPP_AUTHTYPE printer delivery attribute and related info
Setting up XDO Delivery Configurations
There is only step to utilizing an XDO Delivery configuration. Navigate to the global property tab, select property called XDO_TOP, add the path to XDO_TOP. Be sure to create a directory called resource and place your xdodelivery.cfg document in that directory.
To use a server defined in an XDO Delivery configuration enter the server name in the property called “SERVER_NAME”. The SERVER_NAME property is available for most delivery types.
XDO Delivery Configuration file generation
An XDO Delivery Configuration can be generated at any point in time by clicking the delivery config button. The Delivery Designer will take the current properties for a delivery and generate the XDO Delivery Configuration. The deliveries that can automatically be generated are IPP, SMTP, Webdav, FTP, SFTP & External Command.
Figure 13 Sample XDO Delivery Configuration file generation
Real-time log file
When a Delivery Request is submitted, instant feedback is provided about the delivery request on the log screen. The log screen is continually updated till the Oracle DeliveryResponseListener returns the final status of the delivery.
The Delivery Designer also supports undocumented properties that manipulate Oracles Delivery Reponse Listener. Changing these properties will determine how and when a delivery times out. Below is a list of properties that are available for every delivery.
- ASYNC_TIMEOUT - Sets the timeout period for the delivery
- ASYNC_CHECK_INTERVAL- Set this property to change the minimum wait time before refreshing the log screen
- RETRY - Set this property to change the number of times to re-attempt sending this delivery
- RETRY_INTERVAL- Set this property to change the minimum wait time before re-attempting to send this delivery
Figure 14 Real-time log file
Report Conversion
It's never been easier to convert a reports6i report to BI Publisher using the toolbox. Reports can be converted in mere seconds. To get an idea of how easy it is click here to watch the demonstration video.
Prerequisites
Forms and reports 10g locally must be installed. The report6i files must be converted to an xml format. To convert reports6i report, use the rwconverter.exe. A dos batch or shell script can be written to accomplish a batch conversion task (See Figure 15)
REM WINDOWS CONVERTING REPORT
::report_batch_converter.bat
cls
Echo compiling Report .....
for %%f IN (%1*.rdf) do rwconverter.exe batch=yes source=%%f dest=%%f.xml dtype=xmlfile overwrite=yes logfile=%1reports.log
Echo FINISHED CONVERTING REPORT
Figure 15 Sample batch script for converting a reports6i report to an xml format
Conversion Steps
Step 1: First part of conversion is converting your reports6i report to xml. To do this use rwconverter.exe or create a batch script (see above, Figure 1)
Step 2: The next step is to take the xml files and convert them to bi publisher files. In order to do this we need to run the ReportConversion.exe.
Step 3: Click on the folder icon to select reports6i the xml files. You may optional change the format template and the log file name after the files are loaded.
Step 4: The final step is to click the process button.
Navigate to your conversion directory. The following should be present:
1. Format Template
2. Data Template
3. Related PL/SQL Packages
Other Known Issues
| Issue | Resolution/Workaround |
| Application starts to load but fails | Java 6 is required to run this application. |
| JNLP file does not execute in browser | Follow these instructions |
| Application runs in Java 1.5 but none of the cell browsers work. | Java 6 is required to run this application. |
| : XSL-1031: (Error) Variable not defined: '_XDONFSEPARATORS'. | Import statements in format template will on only work for R12 or 6241631 patchsets |
| Can’t find DBC File | DBC files can be found under $FND_TOP/secure. |
| Sftp & AS2 – Fail for Delivery Designer | There is no workaround for this. The delivery designer is coded to execute these classes when there available from oracle |
| Cannot connect to database for Data Templates | Verify your connection is setup correctly. Tnsping |
| There is no option to specify a custom xdodelivery.cfg file for bursting. | Future release will allow ad/hoc xdodelivery.cfg files outside of $XDO_TOP |
| There is no standalone version of the application (executable) | The BI Publisher Toolbox is a webstart application. This allows for developers to run the most current version of the application automatically. |
| XDO Loader Features are missing | XDO Loader features are being redeveloped. |
| After keying data in a field, it disappears when I click save or a menu item. | Be sure to always tab out of each field before performing a save action. |
| The report6i xml file is malformed or does not convert at all. | Identify that 10g version rwconverter.exe is being used to convert the rdf to xml. It’s not uncommon that 6i version of rwconverter.exe gets used instead. Goto My Computer System Properties Environment Variables. Modify the path variable to point at the 10g directory or Manually run the rwconverter.exe |
In this post you learned about the new features and updates to the BI Publisher ToolBox Development Suite. 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.
