Connecting to MySQL from BI Publisher

Since BI Publisher just uses a JDBC connection you can of course configure it connect to a MySQL database! I'm using v5.1 of the MySQL Connector/J and BI Publisher is running on Apache 5.5. Here's the instructions:

1. Download the latest MySQL JDBC driver, which can be found here
2. Copy the mysql-connector-java-5.1.7-bin.jar JAR file to $CATALINA_HOME/shared/lib directory
3. Restart you application server (Tomcat in this case)
4. Connect to BI Publisher and click on the Admin tab and then click on the JDBC Connection hyperlink
5. Select the Add Data Source button
6. The connection String should be as follows:

jdbc:mysql://<server>:<port>/<database name>

7. The database driver class should be:

com.mysql.jdbc.Driver

8. Enter the other required information such as username, password and then click on Test Connection.

BI Publisher API jar files

If you want to use BI Publisher Java API then you do not need to install BI Publisher server, you only need to include the following JAR files within you application:

xdocore.jar - the core BIP/XMLP library
xdoparser.jar - this is the scalable XML parser and XSLT 2.0 engine
xmlparserv2.jar (10.1.3.4) or xmlparserv2-904.jar (< 10.1.3.4) - the main XML parser/XSLT engine
aolj.jar - this is an Oracle EBS library, we need it whether you're developing in EBS or not
i18nAPI_v3.jar - this is the i18n library used for localization functions
collections.jar - you only need this if you are working with the delivery APIs or bursting engine.

Optional JARs for Charting Support
======================
bipres.jar - charting library
bicmn.jar - charting library
jewt.jar - charting support library
share.jar - charting support library

These JARs can all be found in the WEB-INF/lib directory or the xmlpserver.war (which is located in manual/generic from the installation zip archive).

Dates and Report Parameters

When configuring a Date Parameter in BI Publisher the Date Format String has to conform to the Java SimpleDateFormat format mask. Some commonly used examples are:

Format Mask	Output
dd-MMM-yyyy	01-Jan-2008
dd/MM/yyyy HH:mm:ss	01/01/2008 13:25:00
hh:mm a	01:25 PM
h:mm a	1:25 PM
EEE dd-MM-yy	Tue 01-01-08
EEEE dd-MM-yy	Tuesday 01-01-08

Default values for Dates
This can either be:
• A fixed date and must match the format mask specified in Date Format String
• One of the following functions:

Function Name	Description
SYSDATE()	Today’s Date
FIRST_DAY_OF_MONTH()	First day of the current Month
LAST_DAY_OF_MONTH()	Last day of the current Month
FIRST_DAY_OF_YEAR()	First day of the current Year
LAST_DAY_OF_YEAR()	Last day of the current Year

When using the functions above the they need to be surrounded by a curly brace followed by a dollar sign i.e. {$FIRST_DAY_OF_MONTH()$}
Simple addition and subtraction is also supported with these functions, for example:
{$SYSDATE()+1$} would specify tomorrows date as the default parameter.

Rendering Error: Unexpected element: :report

Ever seen the error "Unexpected element: :report" when trying to render a report in BI Publisher? This is due a a Bug/Feature where the "xmlns" attribute is not automatically inserted into the element in the xdo file for your report. To resolve the issue is quite simple.

1. Open the xdo file for you report in a text editor
2. Insert the following attribute into the element

xmlns="http://xmlns.oracle.com/oxp/xmlp"

The element should then look something like this:

<report version="1.1" xmlns="http://xmlns.oracle.com/oxp/xmlp" defaultDataSourceRef="dev">
...
</report>