Integrating BI Publisher standalone with ADF 10g/11g

Posted by HusainD



As we know BI Publisher is one of the best reporting tools available in the market. It is integrated with E-business suite (using OA Framework) and PeopleSoft products hence creating data for reports easy. However ADF 11g does not come with an integrated BI Publisher support. 

OBIEE (Oracle BI Enterprise) provides different ways to call reports from external applications including ADF. This blog by Venkatakrishnan J explains it thoroughly: http://oraclebizint.wordpress.com/2008/01/28/oracle-bi-ee-101332-integration-into-external-applicationsportals/
However my client has an application in ADF and needed an approach where existing pages can call reports using existing queries written in View Objects.

The idea here is to make the integration generic enough so that any page can use it to generate report.

The sample application can be downloaded from here

Step 1: Environment setup

    1. The following solution is applicable for both ADF 10g and 11g. I am using ADF 11g installed from http://download.oracle.com/otn/java/jdeveloper/111/jdevstudio11111install.exe
    2. Install BI Publisher Desktop from http://download.oracle.com/otn/nt/ias/101341/bipublisher_desktop_windows_x86_101341.zip
    3. Install Oracle XE and configure HR schema. Download Oracle XE from http://download.oracle.com/otn/nt/oracle10g/xe/10201/OracleXE.exe
    4. Create a new application "Fusion Web Application (ADF)" Call it "BIIntegrationApp"
    5. Copy the jar files from \BI Publisher Desktop\Template Builder for Word\jlib into \BIPIntegrationApp\Model\lib folder. Following the the most important files needed :
                  · xdocore.jar
                  · xmlparserv2-904.jar
                  · collections.jar
                  · i18nAPI_v3.jar
                  · versioninfo.jar

        Step 2: Model Project

  • Right click the “Model” project and go to "Project Properties" > “Libraries and Classpath”. Click on “Add Jar/Directory” and add all the jar files from Model/lib folder .

  • Create a new Database connection to HR schema of Oracle XE db.


  • Create Business Components using “Business Components from Tables”
Select "Employees" and "Departments" tables for creating Entity Objects. Also create default view objects and Default Application Module.
Delete the View Links DeptMgrFkLink and EmpManagerFkLink since we wont be using them. We need a simple relation between Department and Employee in our report.


  • Next we create a layer of ADF extension classes to put generic code used for report generation. Since the XML data is generated from VO, lets extend the ViewObjectImpl.
Create a new Java class “model.base.BaseViewObjectImpl” which extends “oracle.jbo.server.ViewObjectImpl”


  • In the BaseViewObjectImpl we will add at least 2 methods getXMLData() and getReport(). {Other overridden methods can be added to pass different parameters}

getXMLData() will generate XML representation of the View Object Data


public byte[] getXMLData() {

byte[] dataBytes = null;
try {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
//The following statement will generate xml representation of the view data.
//the first param of writeXML takes the number of levels of child nodes to consider.
//the entire xml is then printed into the outputStream.
((XMLNode)writeXML(4, XMLInterface.XML_OPT_ALL_ROWS)).print(outputStream);

//Simple and Dirty way to generate sample XML data is to print the outputStream to log
//Then copy the data into xml file.
//However it will not be possible to generate large xml file in this way
System.out.println(outputStream.toString());

//The reason we convert to byte[] is that outputStream is not serializable
//while byte[] is serializable and can be used in client class when needed.
dataBytes = outputStream.toByteArray();
} catch (IOException e) {
System.out.println(e.getMessage());
} catch (Exception e) {
System.out.println(e.getMessage());
}

return dataBytes;
}
  • getReport() is the main method that takes the xml data and template file from parameter and converts it into the report of desired format.
Although I am passing the template path and filename, the logic can be modified to fetch the template from database. (that will be in some other post.)


