Introduction to FO Designer in Oracle Application Express 5

 

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, 4.0 ,4.2 and 5.0)
  2. A print server for Oracle Apex has been installed. Several possibilities are available:
    • J4L FOP Server
    • Oracle Data Rest Services (APEX Listener)
    • 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. 

 

Usage with Oracle REST Data Services

Starting with FO Designer version 1.6.1, the templates generated work also with Oracle Rest Services print server. You can use this option if you use Oracle REST Data Service (ORDS) as web server , either standalone or inside a web server like Glassfish.

In the instance settings of APEX you have to select Oracle REST Data Services as print server.

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
  • it supports additional function, embedded images,, emails and digital signature

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

The fastest way to get a printing server up and running is by installing our Apache FOP Server Bundle. You can install and uninstall with just one cliclk.

If you however want to install on a Tomcat server or on another platform other than Windows,  the installation process 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 8087 port in this example).

 

If you get this error while running a report:

ORA-20001: The printing engine could not be reached because either the URL specified is incorrect or a proxy URL needs to be specified.

This means APEX has no permission to access the FOP server (this is the default setup in Oracle 11g) , please refer to the Oracle documentation for enabling the network services.

Example step by step for APEX 5

Create the table

For our example we have installed the table TASKS contained in the file tasks.txt.

  1. Select from the Apex main menu Utilities-> Data load 

  2. Select "Spreadsheet data"
  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, (select view as inline if you want to display the PDF in the browser), click next


  4. Enter SQL query "select * from tasks", click next

  1. Select XML data checkbox and click download to save the xml data file tasks_query.xml  in your local drive
  2. Select XML Schema checkbox and click download to save the xml schema file tasks_query.xsd  in your local drive
  3. click "create report query" and finish.

    You can now test the report, it will create a report based in the generic format since we did not upload any custom layout yet.

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_utf8.fo (tasks.fo, windows encoded): 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_utf8.fo (use tasks.fo only if the utf8 verison does not work), 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 Static content as region type, click next
  4. Enter a title for the region and click create
  5. add a button to the page, switch to the component view if you are not in it and then edit the button. As Action select Redirect to URL and enter the url of the report  f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=Tasks_Query


  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.