Thursday, July 31, 2008

Rendering Oracle HTML DB Reports as PDFs Using Formatting Objects Processor

Process Overview :
The process of rendering Oracle HTML DB report output to PDF requires a few different pieces to be in place:

Oracle HTML DB 1.6 - This version of HTML DB is required to generate report output in an XML format that can be understood by FOP.
Java Platform - an environment in which a JavaServer Page (JSP) can perform an XML/XSL transformations.
JavaScript - on the page producing the Oracle HTML DB report, a certain amount of JavaScript is required to pass the report output over to the JSP.
Extensible Stylesheet Language Transformation (XSLT) - an XSLT document is required by the JSP to transform the report in XML format to PDF.
Process Example

To illustrate this concept, we will add an "Output to PDF" link to the template of an HTML DB region that contains a report. When users click on this link, a new browser window will pop up containing the PDF version of the same report output. Although we will be using a simple query on the HR.EMPLOYEES table, the formatting code provided with this Technical Note is not query-specific and should work with any queries that you create. The first few steps of this example are performed outside the Oracle HTML DB environment to put the various supporting pieces in place.
Step 1: Add FOP Libraries to the Java Environment

In order to render the received XML report output into a specific format (PDF in this case), your Java environment needs access to the FOP libraries. In this example we will use Oracle Application Server Containers for J2EE (OC4J) 10g for that environment, so the libraries will be added to $ORACLE_HOME/oc4j/j2ee/home/config/application.xml.

Download and unzip the FOP libraries from http://xml.apache.org/fop/
Place the unzipped fop-0.20.5 directory in $ORACLE_HOME/oc4j/j2ee
Include the following lines in $ORACLE_HOME/oc4j/j2ee/home/config/application.xml:




Step 2: Add the Rendering Components Specific to Oracle HTML DB to the Java Environment
Two files specific to this Oracle HTML DB PDF rendering process are required: htmldb_fop_render.jsp and htmldb_example.xslt. Simply put, htmldb_fop_render.jsp receives the XML report output that is POSTed by the JavaScript soon to be added below and performs an XML/XSL transformation using htmldb_example.xslt and the FOP libraries. Although these two files can be placed elsewhere, putting them both in $ORACLE_HOME/oc4j/j2ee/home/default-web-app is easiest for now.

Step 3a: Create an Oracle HTML DB Report

Create a report on scalar data types that will be used for this example. As mentioned previously, the formatting code added above isn't report specific, so any report of scalar datatypes will do.

Log into your Oracle HTML DB workspace.
Go to the Application Builder home by clicking the name of an application to which you wish to add this example report.
Click Create Page.
Select Page with Component and click Next.
Select Report and click Next.
Select SQL Report and click Next.
Finish the report wizard as desired while providing a query to be used. The simple query below would suffice for these purposes:
select first_name "First", last_name "Last", email "Email ID",
job_id "Job", hire_date "Date of Hire", salary "Compensation"
from hr.employees

Please note that if your HTML DB application schema does not have access to the HR schema, simply use another query for this example.
Step 3b: Add a Page-Level Item and Branch to Oracle HTML DB Report
To illustrate the ability to reference page-level items from the XSLT used in this transformation, add an item and a branch to return users to the current page.

From the Page Definition view of the newly created page, click the Create icon in the Items section.
Select Text and click Next.
Select Text Field (always submits page when Enter pressed) and click Next.
Name the item "MY_REPORT_PARAMETER" and click Next.
Provide a Label value of "My Report Parameter" and click Next.
Accept the remaining defaults in the Create Page Item wizard and return to the Page Definition view.
Click the Create icon in the Branches section.
Accept all the defaults of the Create Branch wizard while providing &APP_PAGE_ID. for the target page.
Click Create Branch.
Lastly, include MY_REPORT_PARAMETER in your report region by changing its query to:
select first_name "First", last_name "Last", email "Email ID",
job_id "Job", hire_date "Date of Hire", salary "Compensation"
from hr.employees
where salary > :MY_REPORT_PARAMETER or :MY_REPORT_PARAMETER IS null

Step 4: Add PDF Link to Report Screen

From the Page Definition view of your report page, click the name of the region template displayed in the Templates section of the Shared Components column.
Add the link definition below somewhere to the Template field of this screen. Placing the link immediately before the #CLOSE# substitution string tends to render nicely.
Output to PDF Document
Click Apply Changes.
Step 5: Modify and Upload the JavaScript File
Download this JavaScript file and change the path shown in this line:
var g_Render_URL = 'http://:/htmldb_fop_render.jsp';

to match the location of the JSP you added in Step 2, above. For example:
var g_Render_URL = 'http://myhostname.com:8888/htmldb_fop_render.jsp';

Go back into Oracle HTML DB and click the Application Builder tab at the top of the screen.
Click the Shared Components icon.
Click the Static Files link in the Files section.
Click Create.
Click Browse to navigate to your modified htmldb_pdf.js file.
Click Upload.
Step 6: Reference the JavaScript Functions From Your Report Page
Return to the Page Definition view of the page containing your report region.
Call the uploaded JavaScript from the header of this page by adding the following line to the "HTML Header" field:


Click Apply Changes.
Step 7: Run the Report Page and Click the "Output to PDF Document" Link
Summary

This Technical Note has offered a simple example of using Oracle HTML DB 1.6 and a Java environment to render HTML DB report output as a PDF document. The process illustrated can be summarized as follows:
HTML DB generates HTML report output in an HTML DB application page.
End users click a link on the page.
The link calls JavaScript that tells the HTML DB engine to send the report output to a JSP in a specific XML format.
The JSP performs an XML/XSL transformation using the provided XSLT and the FOP libraries.
FOP renders the document as PDF.

Using this concept, Oracle HTML DB developers can now use FOP to control all aspects of generated report output down to the layout within specific formats such as PDF, PCL, PS, SVG, Print, AWT, MIF and TXT.
Additional Resources & Information

The example shown above is a simple one in which HTML DB report output is sent to a specific PDF format. The layout of the rendered PDF document is controlled almost entirely by the htmldb_example.xslt stylesheet. Developers wishing to customize this resulting output can do so by making changes to that htmldb_example.xslt file. To learn more about transformation using such XSLT stylesheets, see the following resources:

Search 4 DataStage