The Web Report Database Reporting Tool

Lauren is an Internet software engineer for the Atlanta Group of USWeb. She can be reached at [email protected].

Delivering database applications via the Web has become commonplace, with web front ends replacing client/ server applications. Now the push is on to make those web apps as intuitive and easy to use as their predecessors. That's a pretty tall order, given the current set of tools and technologies we have to work with. Consequently, most good web apps are a creative blend of server-side and client-side scripting, along with (more recently) Dynamic HTML (DHTML).

In this article, I'll present an application called "The Web Report" that lets you hook up users to ODBC-compliant databases, then lets them create their own ad hoc queries and reports using the Web as the query-building interface. In the process, I'll show how to knit together Active Server Pages, Javascript, and DHTML to create an intuitive and effective web front end. To do so, I'll use Microsoft's Access 7.0 as my database, but you can use the techniques to reach any RDBMS, including Oracle, Informix, Sybase, or SQL Server. The examples presented here pertain specifically to Microsoft's Internet Explorer 4.0, but, with some changes to the DHTML code, can be implemented for Netscape's Navigator as well. To see The Web Report in action, see Figure 1 or visit http:// webreport.atlanta.usweb.com/.

The Web Report: An Overview

Canned reports serve 90 percent of users with the information they need. The other 10 percent want to generate reports based on their own distinctive way of viewing the data. Satisfying that 10 percent requires a full-fledged, interactive query tool that traditionally has been delivered by client/server reporting tools such as Seagate's Crystal Reports or Borland's ReportSmith. These tools carry the same baggage that any client/server application does. The reasons for switching to a web-based solution are the same for reporting tools as they are for custom applications -- client/server tools require installation on each individual PC and are laborious to configure given the array of middleware connectivity pieces.

When designing The Web Report, my main objectives included:

• Providing a usable and intuitive front end.
• Allowing users to build a query by selecting tables and columns from an HTML form.
• Allowing users to build relationships between tables and columns.
• Allowing users to select rows based on the values for particular columns.

Any web-based tool that makes use of client logic must rely heavily on a scripting language (such as Javascript) that provides the client-side logic necessary to create an intuitive front end. In this example, the server delivers the table names and field names to the browser when it builds the HTML page. The client can then use the table names and field names when it responds to events fired when users do something, such as selecting a field to include in the report. All the events on the client happen without users having to send information back to the server to process. By placing the majority of the logic in the client and reducing the number of trips to the server to retrieve information, The Web Report functions as client/server applications users would expect.

The System Architecture

As Figure 2 illustrates, The Web Report uses Javascript, Active Server Pages (ASP), and DHTML. Alone, each of these can deliver limited functionality to a web application -- combined, they can deliver a tightly knit client/server-looking front end.

Microsoft introduced Active Server with Internet Information Server 3.0. When IIS processes a page with Active Server scripting, it parses the script tags out of the HTML, performs the necessary functions, and transfers the resulting HTML page to the browser.

Once the HTML page is transferred to the browser, Javascript performs the logic. When compared to HTML, DHTML introduces a significantly enhanced document object model. The document object exposes a set of properties, methods, and events for manipulation by scripting languages like Javascript. DHTML expands on the previous object model by introducing a slew of new objects with properties, methods, and events that are beginning to look a lot like those you're are accustomed to using in traditional client/server development environments. This new model makes it possible for you to use scripting languages to respond to user actions more effectively, and to design programs for the Web that more closely resemble what users have come to expect from client/server applications.

Setting Up the Database

Before you can access information about tables from a database, you must allow users to read the system tables. Databases keep track of table names and properties in a set of system tables that it generates for each new database you create. In Access, these tables are referred to as "System Objects" and are hidden by default. To see the system tables, click Tools/Options, then click System Objects in the Show box. All of the system objects begin with "Msys." For The Web Report to get the information it needs, users must be able to read the data from the system tables in MsysObjects, which lists all the table names in the database along with a wealth of other information about the database design. Specifically, The Web Report is interested in the Name and Type fields. All rows with a type 1 are tables in the database.

