Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

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.htm, CustomerPreferencesHTM.xml, CustomerPreferencesHTM.xsd, and CustomerPreferencesHTM.xsl.
Double-click the CustomerPreferencesHTM.htm 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 created—CustomerPreferencesASP.asp, CustomerPreferencesASP.asp, CustomerPreferencesASP.asp, and CustomerPreferencesASP.asp—to 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.htm 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).

[ Team LiB ]