Recipe 18.8 Export a Report | Access Data Analysis Cookbook (Cookbooks)

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).