Creating Reports for the Web: WebReports.cc
by Michael Nuwer

THIS ARTICLE explores the usage of a custom class designed to produce HTML pages containing dynamic data. The class is called WebReports.

Many dBASE users use a`.REP` file to create their Web reports. The advantage of this approach is that the report can be laid out with the dBASE Report Designer, which is a familiar tool. When a`.REP` file outputs to a HTML file or as a CGI Response, dBASE converts the report layout into an HTML table. The problem with this conversion is that the developer has very little control over how the HTML is generated. There is no mechanism for defining any of the tag properties. This makes it difficult to integrate the appearance of the report with the appearance of a wider web site.

The WebReports class that we explore in this article is an alternative approach for creating dynamic Web pages. It builds a CGI response directly within an HTML table. This offers the user a high degree of control over the layout and appearance of the output. In addition, the class is designed such that the output is built from components. Each component is a method of the class and, therefore, can be overridden in a sub class. You can, for example, add a complex navigation template by subclassing one method.

`WebReports.cc` is included in the archive file that accompanies this article. Also included in this archive is a WebReports Builder. This application is a desktop interface that will let you choose many of the options available in the WebReports class and it will write a PRG file for you. We are not going to discuss the interface in this article (the application includes help files that will do that for you). Instead we are going to focus on the details of the WebReports class. This discussion will take us beyond the functionality offered by WebReports Builder.

Usage

Let’s start with a simple CGI response page. This page could be a listing of names and addresses from a single DBF table. Later we will introduce other features included in WebReports, but they are no more than extension of the basic structure.

`WebReports.cc` is subclassed from the dBASE `WebClass.cc`. Therefore the structure of the web application is the same as any Web application developed with `WebClass.cc` (for details see my earlier article on this subject).

Program Example

| `Try // Error trap entire CGI applet.` `////// Create new instance of the Web Class` `Set proc to WebReports.cc additive //subclass of WebClass.cc` `oCGI = new WebReportsCGISession ()` `////// Connect to the Web Server (StdIn/StdOut)` `oCGI.Connect()` `// insert your address as appropriate` `oCGI.setWebMasterAddress("webmaster@mailaddress.com")` `//// define the fields for each column in the table` `aFieldArray = new Array()` `aFieldArray = {"LName","FName","City","Country"}` `//// define the captions for each column heading` `aCaptionArray = new Array()` `aCaptionArray = {"Last Name","First Name","City","Country"}` `/// define data access properties` `oCGI.Database = "MYALIAS"` `oCGI.Table = "Customers.dbf"` `oCGI.IndexName = "CustomerName"` `/// call the lookup method to activate the query.` `if oCGI.Lookup()` `/// call the init method to setup the fields.` `oCGI.init(aFieldArray)` `/////////////////////////////////////` `// Set the custom properties` `/////////////////////////////////////` `oCGI.bAlternateShading = false` `oCGI.homePage = "http://your.website.com"` `//////////////////////////////////////////////////////////////` `// Call response page` `//////////////////////////////////////////////////////////////` `oCGI.ResponsePageTop("Customer List")` `oCGI.ResponsePageTable(aCaptionArray,aFieldArray)` `oCGI.ResponsePageBottom()` `else // no records found` `/// call sorrypage here` `oCGI.SorryPage("No Records found")` `endif` `catch (exception e)` `oCGI.errorPage(e)` `endtry` `/// cleanup our mess` `oCGI = null` `aCaptionArray = null` `aFieldArray = null` `////// all done` `Quit`
| ``

This program will produce an HTML page that looks similar to the following.

My Report

Heading | Heading | Heading | Heading
Detail | Detail | Detail | Detail
Detail | Detail | Detail | Detail
Detail | Detail | Detail | Detail

The program begins by creating a new instance of the Web Reports Class and calling the `connect()` method. After that two arrays are defined. The first array,`aFieldArray[]`, identifies the fields that will be printed on the response page. The second array,`aCaptionArray[]`, is used to define the text for the column headings. Following these two arrays you must set the database, table name and index name.

