Recipe 18.8 Export a Report
18.8.1 Problem
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.
18.8.2 Solution
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.
Figure 18-20. The rptCustomer report displaying customer preferences and rankings
18.8.2.1 Exporting to HTML
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.
Figure 18-21. Choosing the presentation format for the report
- 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.
Figure 18-22. The results of saving a report to HTML
18.8.2.2 Exporting to ASP
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).
Figure 18-23. Specifying ASP output for the report
- 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
Figure 18-24. Loading the report as ASP
18.8.3 Discussion
18.8.3.1 Dissecting the HTML
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="text/html;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 "text/html","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.
Figure 18-25. The xsl contains the formatting instructions for rendering the XML
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.
18.8.3.2 Dissecting the ASP
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 = "text/html"
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.
18.8.4 See Also
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).