Querying and Reporting: Printing Reports from the Web

A simple but limited solution to a complicated problem

Organizations now require the ability to generate reports from Internet and intranet applications. Web users need to produce hard copies of what's on the screen or selected data that they query on.

You can choose from many options, including third-party tools, to let users produce reports. Some tools generate reports from the server. Using a server reporting tool across the Internet presents a problem because the user's printer likely is on the local system, not the server. For example, if salespeople use notebook computers to access the company's sales intranet from a hotel, they might want to print sales data locally on a portable printer. Other Web users also might need to print data locally from the application, but they can't if the application prints through the server. How can you solve this problem?

One possible approach is to use ADO and the browser for the reporting task, but this approach might not work. The user can specify a printer for a document, but browsers are notorious for their inability to accurately print screen contents and for their lack of print options. Further complicating matters, print capabilities vary from browser to browser. In this article, I demonstrate a simple, albeit limited, method that uses ADO and the browser to generate Internet or intranet reports.

Many Web pages aren't suitable for printing as a report. For example, Web developers often create pages that display the data in a table with 10 to 15 rows. When users need more data, they scroll forward and the page refreshes with a new set of data for the table. This interface is efficient and easy-to-use, but it doesn't help users when they need to print a report that contains their entire selection of data. Screen 1 shows an Active Server Pages (ASP) file, CustomerStuff.asp, that implements a scrolling interface showing only 10 rows per page. CustomerStuff.asp uses the Visual InterDev 6.0 Grid Design-Time Control (DTC) to implement the table. The Grid implements the scrolling actions and display options for the table and reduces the amount of coding you need to do. Note that the Grid DTC runs the entire query

select * from customers

for each set of 10 rows as the user pages through the document. This approach can degrade performance and hinder scalability. If the user clicks the browser's print button, only the current rows in the table will print because the browser has no knowledge of any other data.

To add the ability to print more rows, I added the Customer Report link at the bottom of CustomerStuff.asp. Clicking this link displays the same data from CustomerStuff.asp in a different format, as Screen 2 shows.

The output in CustomerReport.asp contains all the rows in the recordset in a simple HTML table, with one twist: The breaks between the page sections are set up the way reports were in the early days of computing. Back then, users had to insert blank lines into each page to correctly force the page headers to flow to the top of the next page. No banded report writers existed to automatically output the correct printer codes.

You can use the code in Listing 1 to insert the report headers in the example report. Listing 1 shows the code for CustomerReport.asp. The OutputHeader function in Callout A generates the report header for the table. The iBreaks variable determines the number of blank lines at the end of each group of records. When the first page is printed, the iBreaks variable reduces the number of inserted blank lines by two to allow for the header on page 1. The iCurrentPage variable tracks the current page number, which appears in each header.

If the value of the iPageCount variable is greater than 0, the code has generated printed records, and consequently, the function adds the terminating table tag () and blank lines. The remaining code in the function simply outputs the table header again.

Callout B in Listing 1 shows the code for the PageBreak function. This function returns True if the iDesiredPageCount variable is equal to the iPageCount variable or if iDesiredPageCount variable minus 2 is equal to the iPageCount and the current page number is 2. This calculation accounts for the reduced number of rows on the first page.

The code in Callout C in Listing 1 specifies the number of records per page (group of records per table). If the DesiredPageCount querystring/form variable isn't blank, this variable determines the number of records, which is set in the iDesiredPageCount variable. If the DesiredPageCount variable is not set, the number-of-records default is set from the LinesPerPage cookie if the cookie returns a value. If the cookie doesn't return a value, the function sets LinesPerPage to 50.

The code in Callout D in Listing 1 executes the PageBreak function to determine when to generate a new page header. If PageBreak returns True, the code executes the OutputHeader function to generate a new header. The remaining code in the loop outputs a row in the detail table and moves the recordset cursor to the next record. After the loop completes, a tag terminates the last table.

To help test and configure this report page, I created ReportConfiguration.asp, which creates the dialog box in Screen 3. ReportConfiguration.asp lets the user determine the number of lines per page, then saves this number in the LinesPerPage cookie. SetReportConfiguration.asp contains the code to save the cookie data. Then, the cookie redirects users back to the page they were on before they configured the report (WebSQL subscribers can download this code from the article index at http://www.sqlmag.com.)

Good but Not Great

Now, let's discuss the bad news. Browsers aren't designed as print engines. They contain print functions that let you print what's on the screen. Some browsers let you tweak this capability somewhat by adjusting the browser's print settings. For example, if you use Internet Explorer (IE), you can specify whether you want to print the current frame in a set or print all frames.

Although the method I show in this article works for simple print jobs, it demonstrates the problems inherent in printing from a browser. You can tweak the code in this article, but you'll still have problems with adjusting the header location on each page. If your reports are only two pages long, you'll be able to print them successfully. But if your reports are three or more pages, the headers will likely shift position from page to page. To solve this problem, you could dump the output into one long table and let that table flow from page to page. The problem with this solution is that headers will print on only the first page.

You can use Microsoft Word to print documents from the Internet. In the July 1999 issue, I demonstrated how you can use Word to print reports from Visual Basic (Querying & Reporting, "Generating Reports with Word"). Similarly, you can use Word and ASP on the Web server to print reports. The ASP code can generate a Word document that contains the report. Then ASP can provide the users a link to the document or redirect users' browsers to the new document. Users can download the document into the browser, where they can print it using Word's print engine. To use this method, users need to have Word installed and need to have a browser that supports downloading Word documents and either displaying them in the browser or launching a separate instance of Word. In this world of easy-to-use HTML and generic browsers, the problems involved in printing documents from the Internet demonstrate that easy doesn't always mean functional or complete.