Once the system objects are visible, you can set the security so The Web Report can read them via an Active Server Page. Click MsysObjects, click Tools/Security/User and Group Permissions, then turn on Read Data. If you want to connect The Web Report to another type of database, check your database documentation for the names, structures, and security options of the system tables.

To allow Active Server Pages to interact with the database, you must create an ODBC system datasource on the server. To do that, double-click the ODBC icon in the Control Panel, click the System DSN tab, click Add, then click the appropriate driver for the database. (Remember that this example uses an Access database.) Once you've chosen a driver, the dialog box pops up for the specific type of database. An access database requires a data source name and location. SQL Server, Oracle, Informix, and Sybase databases will require a host machine (the IP address or DNS name of the host) and a data source name. The data source name is the name you use in your Active Server Pages to access the database. It can be any name you deem appropriate. In this example, I'm accessing the northwind.mdb database, so I named the data source "northwind."

The Active Server Page Code

ASP code is responsible for creating a connection to the database to retrieve information about the tables and fields. Once the information is retrieved, it is embedded into the HTML page and delivered to the browser where Javascript and DHTML take over.

The ASP code creates a connection to the database and processes a SQL statement that asks for the names of the tables in the database; see Listing One (The complete Active Server Page that builds and runs the app is available electronically; see "Resource Center," page 3.)

The <% and %> characters set the ASP code off from the HTML codes and text in the page. The server parses and processes the code between the <% and %> symbols. The table names can now be used to populate an HTML select list. The code in Listing Two cycles through the result set, DBTables. When the server returns the HTML page to the client, it looks like Listing Three.

DHTML and Javascript

After the page has been built using ASP code, Javascript takes control and lets users select the fields they want to include in the report.

When users click on a table in the Tables list, the fields in that table display in the Columns list. If users decide they want to include that field in their report, they click the field name in the list, then click the Add button. That creates a new entry in the columns in report list with the table name and field name.

In Listing Three, you might have noticed the onClick event of the select listbox-named tables called the JavaScript dispCols function: <select name="tables"size=10 onClick=dispCols();>. The dispCols function contains the field names for every table in the select list. The field names are generated by the server when it processes the ASP code; see Listing Four.

ASP code is embedded in this Javascript function. When the HTML page is returned to the client with the database information embedded, it looks like Listing Five. When users click a table name in the Tables-select list, the onClick event fires, which in turn calls the dispCols function. dispCols determines the table name selected in the Tables-select list, then populates the tablecols list with the appropriate field names. While you're looking at the code, keep in mind that "dbLists" is the name of the HTML form, "tables" is the name of the Tables-select list, and "tablecols" is the Columns-select list.

The addColumn function is run when users click the Add button on the Columns tab. The browser recognizes the onClick event of the image and runs the addColumn function: <img src="plus.gif" onClick="addColumn();"><br>.

Running the Report

The only time The Web Report returns to the server for information is when users click the Run It button. At this point, the form contents, most importantly the generated SQL statement, are transferred to the server and processed. As Listing Six shows, the ASP code in results.asp determines the SQL statement from the previous page, connects to the database, retrieves the information, and displays it in columns.

Caveats and Pitfalls

Anyone who's worked in web development knows that, as a full-fledged development environment, the Web is in its infancy. The tools available to produce web-based applications pale in comparison to client/server rapid-application-development tools such as Visual Basic, Delphi, or PowerBuilder. At this stage, the Web poses the following problems for developers:

• Learning different syntactical languages (ASP, Javascript, and DHTML) and using them simultaneously within the same application can confuse even the most organized developers.
• Javascript is cumbersome and often difficult to use in complex coding situations.
• Debugging is difficult due to the lack of good script-debugging tools on the market.

The good news is that the market for good web development tools is forcing vendors to produce.

Conclusion

I have only scratched the surface in explaining all the nuances of The Web Report. Still, the examples I've presented should give you a taste of the potential of a web app designed using DHTML, Javascript, and Active Server Pages.

In this case, The Web Report deals with little data from the server. In cases where there will be larger result sets, different technologies and strategies would be more appropriate than those I've described here.

As always, the key to designing a good web application is determining the best tools and technologies to use for your specific purposes.

DDJ

Listing One

