You want to export a report that can be displayed on your web site. You'd like to preserve the formatting of the original report.
One of the new features in Access 2003 is the ability to export reports to XML, preserving formatting and displaying aggregates (totals, counts, averages, and so on). You can export formatted reports to ASP or to HTML.
The
18-8.MDB sample database contains a report named rptCustomer that displays customer preferences, as shown in Figure 18-20. The report has an aggregate function that counts the number of cars ranked, and displays the make and model for each. The following sections show you how you can export the report to HTML and to ASP.
Follow these steps to export the report to HTML:
Right-click on the rptCustomer report in the
18-8.MDB database window and choose Export from the menu.
Select XML from the Save as type drop-down at the bottom of the dialog box.
Name the output file CustomerPreferencesHTM and click OK.
Select all three checkboxes on the Export XML dialog box, and click the More Options button.
Specify the output folder where the output files will be located and click the Presentation tab. Make sure that HTML is selected, as shown in Figure 18-21. Click OK.
Using Windows Explorer, browse to the location where you saved the files. You should see four files listed: CustomerPreferencesHTM, CustomerPreferencesHTM.xml, CustomerPreferencesHTM.xsd, and CustomerPreferencesHTM.xsl.
Double-click the CustomerPreferencesHTM file to load it into your browser. It should look like that shown in Figure 18-22.
Follow these steps to export the report to ASP:
Right-click on the rptCustomer report in the
18-8.MDB database window and choose Export from the menu.
Select XML from the Save as type drop-down at the bottom of the dialog box.
Name the output file CustomerPreferencesASP and click OK.
Select all three checkboxes on the Export XML dialog box, and click the More Options button.
Specify the output folder where the output files will be located (this can be an IIS application folder) and click the Presentation tab. Select the ASP option and click OK (see Figure 18-23).
In order to display the report, you will need to copy the four files createdCustomerPreferencesASP.asp, CustomerPreferencesASP.asp, CustomerPreferencesASP.asp, and CustomerPreferencesASP.aspto your web server, unless you had the wizard place them there.
Launch CustomerPreferencesASP.asp using the HTTP protocol. The report should look like that shown in Figure 18-24:
http://localhost/TestApps/CustomerPreferencesASP.asp
When you export a report to HTML, Access does a lot of work under the covers to ensure that the output looks almost identical to the output of the report when rendered in Access. Access generates quite a bit of VBScript code to achieve these results.
Open the CustomerPreferencesHTM file in Notepad or another text editor to see how the code applies a transform on the CustomerPreferencesHTM.xml file by invoking the CustomerPreferencesHTM.xsl:
<HTML xmlns:signature="urn:schemas-microsoft-com:office:access">
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="textl;charset=UTF-8"/>
</HEAD>
<BODY ONLOAD="ApplyTransform( )">
</BODY>
<SCRIPT LANGUAGE="VBScript">
Option Explicit
Function ApplyTransform( )
Dim objData, objStyle
Set objData = CreateDOM
LoadDOM objData, "CustomerPreferencesHTM.xml"
Set objStyle = CreateDOM
LoadDOM objStyle, "CustomerPreferencesHTM.xsl"
Document.Open "textl","replace"
Document.Write objData.TransformNode(objStyle)
End Function
Function CreateDOM( )
On Error Resume Next
Dim tmpDOM
Set tmpDOM = Nothing
Set tmpDOM = CreateObject("MSXML2.DOMDocument.5.0")
If tmpDOM Is Nothing Then
Set tmpDOM = CreateObject("MSXML2.DOMDocument.4.0")
End If
If tmpDOM Is Nothing Then
Set tmpDOM = CreateObject("MSXML.DOMDocument")
End If
Set CreateDOM = tmpDOM
End Function
Function LoadDOM(objDOM, strXMLFile)
objDOM.Async = False
objDOM.Load strXMLFile
If (objDOM.ParseError.ErrorCode <> 0) Then
MsgBox objDOM.ParseError.Reason
End If
End Function
</SCRIPT>
</HTML>
The CustomerPreferencesHTM.xsl file contains all of the formatting for the report, and is quite complex. Figure 18-25 shows just a fragment of the file.
After the formatting instructions, the file contains more VBScript code that has been generated to handle reporting engine features such as evaluating expressions, running sums, sorting and grouping and so on. The code is commented, so you can examine it and see what it's doing.
When you choose to output the XML to ASP, the page is intended to run on the server, and different code is generated. If you attempt to load the ASP page from the file system, you will get an error.
If you open CustomerPreferencesASP.asp in Notepad or another text editor, you will see the following code which invokes the .xml and .xsl files:
<%
Set objData = CreateDOM
objData.async = false
if (false) then
Set objDataXMLHTTP = Server.CreateObject("Microsoft.XMLHTTP")
objDataXMLHTTP.open "GET", ", false
objDataXMLHTTP.setRequestHeader "Content-Type", "text/xml"
objDataXMLHTTP.send
objData.load(objDataXMLHTTP.responseBody)
else
objData.load(Server.MapPath("CustomerPreferencesASP.xml"))
end if
Set objStyle = CreateDOM
objStyle.async = false
objStyle.load(Server.MapPath("CustomerPreferencesASP.xsl"))
Session.CodePage = 65001
Response.ContentType = "textl"
Response.Write objData.transformNode(objStyle)
Function CreateDOM( )
On Error Resume Next
Dim tmpDOM
Set tmpDOM = Nothing
Set tmpDOM = Server.CreateObject("MSXML2.DOMDocument.5.0")
If tmpDOM Is Nothing Then
Set tmpDOM = Server.CreateObject("MSXML2.DOMDocument.4.0")
End If
If tmpDOM Is Nothing Then
Set tmpDOM = Server.CreateObject("MSXML.DOMDocument")
End If
Set CreateDOM = tmpDOM
End Function
%>
The CustomerPreferencesASP.xsl file is structured similarly to the CustomerPreferencesHTM.xsl file, with formatting instructions for how the report is to be rendered at the bottom of the page.
These built-in export capabilities for Access reports show that outputting the data as XML and formatting it using XSLT is a powerful approach. With the proper XSLT transformations, your Access reports can be exported to any text-based document format.
The \Program Files\Microsoft Office\OFFICE11\AccessWeb directory, which is created when you install Access, contains additional transforms to assist you in creating your own XSL stylesheet. See Rpt2HTM4.xsl, used to transform Access reports into ReportML, which describes the presentation-specific attributes of a report. The Rpt2DAP.xsl transform also found in the folder can be used to transform ReportML into a data access page (DAP).