PDF Access Reports

The PDF Access Reports component of Reggie/CIS enabled its reporting system to generate PDF reports by using Microsoft Access and open-source software. This obviated a need for a Crystal Reports server license and its associated maintenance. By using Microsoft Access, PDF reports were also easier for me to create and maintain, since I didn't know Crystal Reports very well at the time.

Once this system was in place, it ran itself without any trouble, though I cannot vouch for its stability after I left to go to graduate school.

As a design limitation, the PDF Access Reports component could run only one report at a time. This was a result of how the PDF file got generated, which was to use COM automation to tell Microsoft Access to print a report. This limitation in practice was not a problem because the longer-running reports had their long-running parts queried into a purpose-built holding table before the PDF generation callout, and the in-practice concurrent demand for PDF generation was not very high. Concurrent PDF report requests were queued up using standard TCP connection queuing in the PHP-based server process that the reporting server's COM component connected to.

The default printer on the server was set up using RedMon and AFPL Ghostscript with spooling disabled to print to a fixed file location that got picked up after Access returned from the COM-instantiated print command.

Here is how the PDF Access Reports server worked:

• Following the path of execution from the start at the top-left, a user requests a PDF report from a web browser, sending that request to the appropriate ASP page in the Reggie Reporting System.
• That report calls a function in PDFReport.INC to run the report, supplying an optional WHERE clause in the call.
• The PDFReport.INC file then instantiates the PDF Generation COM object and tells it to run the report.
• Responding to the call, the COM object contacts the PHP PDF Server. If another report is running on the PHP PDF Server, this new report request waits in the PHP PDF Server's TCP/IP pending connection queue until it gets around to completing the connection to service the request.
• Once the request comes in, the PHP PDF Server uses PHP's COM abilities to tell the PDF Access Database to print a report given the optional WHERE clause. Access runs the report, querying data from the databases via ODBC in the process.
• As pages become available for printing, Access prints them through the standard Win32 APIs, generating a GDI Enhanced Metafile data stream for the printer driver.
• The Apple Color LaserWriter 12/600 printer driver, which is configured on the default printer, generates Postscript data and streams it to the output port, which is RedMon rather than a real printer port.
• RedMon, configured to pipe the output through AFPL Ghostscript, sends the Postscript data to Ghostscript along with command-line parameters telling it to convert Postscript into PDF and place it into a specific file location.
• Ghostscript performs the conversion and writes the PDF file.
• Once that process completes, the PHP PDF Server returns from the Access Print Report command (the virtual PDF printer is configured to "print directly to printer" instead of to a print queue).
• The PHP PDF Server reads the file and sends the data through the original TCP/IP connection initiated by the COM object.
• The PDFReport.INC file then copies the binary data from the COM object to the Response object in ASP, streaming it back to the original web client.
• The web client, receiving application/x-pdf data, launches an Adobe Acrobat Reader object for the web browser and displays the PDF data in that browser window object, completing the cycle.