Print PDF reports with APEX 5.1 + ORDS

By default, if you use embedded PL/SQL gateway to run your APEX, PDF printing is not possible. There are several ways to make it possible.

• By configuring ORDS and running it in either Standalone mode or into Apache Tomcat / Glassfish / or other web server. This option is completely free and easy to setup.
• By bying Oracle BI Publisher and running it with Oracle Web Logic application server, which is the richest one in functionality, allowing the use of RTF templates, export to PDF, Word, Excel, etc. This option is the most expensive one and is suitable for corporate projects with big demands for customized reports and Word editable templates.
• By Using a third party service, such as APEX Office Print. They offer almost all of the BI Publisher functionalities at a much lower cost.

We have chosen to use APEX + ORDS + Apache Tomcat. This is easy to configure and fast solution if you don’t need complicated PDF output.

Later on HERE we will put the steps to configure all three products.

But now lets focus on something a bit more interesting and really benefitial if you want to change the default PDF print template, add your company logo or create a custom output for some or all reports in your application.

Fist of all, make Oracle REST Data Services your default Print server. Here is a very useful LINK which will guide you in this.

Then you should enable PDF print in you report by going to the Property Editor – Attributes section, and select Yes to Enable Report Printing. For your interactive reports, you should put a check to PDF option in Property Editor – Attributes.

After doing so, you will be able to download the PDF from Actions – Download. By default the PDF looks ugly, but at least you have it there. You can play arond in  Property Editor – Attributes – Printing  and make a bit better by changing the Header, Footer, font size, colours of the table and so on. Usually it will be good enough and most users will be happy with the result.

But if you have a demand for more complicated PDF output, with logo, some other pictures, custom texts here and there, you can still achieve it without spending a single penny. ORDS is good for many reasons, but especially in this task it helps with the ability to read XSL-FO files and produce PDFs out of it. Actually it uses exactly that for the default PDF output aswell.

You have two options. First option is to go to Shared Componentsand create Report Layout and Report Query, which is using the Report Layout you just created. There are two options when you create Report Layout.

Report Layout Type:

Generic Columns (XSL-FO)
Named Columns (XSL-FO)

The first option basically does the same as the default PDF Print. BUT … but, when you select that option, on the next step you can see the XSL-FO source code of the default template. You can then change it, add new stuff there and save it. You can copy it to an external editor, such as Altova and produce a great template to use for your reports.

The second way, a bit more complicated, is to change the default template directly in the database. WARNING! This could break your PDF Printing, so be extremely careful doing it.  So you should have admin rights and access APEX_050000 schema (your APEX database schema that keeps everything, related to the work of APEX). Then find the package wwv_render_report3 and open it for editing. It’s BODY is wrapped, but fortunately, what we need to change is the SPEC of the package. Search for #PAGE_HEADER# or something like that or just scroll down the code to find the template source code. Edit it as you wish and test. If you can produce a PDF reports after your changes then you are good with XML-FO  Go on and experiment. Then test again. Until you are satisfied with the result.

Now that we have a new template, let’s see how we can manipulate the Header, Footer and Body of the PDF document. Here is a script that you can apply to any Report and change the default values for the above sections :

set define off
UPDATE APEX_050000.WWV_FLOW_PAGE_PLUGS
SET PRN_UNITS = ‘MILLIMETERS’,
PRN_PAPER_SIZE = ‘A4’,
PRN_WIDTH_UNITS = NULL,
PRN_WIDTH = 297,
PRN_HEIGHT = 210,
PRN_ORIENTATION = ‘HORIZONTAL’,

PRN_PAGE_HEADER = PLUG_NAME,
PRN_PAGE_HEADER_ALIGNMENT = ‘CENTER’,
PRN_PAGE_HEADER_FONT_COLOR = ‘#4F81BD’,
PRN_PAGE_HEADER_FONT_FAMILY = ‘Helvetica’,
PRN_PAGE_HEADER_FONT_WEIGHT = ‘bold’,
PRN_PAGE_HEADER_FONT_SIZE = 12,