/**
getReport method obtains the xml data of the view object and combines it with the template passed to it.
It then converts the report in the desired format and returns the byte[] array.
*
The reason byte[] is passed instead of OutputStream is that OutputStream is not serializable.
*
@param templateFile: File Name with Path of the RTF template file.
@param outFileType: eg HTML, PDF, RTF
*/
public byte[] getReport(String templateFile, String outFileType) {

byte[] dataBytes = null;

try {
//Process RTF template to convert to XSL-FO format
RTFProcessor rtfp = new RTFProcessor(templateFile);
ByteArrayOutputStream xslOutStream = new ByteArrayOutputStream();
rtfp.setOutput(xslOutStream);
rtfp.process();

//Use XSL Template and Data from the VO to generate report and return the OutputStream of report
ByteArrayInputStream xslInStream = new ByteArrayInputStream(xslOutStream.toByteArray());

FOProcessor processor = new FOProcessor();
ByteArrayInputStream dataStream = new ByteArrayInputStream(getXMLData());
processor.setData(dataStream);
processor.setTemplate(xslInStream);

ByteArrayOutputStream pdfOutStream = new ByteArrayOutputStream();
processor.setOutput(pdfOutStream);
byte outFileTypeByte = FOProcessor.FORMAT_PDF;
if ("HTML".equalsIgnoreCase(outFileType)) {
outFileTypeByte = FOProcessor.FORMAT_HTML;
}
processor.setOutputFormat(outFileTypeByte); //FOProcessor.FORMAT_HTML
processor.generate();

dataBytes = pdfOutStream.toByteArray();

} catch (IOException e) {
e.printStackTrace();
System.out.println("IOException when generating pdf "+ e);

} catch (XDOException e) {
e.printStackTrace();
System.out.println("XDOException when generating pdf "+ e);

}
return dataBytes;
}


  • Next we will inherit these methods in DepartmentView by extending BaseViewObjectImpl.
Open the DepartmentsView (View Object), Select the Java tab. Select Generate View Object Class. Also select “Extends button” and extend Object from “BaseViewObjectImpl”

  • In the “DepartmentsViewImpl” add the following code. It will use the getReport() from base class. This code is not necessary if we plan to pass templatePath from UI directly.
public byte[] getReport(String format) {
//Some complex logic to derive the template path based on language, customer type etc..
String templatePath = "C:\tmp\template1.rtf";
return getReport( templatePath, format);
}


  • Now create a client interface for DepartmentsView and shuttle in getReport(String). Only this method will be visible in Data Control later.

  • Compile the model project. Resolve compilation errors if any.
  • At this point its a good idea to run the Application Module and run the method getReport() to check if it generates the report. We will be able to download the report later once UI project is complete.




        Step 3: ViewController Project.

  • Create a new JSF Page. Name the page DepartmentEmployees.jspx. Do NOT create backing bean, we will create a managed bean.

  • Select the getReport(String) method from Data Controls Panel > DepartmentsView1 . Drag it and drop on the page as ADF Parameter Form.

It will create a Textbox that is bound to the parameter of the method. The value entered in the textbox will be passed to getReport() method.


  • You will see it generates a textbox and a button “getReport”. If you run the page and press the button, it will execute the method in VO but not display the report result.
  • For the textbox set the AutoSubmit property to 'true'. Ideally this is not needed but since we will add a file download listener, it wont set the values when button is clicked.
  • In order to download the report, Select “File Download Action Listener” from Component Palette and drop on the “getReport button”.

  • Select the “File Download Action Listener” and in the Property Inspector select the “Edit” next to “Method” property. It will ask to create a new Managed Bean and Handler Method.
{Note that we can give ContentType and Filename here if it is constant. Even if we do not give these it will work fine except the report will open in the same page instead of giving a download dialog.}

  • Click on the New.. next to Managed Bean Dropdown and create a new java class “view.report.ReportDownloader”
Then create a new Method “handleDeptReportDwd” and press Ok.

  • Open the ReportDownloader.java and add the following code. It is a generic code (Thanks to Steve Muench) that calls a method from data binding and returns it result.

