Reporting in Apex 3.0

In the next Application Express release, PDF reporting will be included. No more external tools are needed because the reporting will be built in the Apex tool.

There are several formats to report from the Apex tool. PDF, HTML, Excel as well as Word will be supported. The reporting can be setup from two different places.

It is possible to print out the report region result with the use of a link under the report. The second place to implement a pint-out can be done with the use of a button, list item or other navigation item. The item calls to a general defined report definition.

For defining a print-out link under the report, Apex introduces a third, new navigation tab. This tab is positioned next to the Report Attributes tab in the Report’s tab-set. The tab is named Print Attributes. Here you can define the label of the link and other attributes like font colors and alignments. By default all columns will be printed, but they can be limited to a specific output.

The implementation of a general defined report is set in the Shared Components of the application, more specifically in the Report Queries of the application. A Report Query is the definition of a report. The report data will be retrieved from a SQL query result. The query can consist of bind variables, these are items that are known in the session, for example application items or page items.

The report layout is based on a predefined Report Layout. It is possible to choose for the default general layout or you can choose to create your own layout.

When you choose to create you own layout, you need to use the Oracle XML Publisher Template Builder for MS Word. With this tool, you can make a template that can be used to show the report output.

As the name speaks for itself, XML Publisher makes use of XML files. So we need a XML file before we can start defining our custom layout. Apex gives the possibility to download a print of the query result in a XML file. With the data of the XML file, you can create a RTF file in the XML Publisher Desktop tool.

The layout(s) of the report is separated from the definition of the report. The RTF files or XSL-FO files can be uploaded in the Report Layouts of the application. This can also be found below the Shared Components of your application. After the report template is uploaded the report query definition need to refer to your custom layout.

Finally when the report query definition is complete, you need to implement a call to the definition on one of your pages. Add the end of the report query wizard, an URL is put together.

By example:

What’s remarkable about this URL is that the request is send to page zero and a new parameter is known in the URL structure. The parameter’s name is PRINT_REPORT and it‘s value in this case is MyReport which refers to the name of my report query.

That URL will be used to integrate the report into your page(s). You will use the URL as a target for buttons, list items or other navigation items.

What happens when the navigation item is pressed?

  1. The parameter PRINT_REPORT refers to the defined report query MyReport.
  2. The XML document will be generated with the returned data from the SQL Query.
  3. The XML file will be used by XML Publisher to load the data in the chosen report layout.
  4. The layout will be formatted in the right report format.
  5. A download window disappears at the client to open or save the file.

Have a look at some examples:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

About Tuur Hendrickx