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 extend 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 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
Using 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 = trueA WebReport with grouped data will look similar to the following table:
|
|||
| 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>
MyApp.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 |
||
| 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 boarder affect without using this property. In the following table there appears to be a white boarder 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> </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 |
|
| |
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.