Create a centralized reporting tool

One way to ensure that you can deliver the requirements from one location is to use XML and XSL to format a report's output. Here's how.

This article originally appeared as a Web Development Zone e-newsletter.

By Phillip Perkins

Data and reports go hand in hand. For
instance, when you have an application for data entry, you'll
probably have a need for reporting.

A report can take many forms. Even though one
user may want a hard copy with detailed information and another
user may want a spreadsheet with formulas and formatted data, the
data is usually the same. The good news is that providing users
with the necessary formats doesn't necessarily mean you have to
develop one approach for each format.

One way to ensure that you can deliver the
requirements from one location is to use XML and XSL to format the
output. Microsoft Office 2000 and XP provide the ability to produce
output based on HTML and XML. Microsoft Excel 97 will render TABLE
data, and Microsoft Word 97 and above can process RTF files. Also,
Sun's StarOffice files are all XML based.

The best way to utilize this functionality is
through XSL. When a user requests a report of a particular format,
you can get the XML data and run it through an XSL transformation
to produce the desired report from one location. The concept behind
this is simple: When a user requests a particular report, that
information is cross-referenced in a "reports" table to get the
pertinent information used to create the result.

Several considerations

When delivering the output to the browser, the
browser must initiate the proper application to handle the
document. This is done through the various MIME types. In HTTP, the
MIME type is specified in the Content-Type header.

For example, say you're delivering a Microsoft
Excel file to the client. The MIME type would be
"application/vnd.ms-excel". In most cases, the file will then be
delivered to Excel for processing (assuming that Excel is the
application set up to handle that particular MIME type). In ASP,
the way to set the MIME type is through the ContentType property on
the Response object.

Also think about the file name of the output,
the method for acquiring the data, and the XSL stylesheet that will
be used to convert the XML data. In order to handle these criteria,
you can store this information in a table so that the appropriate
format can be produced. In the database you're using, create a
table, name it "reports", and add the field's report_id,
report_name, stored_procedure (I'll be using stored procedures to
acquire the data), mime_type, filename, and version.

report_id is an identifier for the
report.

report_name is the name of the report that
you'll use to query the report data.

stored_procedure is a field for storing the
name of the stored procedure used to produce the data for the
transformation. You can create an alternate field that can contain
the actual SQL text used to produce the data.

mime_type is self-explanatory.

filename is the name of the file delivered to
the client. This name is specified in the Content-Disposition HTTP
header.

version is a field that I want to add to
support different versions of office software.

Produce a page for report formats

The next step is to create a page that will
provide the user with the ability to choose the report and the
report format. This can be a simple HTML page that contains a list
of reports and a list of formats for the particular report. This
page could also contain the variable information that you need to
filter the data. Here's an example:

This page only allows the user to select a
Sales Report in Microsoft Excel 2000 format. As far as the format
is concerned, I listed the MIME type as the options value. This
way, I can query my reports table for the report with the
report_name of sales_report and a mime_type of
application/vnd.ms-excel. I could go even further to specify an
office version for my report, but, for this example, it isn't
necessary.

To add another field to my reports table, I'll
add the field stylesheet to store the name of the XSL stylesheet
that I'll be using to do the transformation.

This page creates an object that will connect
to a Microsoft SQL 2000 database to retrieve the report information
and data. The first thing it does is run a stored procedure; it
then returns a recordset with pertinent report information. This
includes the stylesheet that will be used to perform the
transformation, as well as the stored procedure name for getting
the XML data.

The next thing that happens is that it runs the
stored procedure defined in the reports record. The data is queried
from the database and returned in XML format through an ADO stream.
The XML data is then loaded into the dom DOM Document. If there are
any problems parsing the XML data, the page raises an exception
with the parsing error.

The page creates another DOM document for
storing the XSL stylesheet for performing the transformation. The
stylesheet is loaded, the Content-Type header and the
Content-Disposition header are set, and the transformed XML data is
sent into the Response.Buffer. After a little cleaning up, the page
is finished.

The benefit to this approach to reporting is
its centralization. Also, if you need to update or change the
output of the reports, you only have to tackle the individualized
stylesheet for that report. In addition, if you maintain the
business logic in your stored_procedures, making any changes to the
actual returned data is simple.

Phillip Perkins is a contractor with Ajilon Consulting. His experience ranges from machine control and client/server to corporate intranet applications.