| Exporting live Web reports assures that users have up-to-the-minute data. Live Web reports execute a query against an SQL Server table, view, function, or stored procedure when users open the ReportName.htm or ReportName.asp file that's generated by the export process. Live Web reports are well-suited for displaying current sales and inventory data, as well as summaries of hourly or daily Web site statistics. An advantage of live Web reports is that you don't need to periodically refresh the .xml file that provides the report's data; live Web reports obtain their data directly from SQL Server in XML format. Note Only ADP connected to SQL Server 2000 databases can export live Web reports. Live Web reports issue an SQL Server HTTP query to a designated database. SQL Server 7.0 and earlier don't support HTTP queries, which also are called URL queries.
Exporting live Web reports or running SQL Server 2000 HTTP queries, the subject of the later "Using SQL Server 2000's HTTP Query Features" section, requires installation of the following applications and services: Any version of SQL Server 2000 for which you have a valid license that covers the intended use. MSDE requires an Office XP license for the machine on which it's installed. The database server can run on the machine running IIS or any other computer on the network. IIS 5.0+ and IE 5.0+ installed by Windows XP/2000 Professional and Windows 2000+ Server. Windows XP installs IE 6.0+ and Windows Server 2003 installs IE 6.0+ and IIS 6.0. IIS Virtual Directory Management for SQL Server, a component of the SQL Server 2000 Client Tools installed from an SQL Server edition other than the Desktop Engine. IIS Virtual Directory Management for SQL Server is the user interface for the Internet Services API (ISAPI) extension (filter) for SQL Server (Sqlisapi.dll).
Note  | The SQL Server 2000 Client Tools are required to enable most XML features of SQL Server 2000 Evaluation Edition, including installation and removal of the SQLXML 3.0 Web download. SQLXML 3.0 installs an advanced version of IIS Virtual Directory Management for SQL Server that's required for creating XML Web Services from SQL Server 2000 stored procedures. Chapter 31 describes how to use the SQLXML 3.0 version. SQL Server 2000 Trial Software (downloadable) and Evaluation Edition (CD-ROM) also include the Client Tools. You can learn more about these products at http://www.microsoft.com/sql/evaluation/trial/. |
Setting Up the IIS Virtual Directory for the Database You can export live Web reports from ADP without configuring SQL Server support for IIS, but you can't test the results by executing the exported ReportName.htm or ReportName.asp file until you do. Caution When you run the SQL Server 2000 setup program, don't install SQL Server 2000. You only need the Client Tools. In the initial screen, click SQL Server Components and Install Data Base Server to start the Installation Wizard. Some editions permit installing only the client tools to the local computer running Windows XP/2000 Professional. The Installation Configuration page of the Setup Wizard is where you select the installation type. Be sure to select the Client Tools Only option on this page. On the Select Components page, clear all but the Management Tools check box. Be prepared to wait a few minutes while the Wizard installs the required files.
The basic steps to add the IIS virtual directory for an SQL Server database are as follows: Add a subfolder to your ...\Wwwroot folder that you specify as the virtual directory for SQL Server HTTP queries against a specific database. The subfolder name must be the same as the database name, NorthwindCS for this example. Use the IIS Virtual Directory Management for SQL Server MMC snap-in to configure the virtual directory. Export the report to XML using the expanded version of the XML Export dialog, placing the XML and XSL files in the ...\Wwwroot folder or another subfolder, such as ...\Wwwroot\Reports.
Following are the detailed steps to configure IIS 5.0+ for SQL server HTTP queries against the NorthwindCS database: Create the \Inetpub\Wwwroot\NorthwindCS folder. Create an \Inetpub\Wwwroot\Reports folder to place live Web reports in their own folder. Choose Start, Programs, Microsoft SQL Server, Configure SQL Server Support for IIS to open the IIS Virtual Directory Management for SQL Server snap-in. Expand the ServerName node OAKLEAF-XP1 for this example, and select the Default Web Site. The snap-in's right pane displays an empty list with Virtual Directory Name, SQL Server, and Database Name column headers. Right-click the right pane, and choose New, Virtual Directory to open the General Page of the New Virtual Directory Properties dialog. Type a name for the virtual directory, which must have the same name as the SQL Server database (as mentioned earlier). Use NorthwindCS for this example. Click Browse to open the Browse for Folder dialog, select the ...\wwwroot\NorthwindCS folder, and click OK (see Figure 23.11). Figure 23.11. Name the new virtual directory as the database name and the path to its physical folder in the General page of the New Virtual Directory Properties dialog.  Note The third Microsoft mystery of this chapter is why the developers of the SQL ISAPI filter decided to specify a height of 600+ pixels for the dialog, when 480 pixels easily would suffice. Click the Security tab and select the Use Windows Integrated Authentication option. To permit anonymous Web access to SQL Server, you must grant the IUSER_SERVERNAME account an SQL Server login and at least public rights on the NorthwindCS database. A later section describes how to set up Web access permissions for the database. Caution MSDE 2000 installs with Windows authentication as the default. If you specify SQL Server security with a login ID and password, both values can become accessible to unauthorized users as clear text in the .htm or .asp file. Click the Data Source tab, type the NetBIOS name of the computer (OAKLEAF-XP1) in the SQL Server text box, and select NorthwindCS in the Database list (see Figure 23.12). Figure 23.12. Specify the SQL Server instance name, and designate the database for the virtual directory in the Data Source page.  Click the Settings Tab and mark the Allow URL Queries check box (see Figure 23.13). The .htm and .asp execute HTTP (URL) queries against the database. (Template queries are permitted by default.) Figure 23.13. Mark the Allow URL Queries check box to permit the exported .asp (or .htm) file to send HTTP queries to the server.  Click the Virtual Names tab, and click the New button to open the Virtual Name Configuration dialog. Type Templates as the Virtual Name, and select Template in the Type list. Click the browse button to open the Browse for Folder dialog, navigate to and select the folder you added in step 1, and add \Templates to the path (see Figure 23.14). Click Save to return to the Virtual Names page (see Figure 23.15). Figure 23.14. The Templates folder you specify at this point is used for custom HTTP queries that you create later in the chapter.  Figure 23.15. The Virtual Names page displays a list of the names of folders you create for templates, database objects, or schemas.  Click OK to save your settings, close the dialog, and add the new virtual directory to the IIS Virtual Directory Management for SQL Server snap-in's right pane (see Figure 23.16).
Figure 23.16. You can add as many virtual directories as you have SQL Server databases on your network, or add multiple virtual directories for a single database.  Modifying the Sales Totals by Amount View A modified version of the Sales Totals by Amount report to provide a report of the 100 largest orders received by Northwind Traders is a good candidate for export as a live Web report. Removing the view's existing limitation to 1997 orders allows newly received orders that meet the TOP 100 test to appear in the report. Tip You can't export a report having an SQL statement with JOINs between tables as the value of the Record Source property. To work around this problem, copy the SQL statement to a view, function, or stored procedure and specify one of these objects as the Record Source property value.
Modify the view for the live Web report in the following steps: Open NorthwindCS.adp, and choose File, Connection to open the Data Link Properties dialog. If the server name is (local), type the NetBIOS name of the computer (OAKLEAF-XP1 for this example), click Test Connection, and then click OK to save the changes.   Open the Sales Totals by Amount view in Design view.
Change the BETWEEN... criterion of the ShippedDate column to NOT IS NULL to include only orders that have shipped. Remove the > 2500 criterion of the Subtotal column to make all rows accessible. Add a Descending sort type to the Subtotal column to place the highest-valued orders at the top of the query result set.  Right click the upper pane and choose Properties to open the View page of the Properties dialog.
Clear the Percent check box to return the first 100 rows, and close the Properties dialog. Figure 23.17 shows the reformatted view design. Figure 23.17. The modifications to the Sales Totals by Amount view return 100 orders having the highest sales value.   Click Run and save your changes to the view. 100 shipped orders appear in descending order of their total amount (see Figure 23.18).
Figure 23.18. The datasheet confirms your modifications to the view.  Close the view and open the Sales Totals by Amount report to confirm that it displays correctly. Close the report, because you can't export the report when it's open in Print Preview.
Exporting the Sales Totals By Amount Report To export the live Web report to the Reports subfolder you created in the preceding section, do this: Choose File, Export to open the Export Report "Sales Totals by Amount" As dialog, select XML(*.xml) in the Save As Type list, and navigate to the \Inetpub\wwwroot\Reports subfolder, if you added it. Replace the report name in the File Name text box with Top100Orders, and click Export. (Spaces in the file name require users to type a URL with spaces, which isn't a recommended practice.) Click the More Options button to expand the Export XML dialog, and select the Live Data option in the Data page.  | Specify http://COMPUTERNAME/DatabaseName as the URL for the virtual directory in the adjacent text box http://OAKLEAF-XP1/NorthwindCS for this example. |
Click the Browse button to open the Browse dialog, navigate to the folder containing the exported files, \Inetpub\wwwroot\Reports for this example, and click OK. The Export XML dialog's Data page appears, as shown in Figure 23.19. Figure 23.19. Specify the Live Data option, set the Virtual Directory for the database, and set the Export Location to the subfolder for the virtual directory for the exported live report files.  Click the Presentation tab and select the Client (HTML) option in the Run From frame to send XML and XSL documents to Web clients for processing by IE 6+. Live reports don't support Active Server Pages. Repeat step 4 to place Top100Orders.xsl in the virtual directory subfolder (see Figure 23.20). Figure 23.20. Select the Client (HTML) option in the Presentation page to deliver the data document and transform to client browsers.  Click OK to export the live Web report to the folder you specified in step 1. Open IE, type http://servername[/folder]/Top100Orders.htm in the Address box, and press Return to display the live report (see Figure 23.21). Figure 23.21. The live Web page is a close facsimile of the original Access report, except for the values of the Counter column. 
 | If your report has defects, see the "Problems with Live Web Reports" topic of the "Troubleshooting" section near the end of the chapter. |
The RPT2HTML4.xsl style sheet faithfully emulates the appearance of simple Access reports, except for the Counter values incremented by the Running Sum property value of Over All. As mentioned in the earlier "Exporting Static Reports as XML" section, RPT2HTML4.xsl doesn't support the Running Sum property. |
