Create a centralized reporting tool

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.

Enter a record in your reports table with the following information:

Now create a page that will grab the report information and the XML data and run the transformation on the XML data:

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.