After you have declared the data components for the report, the `lookup()` method must be called. This method activates the WebReports query object. In addition, when your WebReport is a Query and Response application, the `lookup()` method will also set the report constraints and determine if the search value exists (there is more on this in the next section).

Following the `lookup()` method, the `init()` method is called. This method calculates the column widths for each of the fields and sets the HTML default values for each of the table cells. After the HTML table is initialized, you can set any of the custom formatting properties. The program example above only sets the `homePage` property for your web site. A full list of the properties is discussed in the sections that follow.

Finally, three methods are called to product the HTML response page —`ResponsePageTop()`,`ResponsePageTable()` and `ResponsePageBottom()`. Three methods are used so that you can customize your page by overriding the `ResponsePageTop()` and/or `ResponsePageBottom()` methods (this too is discussed later in the article).

The Data Source

In the above example program, a single table and an index tag is used. In addition to a single table, the WebReports class also supports an SQL string. Using an SQL string in place of a table is particularly useful when your report needs to access data from multiple tables. In this case you can use a SQL join statement. The SQL string can be created with the dBASE SQL designer or you can use my SQL Query Builder.

So, if you use a single table as your data source, you will typically set the following three properties.

| `oCGI.dataBase` `oCGI.table` `oCGI.indexName`
| ``

However, if you use a SQL string as your data source, you will set these two properties.

| `oCGI.dataBase` `oCGI.sqlString`
| ``

There are a few things to keep in mind about the two options above. When your report is based on a dBASE table you can use an index tag name. In this circumstance WebReports will use the `setRange()` method to constrain the data in your report. Using the `setRange()` method is generally the fasted way to constrain your data, but it requires an active index.

When you use a SQL string as the data source, you will not be able to set an index tag name. In that case you will need to use the SQL `order by` clause to sort the data. You can also use the SQL `where` clause when you need to constrain your data, or you can let WebReports set the constraint with the rowset filter property.

A Query and Response Report

Among the most common type of Web reports is the Query and Response report. The idea is that a Web user is presented with a HTML form in which they can enter a parameter for restricting the report data. For example, a user may wish to view orders for a single customer or details about a specific product in inventory. In the query part of this system, the user must enter a Customer ID or a part number and submit the form. Your dBASE web application receives this parameter, selects the corresponding data, and streams it back to the web user in a response page.

Both the dBASE Web Wizards and the WebReports class offer support for Query and Response reports.

Typically, the Web form that makes up the Query end of this system contains a HTML entryfield or a combobox. The name of this object is `SEARCH` (all upper case). Although the dBASE Web wizard established this naming convention, you can also use it in a WebReports application.

In your program file, the value for `SEARCH` can be retrieved from the CGI array and assigned to a property of the WebReports object. Something like following:

| `oCGI.Connect()` `oCGI.setWebMasterAddress("techsupport@myaddress.com")` `if oCGI.isKey('SEARCH')` `oCGI.RangeValue = oCGI['SEARCH']` `endif`
| ``

With WebReports you have the choice of using the `setRange()` method or the rowset filter property for constraining your data. The above example is used by the `setRange()` method within the WebReports class. If you would rather use the filter property, or if you can not use the `setRange()` method, then your code would look something like this:

| `if oCGI.isKey('SEARCH')` `oCGI.filter = [CustomerID = ] + oCGI['SEARCH']` `endif`
| ``

Use the `RangeValue` property when an index is in effect and all your data is in the same table; use the `filter` property when you access data from multiple tables with an SQL join. Do not use both the `RangeValue` and the `filter` properties in the same report. If you set one of these properties leave the other one un-set or set it to `NULL`. You should set the `filter` or `rangeValue` properties before calling the `lookup()` method.`Lookup()` not only sets the constraint, but also validates that data exists for the search parameter.`Lookup()` returns `true` if a value does exist and `false` if it does not.