<%    Set DBConnection = Server.CreateObject("ADODB.Connection")
    DBConnection.Open ("northwind")
    SQL="SELECT name FROM MSysObjects WHERE type=1 AND name 
                                            NOT LIKE 'MSys%' ORDER BY name"
    Set DBTables = DBConnection.Execute(SQL)
%>


</p>

Back to Article

Listing Two

            <select name="tables" size=10 onClick=dispCols();>
            <% 
                Do While Not DBTables.EOF 
            %>
                <option> <%=DBTables("Name")%>
            <%
                DBTables.MoveNext
                Loop
            %>
            </select>


</p>

Back to Article

Listing Three

<select name="tables" size=10 onClick=dispCols();>
    <option> Categories
    <option> Customers
    <option> Employees
    <option> Order Details
    <option> Orders
    <option> Products
    <option> Shippers
    <option> Suppliers
</select>


</p>

Back to Article

Listing Four

function dispCols()     {
        var selIx=document.dbLists.tables.selectedIndex;
        var optText=document.dbLists.tables[selIx].text;
        for (var y=0;y<document.dbLists.tablecols.length;y++) 
        {
            document.dbLists.tablecols[y].text='';
        }
        <% 
            DBTables.MoveFirst
            Do While Not DBTables.EOF 
            SQL="SELECT * FROM [" & DBTables("Name") & "]"
            Set DBFields = DBConnection.Execute(SQL)
        %>
        if (optText=='<%=DBTables("Name")%>')
        {
           <%For i = 0 to DBFields.Fields.Count - 1%>
               document.dbLists.tablecols[<%= CStr(i) %>].text='
                                                     <%=DBFields(i).Name%>';
            <%Next%>
        }
        <%
            DBTables.MoveNext
            Loop
            DBFields.Close
        %>
    }


</p>

Back to Article

Listing Five

function dispCols()     {
        var selIx=document.dbLists.tables.selectedIndex;
        var optText=document.dbLists.tables[selIx].text;
    
        for (var y=0;y<document.dbLists.tablecols.length;y++) 
        {
            document.dbLists.tablecols[y].text='';
        }
        if (optText=='Categories')
        {
                document.dbLists.tablecols[0].text='CategoryID';
                document.dbLists.tablecols[1].text='CategoryName';
                document.dbLists.tablecols[2].text='Description';
                document.dbLists.tablecols[3].text='Picture';
        }


</p>

Back to Article

Listing Six

<html><head><title>The Web Report Results</title>


</p>
<STYLE type=text/css>
 .Header
    { 
        position : absolute;
        left: 25;
        top: 15;
    }
 .BodyText
    { 
        position: absolute;
        left=25;
        top=100;
    }
</STYLE>
</head>


</p>
<body bgcolor=#FFFFFF>
<div class="Header">
    <img src=header.gif>
</div>


</p>
<div class="BodyText">
<table border=1>
    <tr>
        <%For i=0 to DBResults.Fields.Count - 1%>
            <td align=center bgcolor=#003366>
                <p style="color:#FFFFFF;background:#003366;font-family:arial;
                        font-size=12pt;font-weight:bold"><%= 
                        DBResults(i).Name %></style>
            </td>
        <%Next%>
    </tr>
<% 
    j=0
    Do Until DBResults.EOF or j=100
%>
    <tr>
        <%For i=0 to DBResults.Fields.Count - 1%>
            <td valign=top>
                <p style="color:#000000;background:#FFFFFF;font-family:arial;
                            font-size=10pt"><%= CStr(DBResults(i)) %></style>
            </td>
        <%Next%>
    </tr>   
<% 
    DBResults.MoveNext
    j=j+1   
    Loop
    DBResults.Close
    DBConnection.Close
%>
</table>


</p>
<p style="color:RED;background:#FFFFFF;font-family:arial;font-size=10pt;
                                       font-weight:bold"><%= SQL %></style>
<%If j>99 Then%>
    <p style="color:RED;background:#FFFFFF;font-family:arial;font-size=10pt;
                  font-weight:bold">For demonstration purposes, 
                  this version of The Web Report returns only 100 
                  records of large recordsets.</style>
<%End if%>
<p>
</div>


</p>
</body>
</html> 


</p>


</p>

Back to Article