How to use FO Designer in Oracle Application Express

• Introduction
• Requirements
• Installation of the J4L FOP print server
• Example step by step
  • Create the table
  • Create a Report Query
  • Create the XSL-FO file using FO-Designer
  • Load the XSL-FO file into Oracle Application Express
  • Run the report query in Oracle Application Express

Introduction

Oracle application express can create a PDF file for your forms and reports with just a few clicks, it allows you to configure the columns and some aspect of the columns in the report, like color, fonts etc ... however you cannot modify the layout of the report. A more flexible and powerful solution is to create a XSL-FO file that defines the format of the PDF files, however this can be very tedious even if you learn the XSL-FO language.

J4L FO Designer is a visual tool that can create XSL-FO files you can upload to Apex and use in your applications. The benefit of using FO Designer are:

• No need to learn XSL-FO.
• High productivity by using a visual tool.
• Much more powerful and flexible than the Oracle Apex default layouts.

The procedure executed by Apex is:

1. The data to be printed is converted to XML format
2. the data is passed to the print server together with the XSL-FO file (known as layout in Apex)
3. the print server creates a PDF file using the previous files as input. 

Requirements

1. Oracle Apex has been installed (we used version 3.2.1)
2. A print server for Oracle Apex has been installed. Several possibilities are available:
   • Oracle application server (OC4J) with deployed file apex\utilities\fop\fop.war (included in Apex distribution)
   • or Tomcat with deployed file cocoon.war (we tested version 2.1.10). 
   • or Tomcat with deployed file J4LFOPServer.war This is the option we used for our tests because it is easy to install and a light-weighted solution.
3. The print server has been configured in Oracle Apex. 
   
   

Installation of the J4L FOP print server

You can use the J4L FOP server as print server for Oracle Application Express. The benefits are:

• it is included in the FO Designer license
• it is very easy to install
• it has a small footprint

This is however NOT a requirement. You can use Coccon, OC4J or any other PDF print server supported by Oracle Apex.

The process for the installation is:

1. Download Tomcat 6 and install it
2. Tomcat runs on port 8080, if that port is already used by Apex or another application you can change the Tomcat port in the tomcat\conf\server.xml file.
3. Download J4LFOPServer.war and copy it to the <tomcatdirectory>/webapps
4. Start Tomcat with <tomcatdirectory>/bin/startup.bat
5. Login to Apex workspace INTERNAL as ADMIN user, select Manage Service -> Instance setttings -> Report printing and enter the information of your print server. In the screenshot you can see the setup (note we use port 8081 port tomcat).

Example step by step

Create the table

For our example we have installed the table TASKS contained in the file tasks.txt. The content of this file is:

Project	Task Name	Start Date	End Date	Status	Assigned To
Maintain Support Systems	HR software needs upgrade	01-Dec-2004	14-Jan-2005	open	Pam King
Maintain Support Systems	Apply Billing System updates	15-Dec-2004	22-Dec-2004	open	Russ Sanders
Maintain Support Systems	Investigate new Virus Protection software	15-Dec-2004	28-Jan-2005	open	Pam King
Maintain Support Systems	Arrange for holiday coverage	01-Nov-2004	15-Nov-2004	closed	Al Bines
Email Integration	Complete plan	15-Nov-2004	30-Nov-2004	closed	Mark Nile
Email Integration	Check software licenses	01-Dec-2004	15-Dec-2004	open	Mark Nile
Email Integration	Get RFPs for new server	01-Dec-2004	30-Dec-2004	open	Mark Nile
Email Integration	Purchase backup server	17-Jan-2005	21-Jan-2005	open	Al Bines
Employee Satisfaction Survey	Complete questionaire	19-Jan-2005	28-Jan-2005	open	Irene Jones
Employee Satisfaction Survey	Review with legal	31-Jan-2005	04-Feb-2005	open	Irene Jones
Employee Satisfaction Survey	Plan rollout schedule	17-Jan-2005	21-Jan-2005	open	Irene Jones
Public Website	Determine host server	15-Nov-2004	30-Nov-2004	closed	Tom Suess
Public Website	Check software licenses	15-Nov-2004	30-Nov-2004	closed	Tom Suess
Public Website	"Purchase additional software licenses, if needed"	30-Nov-2004	10-Dec-2004	on-hold	Al Bines
Public Website	Develop web pages	15-Nov-2004	15-Dec-2004	open	Scott Tiger
Public Website	Plan rollout schedule	15-Nov-2004	03-Dec-2004	closed	Tom Suess

1. Select from the Apex main menu Utilities-> Data load, unload
2. Select "Load Data from spreadsheet"
3. Select new table and upload file, click next
4. Select file tasks.txt and enter \t in the separator field, click next
5. Enter table name as TASKS, click next
6. click load data

Now we have a table we want to generate a report for.

Create a Report Query

Before we can create a report in the FO Designer we need 2 items:

• the schema of the xml data to be printed.
• and an example xml file with sample data for testing.

this can be done by creating a report query in Apex:

1. from the main menu select Shared Components -> Reports -> Reports Queries
2. select create
3. enter report query name Tasks_Query, click next
4. Enter SQL query "select * from tasks", click next
5. Select XML data checkbox and click download to save the xml data file tasks_query.xml  in your local drive
6. Select XML Schema checkbox and click download to save the xml schema file tasks_query.xsd  in your local drive
7. click "create report query" and finish.

Create the XSL-FO file using FO-Designer

Now you can start J4L FO Designer and design your report:

1. start FO Designer and
2. load the XML schema  file (select ROWSET as root element)
3. load the XML data file
4. design your report and save it to
5. export the XSL-FO file from the FO Designer

We have already done all this for you, in the FODesignerApexSample.zip file you can find the following 4 files:

1. tasks.xsd:schema file (xsd file)
2. tasks.xml:data file for testing in FO Designer (xml file)
3. tasks.xrp:FO designer report (xrp file). You can load this file into the FO designer and click on the PDF button for testing.
4. tasks.fo: the FO-XSL file exported from FO-Designer. This is the file you have to upload to Apex.

Load the XSL-FO file into Oracle Application Express

Now we will upload the XSL-FO file to be able to use it in Oracle Application Express.

1. In Apex main menu select Shared Components -> Reports -> Reports layouts
2. Click create
3. Select layout type Named columns XSL-FO, click next
4. Enter layout name TasksLayout and select the file tasks.fo, create layout

Now we assign the new layout to our report query:

1. In Apex main menu select Shared Components -> Reports -> Reports queries
2. double click our query Tasks_Query
3. in the report layout field select  TasksLayout
4. click apply changes

As this point you can click on the "test report" button and the PDF report will be displayed. In the next section we will integrate this operation in a html page using a button.

Run the report query in Oracle Application Express

In your Apex application:

1. select create page and select  blank page , enter a name for the page and click next until the page is created.
2. edit the created page and add a new region
3. select HTML as region type, click next
4. Enter a title for the region and click create
5. add a button to the page, click next, accepting the default values until the following screen (note the "download printable report query" action):
   
6. click next accepting the default values until the "report query" field is displayed, in this field you select the query "Tasks_query". Click create button.
7. Now you can run the page. The button will be shown, if you click on the button the query "Tasks_query" will be executed and the PDF file displayed.

Do not hesitate to contact us if you have questions about the usage of J4L FO Designer in Oracle Application Express.