The `filter` property is set to match partial length and to ignore the case (i.e.`filterOption=3`). This is so the user gets a list of all items that start with whatever characters are entered.

A Report Group

You can group the data in a WebReport by setting two properties. First the `GroupBy` property must be set to `true` and second the `GroupField` property must be set to the fieldname that will identify each group.

| `oCGI.GroupBy = true` `oCGI.GroupField = "FieldName"`
| ``

When you group the data in your WebReport, be sure the data source is in the appropriate order. As WebReports loops through the data source it will create a new group each time there is a change in the value of the `GroupField`. Therefore, the primary sort order of the data must be by the field used in `GroupField` property. If you use a dbf table you can use an index to sort the data for grouping. If you use an SQL string, you must have an `order by` clause that sorts the data.

In a WebReport, the group footer is optional. To display the footer set the `printFooter` property to `true`.

| `oCGI.printFooter = true`
| ``

A WebReport with grouped data will look similar to the following table:

Group Header
Heading | Heading | Heading | Heading
Detail | Detail | Detail | Detail
Detail | Detail | Detail | Detail
Detail | Detail | Detail | Detail
Group Footer

When active, the footer band will print a count of the number of rows in the group. Later in this article we will discuss how to customize the group header and footer.

A Drill Down Report

Web applications do not have live data grids and you cannot use an incremental seeker control. The Web alternative to a grid and a seeker is to use a lookup table with a drilldown link. The lookup table presents the user with a list of items, perhaps a list of customers, from which the user can select one item. The first column of the table contains hyperlinks which contains a call to a CGI program and a parameter that identifies the selected item. A drilldown page looks something like the following.

Last Name | First Name | City
Allison | Eric | San Francisco
Bowles | Michelle | Los Angeles
Johnstone | Daniel | San Diego

This table presents a list of items from which the user can make a selection. The user may, for example, click the link for Daniel Johnstone. That hyperlink might look something like the following

| `<A href=MyApp.exe?SEARCH=27>`
| ``

My `App.exe` is the server-side application that will generate the detailed information and `SEARCH=27` is the name/value parameter used to find Daniel Johnstone’s information. A WebReports response page for this drilldown might look like following.

Detail information for Daniel Johnstone 2756 El Rancho Road San Diego, California
Date | Description | Status
Detail | Detail | Detail
Detail | Detail | Detail
Detail | Detail | Detail
Number of items: 3

To create a drilldown report you must add the following properties to the custom properties section of your program.

| `oCGI.drillDown = true` `oCGI.DrillDownCommand = "MyApp.exe"` `oCGI.drillDownField = "CustomerID"`
| ``

`DrillDownCommand` is the CGI program file name that is executed by the hyperlink.`drillDownField` is the field from your table or query that contains the key value to lookup. Normally this is the key value.

In the example above, the first column in the table displays customer names. If you would rather display a label, like “Add Order,” you can set the property called `DrillDownLabel`. The text of this property will replace the field value.

Custom Formatting Properties

The WebReports class uses custom properties to format the HTML table in your response page. We have endeavored to keep these properties identical to their corresponding HTML property. For example, in HTML, the table’s background color is specified as`<TABLE BGCOLOR="#rrggbb">`. As a custom formatting property for the WebReports class, the table’s background color is specified as `TableBGColor="#rrggbb"`. Class properties are constructed by combining the HTML tag name with the tag property (Table+BGColor). The valid values for these custom formatting properties are identical to their HTML counterparts.

WebReports uses three HTML tags:`<TABLE>`,`<TD>`, and`<FONT>`. The following is a list of the custom formatting properties used in this class.

