Custom APEX PDF printing. Editing XSL-FO report layouts

As I’ve said before, you don’t need the all-to-expensive BI Publisher, if you need to print good-looking PDF from APEX. Carl Backstrom suggested using Cocoon  as an alternative XSL-FO Processing Engine. Out choice is 100% Oracle and it was detailed in previous articles: OC4J (a robust version of Oracle Application Server) as a printing server which could “read” the XSL-FO that make up the APEX Report Layouts.

The important issue still remains: how do you customize the APEX Report Layouts, without the BI Publisher tools? Well, manually, of course and with lots of patience. Or you can use a tool like ALGEN (ApexLayoutGenerator), which is an APEX application developed by the ApexNinjas.com team to help visually create Report Layouts based on existing Report Queries. About ALGEN and how to use it, I’ll return in a future post.

1. Default PDF printing in APEX

By default, APEX uses a Report Layout that works only with one report query. It is intended for printing of a single Report from within an APEX page and you can do little customization , but it looks plain an dull, like this:

pdf printing apex

You can do some CSS customizing from the “Print Attributes” tab of a Interactive Report region. You can choose a previously created Report Layout, set the page settings, fonts, colors and so on:

apex pdf printing

 

But what if you want to go beyond that, have layouts with multiple reports, pictures and conditional block display? Then, creating your own Report Layouts is the case.

2. Customizing APEX Report Layout

APEX uses a standard Report Layout designed in XSL-FO. I uses only one report query and the structure of the XSL can roughly be divided like this:

Everything before the <xsl:template match=”/”> tag is just for defining attributes, just like in a CSS file.
<fo:layout-master-set> section: defines the page properties
<fo:static-content flow-name=”region-header”> section: defines the header
<fo:static-content flow-name=”region-footer”> section: defines the footer
<fo:flow flow-name=”region-body”> section: defines the body: this is where you can add report queries and customize them.

Inside the body (or “region-body”) the default APEX Layout allows only on table object (or report-query) and it automatically replaces the #…# substitution strings:

<fo:table start-indent=”0.0pt”>
<xsl:variable name=”_XDOFOPOS2″ select=”number(1)”/>
<xsl:variable name=”_XDOFOTOTAL” select=”number(1)”/>
#PRN_TABLE_CELLS#
<fo:table-header>
<fo:table-row>
  #PRN_TEMPLATE_HEADER_ROW#
</fo:table-row>
</fo:table-header>
<fo:table-body>
<xsl:for-each select=”.//ROW“>
<fo:table-row>
#PRN_TEMPLATE_BODY_ROW#
</fo:table-row>
</xsl:for-each>
</fo:table-body>
</fo:table>

If you have a report query with the following queries:

select name, age from table1;
select product, amount from table2;

Your report layout will look like this (the region-body section):

<fo:table start-indent=”0.0pt”>
<xsl:variable name=”_XDOFOPOS2″ select=”number(1)”/>
<xsl:variable name=”_XDOFOTOTAL” select=”number(1)”/>
<fo:table-column column-width=”30mm”/>
                      <fo:table-column column-width=”30mm”/>

<fo:table-header>
<fo:table-row>
  <fo:table-cell xsl:use-attribute-sets=”
border”><fo:block font-size=”10pt”>NAME</fo:block></fo:table-cell>
                           <fo:table-cell xsl:use-attribute-sets=”border”><fo:block font-size=”10pt”>AGE</fo:block></fo:table-cell>
                        </fo:table-row>
</fo:table-header>
<fo:table-body>
<xsl:for-each select=”DATA/ROWSET1/ROWSET1_
ROW“>
<fo:table-row>
<fo:table-cell text-align=”left”>
                              
  <fo:block font-size=”10pt” font-weight=”bold”><xsl:value-of select=”NAME”/></fo:block>
                            </fo:table-cell>