PRN_PAGE_FOOTER = ‘Experian ‘||TO_CHAR(SYSDATE,’YYYY’),
PRN_PAGE_FOOTER_ALIGNMENT = ‘LEFT’,
PRN_PAGE_FOOTER_FONT_COLOR = ‘#4F81BD’,
PRN_PAGE_FOOTER_FONT_FAMILY = ‘Helvetica’,
PRN_PAGE_FOOTER_FONT_WEIGHT = ‘normal’,
PRN_PAGE_FOOTER_FONT_SIZE = 10,

PRN_HEADER_BG_COLOR = ‘#E9EDF4’,
PRN_HEADER_FONT_COLOR = ‘#000000’,
PRN_HEADER_FONT_FAMILY = ‘Courier’,
PRN_HEADER_FONT_WEIGHT = ‘normal’,
PRN_HEADER_FONT_SIZE = 8,

PRN_BODY_BG_COLOR = ‘#FFFFFF’,
PRN_BODY_FONT_COLOR = ‘#000000’,
PRN_BODY_FONT_FAMILY = ‘Helvetica’,
PRN_BODY_FONT_WEIGHT = ‘normal’,
PRN_BODY_FONT_SIZE = 7,

PRN_BORDER_COLOR = NULL,
PRN_BORDER_WIDTH = 0.5

WHERE ID = 2661340678921742628;

To get the ID of your WWV_FLOW_PAGE_PLUG, you can make a select like this one:

select * from APEX_050000.WWV_FLOW_PAGE_PLUGS
WHERE PRN_PAGE_HEADER_FONT_SIZE IS NOT NULL
AND Page_Id = 1;

In this case Page_Id = 1 is the page, where your Report region is located.

If you want to update all your reports with these settings, use the below UPDATE statement:

set define off
UPDATE APEX_050000.WWV_FLOW_PAGE_PLUGS
SET PRN_UNITS = ‘MILLIMETERS’,
PRN_PAPER_SIZE = ‘A4’,
PRN_WIDTH_UNITS = NULL,
PRN_WIDTH = 297,
PRN_HEIGHT = 210,
PRN_ORIENTATION = ‘HORIZONTAL’,

PRN_PAGE_HEADER = PLUG_NAME,
PRN_PAGE_HEADER_ALIGNMENT = ‘CENTER’,
PRN_PAGE_HEADER_FONT_COLOR = ‘#4F81BD’,
PRN_PAGE_HEADER_FONT_FAMILY = ‘Helvetica’,
PRN_PAGE_HEADER_FONT_WEIGHT = ‘bold’,
PRN_PAGE_HEADER_FONT_SIZE = 12,

PRN_PAGE_FOOTER = ‘Experian ‘||TO_CHAR(SYSDATE,’YYYY’),
PRN_PAGE_FOOTER_ALIGNMENT = ‘LEFT’,
PRN_PAGE_FOOTER_FONT_COLOR = ‘#4F81BD’,
PRN_PAGE_FOOTER_FONT_FAMILY = ‘Helvetica’,
PRN_PAGE_FOOTER_FONT_WEIGHT = ‘normal’,
PRN_PAGE_FOOTER_FONT_SIZE = 10,

PRN_HEADER_BG_COLOR = ‘#E9EDF4’,
PRN_HEADER_FONT_COLOR = ‘#000000’,
PRN_HEADER_FONT_FAMILY = ‘Courier’,
PRN_HEADER_FONT_WEIGHT = ‘normal’,
PRN_HEADER_FONT_SIZE = 8,

PRN_BODY_BG_COLOR = ‘#FFFFFF’,
PRN_BODY_FONT_COLOR = ‘#000000’,
PRN_BODY_FONT_FAMILY = ‘Helvetica’,
PRN_BODY_FONT_WEIGHT = ‘normal’,
PRN_BODY_FONT_SIZE = 7,

PRN_BORDER_COLOR = NULL,
PRN_BORDER_WIDTH = 0.5

WHERE PRN_PAGE_HEADER_FONT_SIZE IS NOT NULL
AND FLOW_ID = 100; — This is the App number