| Properties for <Table></Table> |
| TableAlign | Specifies the horizontal placement of the table. Valid values are: "LEFT|RIGHT|CENTER"
| TableBGColor | Sets the color of the background for the table. This color can be overridden by a BGCOLOR tag in the TH or TD tags. Valid values are: "#rrggbb"
| TableBorder | Indicates the thickness, in pixels, of the border to draw around the table. Give the value as an integer. A value of 0 means the table has no border.
| TableCellpadding | Determines the amount of space, in pixels, between the border of a cell and the contents of the cell.
| TableCellspacing | Determines the amount of space, in pixels, between individual cells in a table.
| TableWidth | Defines the width of the table. The width value can be a number of pixels, given as an integer, or a percentage of the width of the page, given as an integer followed by the percent sign.
| TableHSpace | Specifies the distance between the left and right edges of the table and any surrounding content.
| TableVSpace | Specifies the distance between the top and bottom edges of the table and any surrounding content.
| Properties for the Table Header (TH) and Table Data (TD) | The <TD></TD> tag is used for both the header row and the data rows. However the header row can be formatted differently from the data rows. These Properties are initialized as arrays with the same dimension as aFieldArray. This way each element in the array will set a property for a full column in the response table.
| aTDAlign[n] and aTHAlign[n] | Specifies the horizontal placement of the data inside the table cell. Valid values are: "CENTER|LEFT|RIGHT".
| aTDValign[n] and aTHValign[n] | Specifies the vertical placement of the contents of the cell. Valid values are: "BASELINE|BOTTOM|MIDDLE|TOP"
| aTDBGColor[n] and aTHBGColor[n] | Sets the color of the background of the table cell. Overrides the TableBGColor.
| aTDWidth[n] and aTHWidth[n] | Specifies the suggested width of the table cell. The width value can be a number of pixels, given as an integer, or a percentage of the width of the table, given as an integer followed by the percent sign.
| aTDNowrap[n] and aTHNowrap[n] | Specifies that the lines within a cell cannot be wrap onto the next line. This property should not be to set as "NO" Valid values are: "DEFAULT|YES"
| Font Properties |
| TDFontName and THFontName | Specifies a comma-separated list of font faces.
| TDFontColor and THFontColor | Defines the desired text color.
| TDFontSize and THFontSize | Defines the relative size of the font, in a range from 1 to 7, with a default size of 3. A value of 1 results in the smallest font, and a value of 7 results in the largest font. You can also specify the size using a plus or minus sign in front of the number to change the size with respect to the base font size.
| TDFontBold and THFontBold | Defines the text as bold. This property should not be to set as "NO" Valid values are: "DEFAULT|YES"
| TDFontItalic and THFontItalic | Defines the text as italic. This property should not be to set as "NO" Valid values are: "DEFAULT|YES".

In your Web application, you should set these properties after calling the `init()` method. The following is an example.

| `oCGI.init(aFieldArray)` `////////////////////////////////////` `// Set a few custom properties` `/////////////////////////////////////` `// set the table width to 80% of the page` `oCGI.TableWidth = "60%"` `// Print the table with no borders around the cells` `oCGI.TableBorder ="0"` `// Center the table on the page` `oCGI.TableAlign = "CENTER"` `// Align the 1st column on the right of the cell` `oCGI.aTHAlign[1] = "RIGHT"` `oCGI.aTDAlign[1] = "RIGHT"` `// Center the heading and data in column 2` `oCGI.aTHAlign[2] = "CENTER"` `oCGI.aTDAlign[2] = "CENTER"` `// Set all the table header cells to a light yellow background.` `for i=1 to 4` `oCGI.aTHBGColor[i] = '#FFFFC6'` `next` `// Set the width of each column` `oCGI.aTHWidth[1] = "20%"` `oCGI.aTDWidth[1] = "20%"` `oCGI.aTHWidth[2] = "25%"` `oCGI.aTDWidth[2] = "25%"` `oCGI.aTHWidth[3] = "30%"` `oCGI.aTDWidth[3] = "30%"` `oCGI.aTHWidth[4] = "25%"` `oCGI.aTDWidth[4] = "25%"` `// Set the background color of column 1 to a light gray.` `oCGI.aTDBGColor[1] = "#E0E0E0"` `// Set the color of the font.` `oCGI.TDFontColor = "BLUE"` `// Set the font size to one less than the browser's default` `oCGI.TDFontSize = '-1'` `// Set the font to one of three sans-serif faces` `oCGI.TDFontName = "Georgia,Arial,Helvetica"`
| ``

