XML Reporting in SQL*LIMS
XML Reporting in SQL*LIMS
By Jeff Vannest, Senior Consultant
For several years my boss, Brian, has been telling me that the perfect solution for a LIMS Certificate of Analysis report would use a solid data extraction algorithm and a flexible XML output. The idea has always sounded interesting to me, except for the fact that the SQL*LIMS product doesn’t include the technology to do such a thing…until now. Today’s article describes how to customize SQL*LIMS and Oracle Reports to send data to XML, and how to convert that XML output to PDF.
Standard SQL*LIMS Reporting
Normally, SQL*LIMS implements all reports using Oracle Reports Developer for both data selection and report layout. This solution is consistent with the core technology, but has several disadvantages. Foremost, Oracle Reports Developer is a confusing and unstable application. Any developer that has spent significant time creating Oracle reports can tell you the application crashes several times a day. Sometimes it crashes when copying and pasting layout objects, sometimes it crashes for no reason at all. Whatever the case, it’s frustrating to loose work, and seasoned developers learn to save their work obsessively. Also, Oracle Reports allows the developer to place programmatic triggers in many places throughout the application. Starting with the report properties form before and after triggers, programmatic logic can be executed in the data model, before and after triggers on the report body, and on every object in the report layout. The result is a confusing amalgam of logic that is spread out across dozens and dozens of program units, and executes in a very complex order, sometimes repeatedly. For all of its power, Oracle Reports Developer turns a simple reporting task into an advanced task, and an advanced reporting task into rocket science. For a complex report like a Certificate of Analysis, this solution becomes too cumbersome to maintain, not to mention the CPU cost of causing most of that processing to occur not in the Oracle Database server, but in the user’s client.
The following solution architecture was not my first choice. Oracle has a product called Oracle XML Publisher that I believe would produce the same result as the customization described in this article. However, when we received requirements for a flexible output reporting solution from a customer recently, it was clear that requiring the purchase and implementation of Oracle XML Publisher would not be an option, since the decisions regarding architecture had already been made based on the software required by SQL*LIMS version 5.0.1. This architecture consists of Oracle 10g Database and Oracle 10g Application Server, which includes the Oracle Forms server to implement the SQL*LIMS software forms, and Oracle Reports server to implement SQL*LIMS reports.
Design Decisions
During design several questions needed to be answered:
1) How to extract data according to complex business rules in a manner that was quick to execute, yet simple to understand during software maintenance?
2) How to get the selected data into an XML format?
3) How to change XML data into a user-friendly report layout?
4) How to change a user-friendly report layout into a format that could not be modified by the end user?
Selecting Data
We need to remove data selection out of Oracle Reports. The logical choice given the architecture is to place all data selection criteria into a database package. This is advantageous because it makes the implementation of business rules for selected data more transparent. Think of data selection in Oracle Reports like a system of tree roots; although it starts in one place in the data model, it quickly branches out into a complex system of tiny logical branches as the report executes group, frame, and item formatting triggers. Placing data selection in a database package forces the data selection algorithm into a single, straight line. Also, the package executes in the database, meaning that the user’s client is relieved of this computational task.
Another of the big advantages of using a database package is that the data selection algorithm can be modified without accidentally affecting report layout. In other words, the maintenance of business rules as implemented by report data selection is isolated from the rest of the solution, and may be modified by any developer with PL/SQL knowledge.
Formatting Data to XML
In order to format the data as XML, Oracle Reports Developer is used to create PL/SQL selection blocks in the data model. Each query returns a reference cursor from a function in the database package, and each field returned by the reference cursor is assigned a proper XML tag according to the desired reporting XML schema. The report does not require any layout, since when Oracle Reports executes to an XML format destination only XML data is output according to the order in which queries appear in the data model.
Placing XML formatting in an Oracle Report also allows this custom solution to stay within the SQL*LIMS v5 architecture, since Oracle Reports is normally used for report. So, the report is executed in SQL*LIMS either interactively using the Run Events form, or automatically using a server status action, and SQL*LIMS executes the Oracle Report, which uses the database package to select data, and outputs the data into XML.
Making XML User-Friendly
The next decision is how to make XML user-friendly. Fortunately, the World Wide Web Consortium, or the W3C, has created XSL, which stands for Extensible Stylesheet Language. In simple terms XSL is a family of standards that define the semantics for working with, presenting, and manipulating XML data. One member of this family is XSL-FO, which allows XML data to be converted to a paginated, user-friendly format. Because this is a fairly complex transformation, it is necessary to use a helper application such as Altova StyleVision. This application takes XML data and allows it to be placed on the page in a user-friendly manner. The following is a screenshot from the Altova website.
For a developer that is comfortable with using an integrated development environment (IDE), this application turns stacks of XML data into simple fields that may be placed on the page similar to using Oracle Reports Developer, which for the purpose of comparison is shown in the following screenshot.
Converting XML to PDF
The final component in this customization is to convert the XSL-FO transformation into PDF. This is the most technical part of the system, and requires both custom software and a modification to the Oracle Reports server.
While the ability to transform XML into a new markup format, XSL-FO, is a step in the right direction, a FO processor must be used to convert XSL-FO into PDF. For this, we must rely on Apache FOP.
A FOP, which stands for formatting objects processor, takes XSL-FO markup and turns it into a paginated format such as RTF or PDF. While there are several FOPs on the market, most notably Oracle’s own XML Publisher, we required a FOP that could be plugged directly into the SQL*LIMS v5 architecture and would not adversely affect previously made financial decisions. Apache FOP is both free, and as described below, could be plugged directly into the Application Server as an Oracle Reports Pluggable Destination.
Oracle 10g Application Server introduced Pluggable Destinations, which allow a developer to create and implement a custom destination in the Oracle Reports server. As a proof of concept, Oracle delivered the standard FTP destination using this new feature. Custom destinations are implemented as java servlets and are executed by Oracle Reports as an integrated part of the Application Server architecture.
It may occur to the astute technician that using an Oracle Reports Pluggable Destination to transform XML to PDF using XSL-FO is not consistent with the architecture. This is accurate: this is a change in format not a change in destination. However, the limitation is that Oracle Reports does not yet, nor will it likely ever, implement “Pluggable Formats” for Oracle Reports. Therefore, the only option is to output the report as XML and convert to PDF during the implementation of the destination.
Creating a Pluggable Destination that uses Apache FOP is fairly straight-forward for a knowledgeable java developer. Both are written in java, and the newest version of Apache FOP is thread safe, an absolute necessity when executing in a servlet environment. The final java jar is placed in the Oracle Reports jlib folder along with all font and metrics files, which enables the FOP to embed all fonts into the PDF document. During transformation, Apache FOP uses an XSL-FO file created by Altova StyleVision and the XML data that Oracle Reports stores in the cache to transform XML to PDF. After transformation, the servlet copies the PDF file out of the cache to a shared folder on the SQL*LIMS application tier. This folder is shared to permissible users as necessary.
Pluggable Destinations in SQL*LIMS
The standard SQL*LIMS version 5 software does not support Oracle Reports Pluggable Destinations. However, a simple tweak to the EVENT_DISTRIBUTION_CLASS system parameter allows events to be configured to the “Transform” destination. This modification to the SQL*LIMS software only allows the event to be executed in server mode, but this is actually a limitation intrinsic to the Oracle Forms software, which does not yet support Oracle Reports Pluggable Destinations in its internal APIs. Conveniently, the custom destination is passed through the SQL*LIMS Event Monitor without incident, and sent directly to the Oracle Reports server for processing.
Summary
All of this technology is transparent to the SQL*LIMS user. The Certificate of Analysis event is executed using standard methods (Run Events form or status action), and the PDF report appears in the user’s file share using a filename that is specific to the laboratory, lot, protocol, and date and time. If a change in business requirements is necessary, only the database package is affected. If a change in report layout, page size, or selected columns is necessary, only the StyleVision transform file is affected. In the final implementation, users are allowed to select from one of several report formats, meaning that the same data can be presented in a layout that is sensitive to language, market, region, or specification.
Best of all, the new functionality is based on international standards, with a bare minimum of modifications made to the vendor-provided SQL*LIMS software.
About the author: Jeff Vannest is a Senior Consultant at J&R Consulting, Inc, a consulting firm specializing in LIMS systems, and excels in technical design, development and implementation. Weekly articles on LIMS software and contact information can be found at http://www.jandrconsult.com