/**
This example illustrates does not specifically set any
method action parameter values, so the parameter values
are picked up from the EL expressions configured on the
method action binding arguments in the page definition
metadata.
http://blogs.oracle.com/smuenchadf/examples/#152
*/
public Object executeMethodWithResult(String methodActionId) {
BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();
OperationBinding operationBinding = bindings.getOperationBinding(methodActionId);
Object result = operationBinding.execute();

if (!operationBinding.getErrors().isEmpty()) {
throw new RuntimeException("Error while executing.. "+operationBinding.getErrors());
}
return result;
}


  • Add the following code to handleDeptReportDwd(). It simply calls the above util method and passes getReport which is id of methodAction tag in DepartmentEmployeesPageDef.xml

public void handleDeptReportDwd(FacesContext facesContext, OutputStream outputStream) {
try {
Object result = executeMethodWithResult("getReport");
outputStream.write((byte[])result);
} catch (IOException e) {
System.out.println("Exception "+e);
}
}


        Step 4: BI Publisher Template

  • The Only reason we didn’t create it earlier was because we needed to get the data xml. And now we can get the data xml by running the DepartmentEmployees page.
  • To get the xml, go to the DepartmentsView and open its client Interface and shuttle in getXMLData method.

  • Next open DepartmentEmployees page and drop in the getXMLData() from DataControl and drop it as button. Run the page.


  • Since we put the System.out.println() in getXMLData() method, when we execute that method from the page, it will print the xml data in log file. (If your xml file is big, add a “File Download Action Listener” to this button and write another method to download)
  • Copy the xml data and save it in a file depts.xml.
  • Create a new RTF template with the depts.xml and save as DeptTemplate.rtf
Step 5: Run the application
  • The only thing left is to change the path of template file in DepartmentsViewImpl.java and run the page.
  • Run the page, enter "pdf" in the textbox and press getReport button to get the report.
 

  •  Enter "HTML" in textbox and click getReport to receive report in HTML format.




Hope this helps :)


 

Labels: ,

9 comments:

it is a great post, but i've a problem.

when i put the setActionListener, the 'format' parameter is lost. I debugged the application and, when arrives to DepartmentsViewImpl, function getReport, the parameter format is always null.

I think the problem its arround method executeMethodWithResult... or something about binding the format parameter.

Thanks!

Carlos Marcos said...

November 17, 2009 at 4:19:00 AM PST  

it is a great post, but i've a problem.

when i put the setActionListener, the 'format' parameter is lost. I debugged the application and, when arrives to DepartmentsViewImpl, function getReport, the parameter format is always null.

I think the problem its arround method executeMethodWithResult... or something about binding the format parameter.

Thanks!

Carlos Marcos said...

November 17, 2009 at 4:19:00 AM PST  

Hi,

Thanks for identifying the issue. Ideally executeMethod should have taken the textbox value through the page definition. But since we have used file download listener, the textbox value was not set properly.
So the solution is to set the AutoSubmit property of textbox to true.

I have updated the blog and app file.

Thanks,
Husain

HusainD said...

November 17, 2009 at 7:41:00 PM PST  

Its probably because library files of BI Publisher are missing. Add them in the model project of your app.

HusainD said...

December 8, 2009 at 10:20:00 AM PST  

The library files that I added are the following (please let me know what I'm missing):

xmlparser-2.0.0.jar (xmlparserv2-904.jar broke my app)
versioninfo.jar
collections.jar
xdocore.jar
i18nAPI_v3.jar

Thanks,

Matt

Unknown said...

December 8, 2009 at 10:38:00 AM PST  

xmlparserv2-904.jar should ideally work. Not sure about the version xmlparser-2.0.0.jar.
Did you try adding ALL BI Publisher jars into model project?

Are you able to run a BIP report from word directly?

HusainD said...

December 8, 2009 at 11:47:00 AM PST  

Do you want to send across your code to husaindalal@gmail.com?

HusainD said...

December 10, 2009 at 6:54:00 AM PST  

Nice Post Husain

Ike Wiggins said...

December 14, 2009 at 7:49:00 PM PST  

This is a great post. Was very helpfull. Thanks!!!

Unknown said...

April 12, 2010 at 12:57:00 PM PDT  

Post a Comment

Newer Post Older Post Home

Subscribe to: Post Comments (Atom)