A Formatting Sample

The HTML table object includes a `border` property, however, many Web page designers create a border affect without using this property. In the following table there appears to be a white border for each cell.

First Name | Last Name | City | Country
Monica | Wang | Freeport | Bahamas
Nora | Buzza | Kato Paphos | Cyprus
Ron | Gillaspy | Paget | Bermuda

Using the following WebReports properties creates this affect.

| `oCGI.TableBGColor= "#FFFFFF"` `oCGI.TableBorder = "0"` `oCGI.TableCellpadding = "2"` `oCGI.TableCellspacing = "2"` `for i = 1 to 4` `oCGI.aTDBGColor[i] = "#CCCCFF"` `next`
| ``

The basic idea is to use the table background color as the border color. Then set the cell spacing to one or more pixels and overlay a cell background color. This way the table background color “lacks through” only between the cells.

Alternate Row Shading

Setting the `bAlternateShading` property to `true` will activate alternate row shading in the data band of a WebReport. The default alternate color is white, but you can assign a different color by setting the `AlternateColor` property.

| `this.bAlternateShading = true` `this.AlternateColor = "#FFFFFF"`
| ``

Heading | Heading | Heading | Heading
Detail | Detail | Detail | Detail
Detail | Detail | Detail | Detail
Detail | Detail | Detail | Detail

Customize the Report Components

You can customize the elements of your page that print above the main table by subclassing WebReports and overriding the `ResponsePageTop()` method. You may, for example, have a common page header which contains a logo and some navigation links. These can be streamed in an overridden `ResponsePageTop()` method and your common header will be merged with your WebReport.

The following is a simple example of a customized `ResponsePageTop()` method.

| `Class myWebReport of WebReportsCGISession from "WebReports.cc"` `Function ResponsePageTop(Title)` `/////// Stream out header` `cTitle = iif(empty(Title),'Response Page',Title)` `with (this.fOut)` `puts('Content-type: text/html')` `puts('') /// This line is REQUIRED!!!` `puts('<HTML>')` `puts('<HEAD>')` `puts(' <META HTTP-EQUIV="Content-Type" CONTENT="text/html">')` `puts(' <TITLE> '+cTitle+'</TITLE>')` `puts('</HEAD>')` `////// Start page body` `puts('<BODY BGCOLOR="#FFFFFF">')` `puts('<P>')` `puts('<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%">')` `////// Header inside chrome band across page` `puts('<TR><TD BGCOLOR="#CCCCCC">')` `puts('<H3><FONT COLOR="#666666">Your Search Results for ' + this['SEARCH'] + '</FONT>')` `puts('</TD></TR>')` `////// Blank Line` `puts(' <TR><TD>&nbsp;</TD></TR>')` `puts('<TR><TD COLSPAN="2" Align=center><H3>' + cTitle + '</H3>')` `puts('</TD></TR>')` `puts('<TR><TD COLSPAN="2" Align=center><H3>Information Lookup System</H3>')` `puts('</TD></TR></TABLE>')` `endwith` `return` `endclass`
| ``

The above code will generate a table at the top your the page that looks similar to the following:

### Your Search Results for mySearchValue
### My Title
### Information Lookup System

You can do the same type of thing with the `ResponsePageBottom()`. Perhaps you need to add some copyright information at the bottom of your page. Or you may have some site wide links at the bottom. In any case, you can add this to a WebReport by subclassing the WebReports class and overriding the `ResponsePageBottom()` method.

Custom Group Bands

When you use a Group in a WebReport, the header band will, by default, print the value of the `groupby` field. The footer band will print the count of the number rows in the group. You can customize these elements by overriding the methods that stream the bands. To customize the group header, you should override the `printGroupHeader()` method.

Streaming the group header can be a bit tricky. This is because the header band is embedded within the wider table. The best approach is to design the contents of your custom header band in a HTML table. The following are two examples of possible group headers.

