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
////// Connect to the Web
Server (StdIn/StdOut)
// insert your address as
appropriate
//// define the fields for
each column in the table
//// define the captions for
each column heading
/// define data access properties
/// call the lookup method
to activate the query.
/// call
the init method to setup the fields.
/////////////////////////////////////
//////////////////////////////////////////////////////////////
else // no records found
catch (exception e) oCGI.errorPage(e) endtry
|
|
This program will produce an HTML page that looks similar to the following.
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
// Center the table on the page
// Align the 1st column on the right of
the cell
// Center the heading and data in column
2
// Set all the table header cells to a
light yellow background.
// Set the width of each column
// Set the background color of column 1
to a light gray.
// Set the color of the font.
// Set the font size to one less than the
browser's default
// Set the font to one of three sans-serif
faces
|
|
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)
cTitle = iif(empty(Title),'Response
Page',Title)
////// Start page body
////// Header
inside chrome band across page
////// Blank Line
|
|
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.