<fo:table-cell text-align=”left”>
                              
  <fo:block font-size=”10pt” font-weight=”bold”><xsl:value-of select=”AGE”/></fo:block>
                            </fo:table-cell>
</fo:table-row>
</xsl:for-each>
</fo:table-body>
</fo:table>

<fo:table start-indent=”0.0pt”>
<xsl:variable name=”_XDOFOPOS2″ select=”number(1)”/>
<xsl:variable name=”_XDOFOTOTAL” select=”number(1)”/>
<fo:table-column column-width=”50mm”/>
                       <fo:table-column column-width=”30mm”/>

<fo:table-header>
<fo:table-row>
  <fo:table-cell xsl:use-attribute-sets=”border”><fo:block font-size=”10pt”>Product</fo:block></fo:table-cell>
                           <fo:table-cell xsl:use-attribute-sets=”border”><fo:block font-size=”10pt”>Amount</fo:block></fo:table-cell>
                        </fo:table-row>
</fo:table-header>
<fo:table-body>
<xsl:for-each select=”DATA/ROWSET2/ROWSET2_
ROW“>
<fo:table-row>
<fo:table-cell text-align=”left”>
                              
  <fo:block font-size=”10pt” font-weight=”bold”><xsl:value-of select=”PRODUCT”/></fo:block>
                            </fo:table-cell>
<fo:table-cell text-align=”left”>
                              
  <fo:block font-size=”10pt” font-weight=”bold”><xsl:value-of select=”AMOUNT”/></fo:block>
                            </fo:table-cell>
</fo:table-row>
</xsl:for-each>
</fo:table-body>
</fo:table>

Basically, for each report query you want to display in the PDF file, you have to replace the:

#PRN_TABLE_CELLS# substitution string with a <fo:table-column  /> tag for each column.

#PRN_TEMPLATE_HEADER_ROW# substitution string with a <fo:table-cell /> tag for each column, which is for the column header name and properties.

#PRN_TEMPLATE_BODY_ROW# substitution string with a <fo:table-cell/> tag for each column, which is for the column values, referenced by their name like this: <xsl:value-of select=”PRODUCT”/>

One of the most important things is not to forget to reference the appropriate report query you want to display, replacing .//ROW with DATA/ROWSETx/ROWSETx_ROW (where “x” is the position of the query inside the report query).

Check out and see a PDF Report Layout with 2 report queries and some custom CSS, here (use the “Print PDF” button).

 


In the next posts, I will detail the XSL-FO customization:

– how to add pictures to the PDF

– how to customize the header and the footer

– how to add conditional blocks

– how to add custom CSS to all the XSL-FO objects

– known limitations

– test cases and demonstrations on the ApexNinjas Test Ground 01

– how to download and use the ALGEN application

 

 

 

 

 

 

 

2 thoughts on “Custom APEX PDF printing. Editing XSL-FO report layouts

  1. Danilo Caruso

    Hi to all,
    I’m an italian developer working with APEX.
    I want to do a correction to this article to help most people with my same problem.

    When you change
    .//ROW
    with
    DATA/ROWSETx/ROWSETx_ROW
    (where x is the number of row you must interpreter from xml to xsl)

    you must use
    .//ROWSETx/ROWSETx_ROW
    that say to xsl to go from current location across “ROWSETx” tag to “ROWSETx_ROW” tag location, for find result data to iterate,
    instead
    DATA/ROWSETx/ROWSETx_ROW
    that say to xsl to go from ROOT location across “DATA” tag, across “ROWSETx” tag to “ROWSETx_ROW” tag location.

    The xml file, that apex generates from sql query, don’t has a “DATA” tag but only “ROWSETx” tag after the “DOCUMENT” tag.
    My correction coming from much time of brinstorming behind this thing that make me and my near collegue totally CRAZY :) .

    this is valid for APEX 4.1, i don’t know if is valid for earlier version and for the new 4.2 version.

    Thanks alot for your heavy work on APEX.

    Danilo Caruso.

Leave a Reply