Header example one:

Michael Nuwer
21 Haggerty Road
Postdam, NY 13676
USA

Header example two:

Name: | Michael Nuwer
Address: | 21 Haggerty Road
City, State, Post: | Postdam, NY 13676
Country: | USA

The tricky part is to embed this type of header table within the master table. The best way to do this is to create a single cell which spans all the columns in the master table. The contents of this cell will them be the header table. Here is the code

| `puts([<TR><TD BGColor="#FFFFFF" COLSPAN="]+ltrim(str(this.aFields.size))+[">] ) // Insert a header table here puts([</TD></TR>])`
| ``

You can then insert the header table in this cell:

| `puts([<TR><TD BGColor="#FFFFFF" COLSPAN="]+ltrim(str(this.aFields.size))+[">] ) // Start header table puts('<TABLE border=1 width="100%">') // Concatenate the first and last name cValue = this.GetFieldValue(firstName) cValue += " " + this.getFieldValue(lastName) // Stream the first row puts('<TR><TD width="20%">Name:</TD><TD width="80%">'+cValue+'</TD></TR>') // Retrieve and stream address information cValue = this.GetFieldValue(Address) puts('<TR><TD width="20%">Address:</TD><TD width="80%">'+cValue+'</TD></TR>') // Concatenate the city, state, and postal fields cValue = this.GetFieldValue(City) cValue += ", " + this.getFieldValue(State) cValue += " " + this.getFieldValue(Postal) // Stream the row puts('<TR><TD width="20%">City, State, Post:</TD><TD width="80%">'+cValue+'</TD></TR>') cValue = this.GetFieldValue(Country) puts('<TR><TD width="20%">Country:</TD><TD width="80%">'+cValue+'</TD></TR>') // end of header table puts('</TABLE>') // Close cell in master table puts('</TD></TR>')`
| ``

Embedding a header table within the master table give you the widest flexibility for laying out the header. You can place text and field values just about anywhere in the header. You can also create the HTML table with your favorite HTML editor.

The same principles apply to customizing the group footer. In this case you should override the `printGroupFooter()` method. It is, however, common to print summary information in a group-footer. To do this with WebReports you need to subclass three methods so that you can maintain your counters and/or aggregators. A bare bones example follows.

| `Class myWebReport of WebReportsCGISession from "WebReports.cc" Function printGroupHeader this.counter = 0 this.summation = 0 return Function aggregation this.counter ++ this.summation += this.q.rowset.fields['NumberField'].value return Function printGroupFooter with (this.fOut) /// print footer cell puts([<TR><TD BGColor="#FFFFFF COLSPAN="]+ltrim(str(this.aFields.size))+[">]) puts("<P> The Count is: " + this.counter ) puts("<P> The Sum is: " + this.summation ) puts([</TD></TR>]) endwith return endclass`
| ``

In the `printGroupHeader()` you must set your counter or aggregator to it’s initial value. In the example above the `counter` property will count the number of rows for each group and the `summation` property will sum a numeric field. Both properties are set to zero when the group header is printed. WebReports is designed to call a method named `aggregation()` each time it finishes processing a row in the rowset. Overriding this method lets you define how you want your counters and aggregators to work. In the example above, the `counter` property is incremented and a field value is added to the `summation` property. You can, of course, add conditionals here too.

The Final Word

The main reason for using `WebReports.cc` is that it affords the developer greater control over the layout of a CGI response page than can be achieved with a`.REP` file. You can create simple reports by using the default properties in WebReports. You can also use the WebReports Builder to create you program files. If the Builder doesn’t do all that you need you can edit the resulting program file and you even subclass WebReports to create your desired response page.

`WebReports.cc` was originally inspired by John Staub’s Creating an HTML Table Response Page for the Web. John also helped develop the early version of this class. The class has evolved quit a bit since then, which makes me responsible for any of its shortcoming, nonetheless, a special acknowledgment must be made to John Staub.