Reporting in Visual FoxPro

Reporting in FoxPro is deceptively simple. The FoxPro Report Designer is activated when you type

 MODIFY REPORT (ReportName) 

or

 CREATE REPORT (ReportName) 

either of which does the same thing. To print a report, you use the command

 REPORT FORM (name) TO PRINT PREVIEW NOCONSOLE 

The "official" syntax is shown in Listing 10.1.

Listing 10.1. The Report Form Command Syntax

 REPORT FORM  FileName1  ? [ENVIRONMENT] [  Scope  ]    [FOR  lExpression1  ]    [WHILE lExpression2]    [HEADING  cHeadingText  ]    [NOCONSOLE]    [NOOPTIMIZE]    [PLAIN]    [RANGE  nStartPage  [,  nEndPage  ]]    [PREVIEW [[IN] WINDOW  WindowName  IN SCREEN] [NOWAIT]]    [TO PRINTER [PROMPT]  TO FILE  FileName2  [ASCII]]    [NAME  ObjectName  ]    [SUMMARY] 

Some of this syntax is obsolete or leads to bad practices. For example, FOR is a filter expression, and WHILE stops the report when the WHILE condition is no longer satisfied. Neither one is the right way to decide how to include data in a report; rather, ordering and filtering should be done before you call the report.

Similarly, the PLAIN and TO FILE TextFileName ASCII options that produce a primitive text output have long since been replaced with Excel, HTML, and other types of output; the RANGE command is a command-line device to print only certain pages as a hands-on filtering mechanism. So if you're using most of these options, there's probably a better way to do what you want to do. And if your report is running so slowly that you feel the need to include the NOOPTIMIZE keyword, there's definitely a better way to fix what's wrong.

However, three of these keywords are used almost every time:

• The PREVIEW keyword lets users see their report before they decide to print it.

• NOCONSOLE prevents the output from being duplicated on the _Screen surface, which is never desirable.

• The SUMMARY option prints only the summary lines; detail lines are suppressed.

So you have one command to create the report and one command to display the report. However, don't let the apparent simplicity fool you. For many years , FoxPro has provided an easy and cost-effective way to report on tabular data.

Internal Details

Reports in Visual FoxPro are stored in a pair of tables with the extensions .frx and .frt . Because they're FoxPro tables, you can make changes programmatically or manually to the contents of these tables. Hacking system tables has its risks, but the open nature of FoxPro has long endeared it to developers. So take the usual precautions to back files up first, and you can do just about anything you can imagine. That's the basis of GenRepoX.PRG , the amazing pre- and post-processing utility written by Markus Egger. We'll look at GenRepoX later in this chapter to give you some ideas. After that, it's up to you.

Report Layout in FoxPro

The FoxPro Report Designer has bands for Title, Summary, Header, Footer, Group , and Detail bands. To design a report, you create a basic layout, then add bands and grouping to correspond to data ordering or indexing that will be done before the report is run. The resulting interaction between the report file, the FoxPro report processor, and the data is what gives you the end product.

Type CREATE REPORT SimpleReport1 to open a new, blank report, as shown in Figure 10.1.

Figure 10.1. The FoxPro Report Designer.

When you create a new report, the only bands that are added are Page Header, Detail, and Page Footer. A Report menu pad is added to the FoxPro system menu _MSYSMENU , making additional options available. In FoxPro 8 and higher, a Printer Environment selection has been added. Also, two toolbars ”Report Controls and Report Designer ”are available and meaningful in the context of the Report Designer.

Page Setup

The default page layout of a FoxPro report is a single column, the width of which is determined by the characteristics of your default printer. If you select File, Page Setup from the FoxPro IDE menu to open the Page Setup dialog, you can easily change a report to print two or more columns , or change column margins. You can also tweak printer settings and change the target device if you're building a report that must run on a particular printer, for example, a dot matrix printer with NCR (no carbon required) multicopy forms.

Within the Page Setup dialog, you can also select Print Setup and specify a Landscape report if your printers permit it. If you print more than one column of records, you can choose either row-major (left to right, top to bottom) or column-major (top to bottom, left to right) order.

Printer Problems

FoxPro embeds the name of the printer that you choose, or the name of your default printer if you don't choose one, into your reports. This leads to one of the most annoying problems with the FoxPro Report Designer. When FoxPro builds a report, it embeds the name of the printer for which it was designed into the report layout. The report may not print properly if a user tries to print on a different printer.

As a result, it's necessary to "hack" the report file and remove printer-specific information before delivering it to users. To do that, type USE ( Reportname .FRX) and BROWSE the file. Then use Ctrl+PgDn to open the Expr field in the first line (the one that has ObjType = 1 and ObjCode = 53 ) and remove the contents of the Expr , Tag1 , and Tag2 fields. If you open and save the file again in the Report Designer, you'll have to do this again. You might want to write a little program to open all of your reports and search for this problem.

If you want to allow users to specify a printer, use SET PRINTER TO SYS(1037) . If you know the exact printer name, you can use SET PRINTER TO NAME :Name" . If it's a network printer name, include the \\ComputerName\ prefix.

Finally, SET(:PRINTER") returns "On" or "Off"; SET(:PRINTER",2) returns the default Windows printer; and SET(:PRINTER",3) returns the default FoxPro printer selected using the SYS(1037) dialog. You don't have to use the command SET PRINTER ON to call the REPORT FORM ( name ) TO PRINT NOCONSOLE command.

Report Controls

In each band , you can drag and drop any of the report controls onto the report. Controls include labels, fields, lines, rectangles, rounded rectangles, and ActiveX (OLE) bound controls (for example, pictures).

Labels are for displaying text that never changes. Report controls are a little different from form controls. On a form, you might use a label to display values of variables ”a report title or a group heading. In the Report Designer, labels are constants. When you put one on a form, you immediately start typing the label's caption. You can't assign it a different caption later.

Fields, on the other hand, can be assigned a value either automatically via binding or programmatically. Fields can be bound to columns in a cursor, to memory variables created before you run the report, or to report variables created and managed by processes within the report.

We usually think of fields as the contents of our tables. But a field in a FoxPro report is any data that might change. For example, the date and time of the report can be included by adding fields with DATE() and TIME() as the contents. (You can restrict the length of these two items best if you use a monospaced font ”for example, Courier New ”and set their lengths to 10 and 5 characters respectively. You also have to control the nature of the Date display using the SET CENTURY and SET DATE commands in your code.) Internal variables such as _PAGENO (the current report page) can also be used on your report pages.

Fields don't have to have a tablename as a prefix, and in fact, it's often the last thing you want to do. In the past, FoxPro developers had the habit of dragging tables into the Data Environment, then onto the screen. This stored a hard-coded reference to both the tablename and the field name into the ControlSource of each field. If you subsequently wanted to use a SQL SELECT statement to get the data, you'd have to go through the properties of the fields in the report and remove "FieldName." from each ControlSource . Because multiple field properties are stored in a single memo field, there's no really easy way to automate this process. It's easier to avoid the problem than to have to repair it later. So I recommend making a habit of entering expressions without a table prefix.

Lines and rectangles can be used to make a plain report look crisp and professional. If you put a line above each group heading, groups are set off in a way that's clearer for users to understand. Rectangles that start in the Page Header band and end in the Page Footer band, or rectangles that start in the Group Header band and end in the Group Footer band, automatically expand to include detail bands within them. Nested rectangles for pages and groups, separated by three or four pixels, create a very professional look with little effort.

OLE bound controls tied to images in a database provide a very simple mechanism for displaying pictures in reports. Given the widespread availability of inexpensive color printers, you can easily print beautiful color catalog pages, until you realize that you're being taken to the cleaners by the toner vendors .

Title and Summary Bands

From the Report menu, select the Title/Summary pad. You'll see the dialog shown in Figure 10.2.

Figure 10.2. The Title and Summary band dialog.

The Title and Summary bands, if requested , print only once. As you might surmise, the Title page prints at the beginning of the report, and the Summary prints at the end of the report. The only option for these bands concerns your decision to print title and summary pages as separate pages. If they're small, they probably shouldn't be printed separately. They'll just appear at the top of the first page and the bottom of the last page, respectively.

Data Grouping

If you select Data Grouping from the Report menu pad or from the Report Controls toolbar, you can take advantage of any data sorting that might have been done as of the moment the report runs. If you select Data Grouping, you'll see the dialog shown in Figure 10.3.

Figure 10.3. The Data Grouping dialog.

The Expression Builder browser (the command button with the three little dots on it) opens up a dialog (see Figure 10.4) that lets you type in an expression, which can be a function, a user-defined variable or function, a system variable, or the name of a field from any table that has been opened in the report's Data Environment. Tables that are merely open at design time are not shown in the Fields list.

Figure 10.4. The Expression Builder dialog.

Notice that the Fields list is empty in this dialog. The Fields list only shows tables that are opened in the report's Data Environment. So if you use a SQL SELECT statement in the BeforeOpenTables event of the report's Data Environment, you'll have to remember, manually enter, and correctly spell all field names. The FoxPro Report Writer has notoriously obscure error messages where misspelled field and variable names are concerned .

Group expressions are presumably fields in the report data that have been used in ORDER BY expressions, or are part of the active index tag(s). Generally, these expressions mirror the current order. For example, if your SELECT statement was

 SELECT * FROM Customers ORDER BY Country, Region, CompanyName INTO CURSOR C1 

your GROUP BY expressions would be Country and Region. (The third ORDER BY expression just alphabetizes the data in the report.) You'd have two group bands, a major group based on Country, and a minor group based on Region (state).

However, you can include the name of a report variable that's calculated as the records are processed . For example, suppose you add a variable called StateName and define it using the following expression:

 IIF ( Country="USA", Region, "" ) 

If you then use StateName as the Group By variable, you won't see any breakdown of departments, provinces , or other subnational groupings except for customers in the United States.

Variables

Report variables in FoxPro are special creatures that are calculated by FoxPro. They are created during the report and still persist after the report has run unless you check the Release After Report check box in the Report Designer. They can be initialized from any source ”system variables, memory variables declared before the report is run, values calculated by counting records, a constant, or a calculation based on the contents of a particular field. They can be automatically reset to zero or blank when a given GROUP BY expression changes; thus they're useful in including subtotals and counts in reports. They can also perform other calculations or call user-defined functions (UDFs) to do just about anything.

A Simple Example

For the examples in this chapter, we'll use the tables from the FoxPro sample data directory (HOME() + "Samples\Data\") . Because reports are read-only, you can issue the command

 SET PATH TO ( HOME() + "Samples\Data\" ) 

to ensure that your programs can find the tables when you USE them.

Start FoxPro and open the Customers table. Type this command:

 CREATE REPORT Customer FROM Customers COLUMN 

This creates a primitive report with one column for each field in the current alias until the fields no longer fit. Obviously, it's a pretty primitive approach, but it works. You can specify which fields to include by adding FIELDS FieldList where FieldList contains field names to include, separated by commas, after the word COLUMN . Here's an example:

 CREATE REPORT Customer FROM Customers COLUMN ;  FIELDS Cust_ID, Company, Contact, Title, Address, City, Region, PostalCode 

But what you get needs some serious cosmetic attention before it can be used, as can be deduced from the resulting report shown in Figure 10.5.

Figure 10.5. A QuickReport based on the Customers table.

Adding Groupings

Usually you would create groupings of tabular data that render it more meaningful. For example, let's say you create a cursor of Orders for last month, including customer name from the Customers table, from the Solutions\Data directory in the Samples\Data subdirectory of the FoxPro home directory (see Listing 10.2).

Listing 10.2. Selecting Data for SimpleReport1.FRX

 SELECT ORDERS.*, Customer.* ; FROM ORDERS, CUSTOMER ; WHERE ORDERS.Cust_ID = CUSTOMER.Cust_ID ; ORDER BY ;   Customer.Country, ;   Customer.Region, ;   Customer.Company ;  INTO CURSOR C1 

This orders the contents of the cursor alphabetically by country, then alphabetically by region (state) within each country, and finally alphabetically by company name within each region. It would probably be interesting to the user to know how many customers there are per state and per country. By creating data groupings corresponding to the ORDER BY clause, you cause the report to include an inner group header and group footer for each region within each country, and a group header and group footer pair outside of the Region group header/footer for the Country group.

Calculated Expressions

Because grouping often goes hand in hand with calculating counts and totals, when you put a field on a summary line, you can right-click on it, select Properties, and select a calculation to be performed on the field that is the object of the control. For example, if the rightmost column in each detail row is the order_amt for each order, it would be useful to copy and paste the order_amt field to the group footer line, placing the copy exactly below the original order_amt field. Open the Calculate Field dialog shown in Figure 10.6 to select the calculation type (sum, count, and so on). Note that you need to reset totals to zero whenever the grouping variable changes in value.

Figure 10.6. Calculated data group band fields.

The report layout shown in Figure 10.7 shows a report with subtotals by region within country.

Figure 10.7. The Customer Orders report grouped by region within country seen in the Report Designer.

If you select View, Preview from the menu, you can see that the report indeed produces the expected report, shown in Figure 10.8. However, it's not a very attractive report.

Figure 10.8. The Customer Orders report grouped by region within country.

To improve this report, we can draw column divider lines and add rectangles around the entire report. Draw the rectangle so that it starts on the Page Header and ends on the Page Footer. In Figure 10.9, I've added a pair of nested rectangles from the page header to the page footer (don't forget to leave a half inch or so at the bottom of the page), and a horizontal line under the text in each of the groupings and under the Grand Total line. I also added a vertical line between the top and bottom of the inner rectangle to separate the two data columns.

Figure 10.9. Making cosmetic improvements to the customer orders report in the Report Designer.

The result (see Figure 10.10) is a much more attractive report. And it's not just a cosmetic improvement after all; I find it a lot easier to read.

Figure 10.10. Previewing the customer orders report after modifications.

Grouping Using a Report Variable

You're not limited to counts and totals: If you want to display the highest order amount by country, you declare a variable called MaxOrder , set it to MAX(Orders.Order_Amt) , and include it on the Group Summary band for Country. The option to reset when Country changes completes the calculation.

I also want to do something about the region totals for countries that don't have a region entry. When we added a second group using the Region variable, we got a group header and a group summary line that has the name of the region. However, the data appears not to have region entries in most cases. Double-click on each of the entries on the Region Header and Region Footer to open the dialog shown in Figure 10.11.

Figure 10.11. Suppressing printing of region headers and footers when region is blank.

Click on the Print When button, enter Region <> "" in the text box with the heading Print Only When Expression Is True, and check the Remove Line If Blank check box. If you do this for all of the fields and labels in the group header and footer for Region, it will indeed disappear except for countries that have named regions .

What if you want to show states only for the United States? If you create a new report variable named, say, m.StateName , specify that it's a blank string if Country is not "USA", and do the group based on it, you won't get a subtotal for countries other than the USA because m.StateName will be blank. The Group Summary line only fires if the value changes.

Report Filtering

The Init event of the Data Environment of a FoxPro report form is the place where data for the report has to be acquired . If at the end of the Init event, your user can't connect to the data source, can't find the tables, or for whatever reason doesn't want to continue, the statement RETURN .F. will cancel the reporting process without printing. For this reason, the Init event of the report's Data Environment is a good place to put a report filtering mechanism.

To do this, build a form like the one shown in Figure 10.12.

Figure 10.12. A report filter form.

The form must be a modal form so that it can return a value. Listing 10.3 shows the Click event code for the Show Matching Records button.

Listing 10.3. The Click Event Code for the cmdShowMatches Button

 WITH THISFORM Exp1 = [] Exp2 = [] Exp3 = [] Exp4 = [] IF NOT EMPTY ( .Text1.Value )    FuzzyFilter = IIF ( .Check1.Value = 1, [%],[] )    Exp1 = [ AND LOWER(Company) LIKE '] + FuzzyFilter ;            + ALLTRIM(LOWER(.Text1.Value)) + [%'] ENDIF IF NOT EMPTY ( .Text2.Value )    FuzzyFilter = IIF ( .Check2.Value = 1, [%],[] )    Exp2 = [ AND LOWER(Contact) LIKE '] + FuzzyFilter ;            + ALLTRIM(LOWER(.Text2.Value)) + [%'] ENDIF IF NOT EMPTY ( .Text3.Value )    FuzzyFilter = IIF ( .Check3.Value = 1, [%],[] )    Exp3 = [ AND LOWER(City)    LIKE '] + FuzzyFilter ;            + ALLTRIM(LOWER(.Text3.Value)) + [%'] ENDIF IF .List2.ListCount <> 0    Exp4  = [ AND Country IN ( ]    FOR I = 1 TO .List2.ListCount        Exp4 = Exp4 + ['] + ALLTRIM(.List2.List(I)) + [',]    ENDFOR    Exp4 = LEFT(Exp4,LEN(Exp4)-1) + [)] ENDIF Expr = Exp1 + Exp2 + Exp3 + Exp4 Cmd = [SELECT Company, Order_Amt, Region, Country ] ;     + [ FROM Customer, Orders ]                        ;     + [ WHERE Customer.Cust_ID = Orders.Cust_ID ]    ;     + Expr                                            ;     + [ ORDER BY Country, Region, Company INTO CURSOR C1] *!*    MESSAGEBOX( Cmd, 64, [Report filter] ) *!*    Uncomment to see the SELECT statement &Cmd        && This executes the SELECT statement! ENDWITH MESSAGEBOX( TRANSFORM ( ;   RECCOUNT() ) + [ records will be included in the report], ;   64, [Report filter], 2000 ) 

The cmdSelect and cmdCancel buttons simply determine what value the form returns. In either event, the Unload event is going to close the two tables used in the query and return the ReturnValue . Listing 10.4 shows the code.

Listing 10.4. The Rest of the Filter Form Code

 cmdSelect::Click WITH THISFORM .ReturnValue = .T. .Release ENDWITH cmdCancel::Click WITH THISFORM .ReturnValue = .F. .Release ENDWITH Unload event code: IF USED ( [Orders] )    USE IN  Orders ENDIF IF USED ( [Customer] )    USE IN  Customer ENDIF RETURN THISFORM.ReturnValue 

Build and Execute a SELECT Statement in the Init Event of the Report's Data Environment

Back in the report, all you have to do is put the code from Listing 10.5 in the form's Init code.

Listing 10.5. The Init Event of the Report's Data Environment

 DO FORM CustFilter TO Ok IF NOT Ok    IF USED ( [ReportCursor] )       USE IN  ReportCursor    ENDIF    RETURN .F. ENDIF 

Simple, isn't it?

Using a Report Filter with SQL Server

An additional benefit of using a report filter form of this type is that with only a few changes you can use this form with SQL Server or with other data sources. I won't show you any of the others because I've only heard of a very few cases of people using the FoxPro report writer with data sources other than DBFs and SQL Server. But for SQL Server, it's very easy. All you have to do is make the changes shown in Listing 10.6 to the cmdSelect::Click event code.

Listing 10.6. SQL Server Version of the Click Event Code for the cmdShowMatches Button

 WITH THISFORM Exp1 = [] Exp2 = [] Exp3 = [] Exp4 = [] IF NOT EMPTY ( .Text1.Value )    FuzzyFilter = IIF ( .Check1.Value = 1, [%],[] )    Exp1 = [ AND LOWER(Company) LIKE '] + FuzzyFilter ;            + ALLTRIM(LOWER(.Text1.Value)) + [%'] ENDIF IF NOT EMPTY ( .Text2.Value )    FuzzyFilter = IIF ( .Check2.Value = 1, [%],[] )    Exp2 = [ AND LOWER(Contact) LIKE '] + FuzzyFilter ;            + ALLTRIM(LOWER(.Text2.Value)) + [%'] ENDIF IF NOT EMPTY ( .Text3.Value )    FuzzyFilter = IIF ( .Check3.Value = 1, [%],[] )    Exp3 = [ AND LOWER(City)    LIKE '] + FuzzyFilter ;            + ALLTRIM(LOWER(.Text3.Value)) + [%'] ENDIF IF .List2.ListCount <> 0    Exp4  = [ AND Country IN ( ]    FOR I = 1 TO .List2.ListCount        Exp4 = Exp4 + ['] + ALLTRIM(.List2.List(I)) + [',]    ENDFOR    Exp4 = LEFT(Exp4,LEN(Exp4)-1) + [)] ENDIF Expr = Exp1 + Exp2 + Exp3 + Exp4 Cmd = [SELECT Company, Order_Amt, Region, Country ] ;     + [ FROM Customer, Orders ]                        ;     + [ WHERE Customer.Cust_ID = Orders.Cust_ID ]    ;     + Expr                                            ;     + [ ORDER BY Country, Region, Company]   && Removed this: [ INTO CURSOR C1] *!*    MESSAGEBOX( Cmd, 64, [Report filter] ) *!*    Uncomment to see the SELECT statement lr = SQLExec ( THISFORM.Handle, Cmd, [C1] ) *!*     Use this instead of the INTO CURSOR clause IF lr < 0    MessageBox ( "SQL Server command failed" )  ELSE    MESSAGEBOX( TRANSFORM ( ;      RECCOUNT() ) + [ records will be included in the report], ;      64, [Report filter], 2000 ) ENDWITH 

I had to remove the INTO CURSOR C1 clause because the name of the cursor is supplied as the optional third parameter in the SQLExec() function call.

You could build the form so that it could be used with either SQL or with FoxPro tables, using a form property to determine whether to use macro expansion to run the FoxPro SELECT command, or to call the SQLExec() function. In either case, the result is a cursor, and REPORT FORM doesn't care .

There is one other consideration when you work with SQL Server. It's not uncommon to include a date, or a range of dates, in a report filter form. In FoxPro, the date delimiters are braces, like this:

 ldDate = {03/15/2004} 

In SQL, dates are delimited with single quotes. So if your filter form includes a pair of date fields, the logic for building an expression based on them would have to delimit the resulting date strings with single quotes instead of with braces.

Finally, the only delimiter allowed for strings in SQL is the single quote. So your expressions must use single quotes to delimit strings. Because of this, if your users type in expressions that contain a single quote, for example, "Frank's Franks", you have to replace the single quote in their entry with either a pair of single quotes or a replacement character. And because you don't know when they might do this, you have to do so every time. CHR(146) is a passable substitute for the offending CHR(39) :

 Exp1 = [ AND LOWER(Company) LIKE '] + FuzzyFilter ;      + CHRTRAN(ALLTRIM(LOWER(.Text1.Value)),['],CHR(146)) + [%'] 

Printing Tricks

A few of the things you can do with FoxPro reports are less than intuitive. For example, to print "Page 1 of NN," you actually have to run the report twice (see Listing 10.7). The trick is that _PageNo is a system variable; at the end of a report, it still contains the last page number printed. You can then refer to the saved variable in a field expression in your report.

Listing 10.7. Counting Pages in Order to Include "Page I of N" in a Report Heading

 nPages = 0 WAIT WINDOW 'Getting page count' NOWAIT FName = SUBSTR(SYS(3),2,6) + ".txt" REPORT FORM &ReportName NOCONSOLE TO FILE &FName nPages = _PAGENO ERASE (FName) REPORT FORM &ReportName NOCONSOLE TO PRINTER PREVIEW 

Generic Reporting

If you open SimpleReport1.frx with a USE statement and BROWSE it, you'll see lots of details, but nothing that's difficult. In fact, using your database container as metadata, you can allow users to build specifications for reports, then generate and execute FRX files just like this, without too much difficulty. In the November 2003 issue of FoxPro Advisor magazine, Mike Lewis published an article that shows one solution, thereby relieving me of the need to write said utility here. But if you have the need to provide users with the ability to design their own reports, the fact that the FRX file is a DBF makes it relatively easy to do.

The fact that FoxPro reports are just tables is what made this possible. It also made possible GenRepoX, a fabulous utility written by Markus Egger, which extends the FoxPro report-writing capability considerably.

GenRepoX was written based on the idea of GenScrnX, written by the amazing Ken Levy, head of the FoxPro team at Microsoft. I first met Ken when he was a long-haired teenager, amazing everyone at the early Fox Software conferences with his skill. Markus did a similarly amazing job with GenRepoX. You can download it from the EPS Software Web page, www.eps-software.com.

Reporting on the Internet

When you've designed a nice report, you might want to publish it on the Internet. If the report is one that seldom changes, it's ridiculously easy. Just publish the output of the report as HTML, put the resulting text files into a virtual directory, and send your users links to the filenames.

The Save as HTML feature in FoxPro is unusual. It actually runs the report. Try it using the report we just built with the filter page. In the command window, type MODIFY REPORT SimpleReport1 and select File, Save as HTML from the FoxPro IDE menu. The filter page appears and asks you to select records, then publishes the results as an .htm file. But it's not the same as live reporting. Besides, if you open the file as text and look at it, it's way ugly. You can do better. You have the technology.

Listing 10.8 shows the code to build SimpleReport1 as a Web page that is written to a previously determined filename in a virtual directory.

Listing 10.8. Building Web Pages as Reports

 SET TALK OFF SET CONFIRM ON SET PATH TO ( HOME() + [Samples\data\] ) mCountry = INPUTBOX("Country to include?", ;    "Leave blank to include all countries, ESC to cancel","") IF LASTKEY() = 27    RETURN ENDIF mCountry = PROPER( mCountry ) SELECT ;   ORDERS.Order_Amt,                        ;   Customer.Company,                     ;   Customer.Region,                        ;   Customer.Country                        ; FROM ORDERS, CUSTOMER                    ; WHERE ORDERS.Cust_ID = CUSTOMER.Cust_ID ; AND Country = mCountry                    ; ORDER BY                                ;   Customer.Country,                        ;   Customer.Region,                        ;   Customer.Company                        ;  INTO CURSOR C1 IF RECCOUNT() = 0    MESSAGEBOX( "No records found for " + mCountry, 64, ;    "No matching records found", 5000 )    USE IN C1    RETURN ENDIF SET TEXTMERGE ON TO C:\inetpub\wwwroot\CustsByCountry.HTM NOSHOW \<html><head><title>Customers by country</title></head> \<body> \<table> \ <tr><td width="100"> \   <table><tr><td size="1"><<DTOC(DATE())  >></td></tr> \          <tr><td size="1"><<LEFT(TIME(),5)>></td></tr> \   </table> \     <td width="600" size="4" align="center" valign="top">Orders by Country</td> \     <td width="100" size="1" valign="top">Page 1</td> \ </tr> \<table> LastRegion  = "X" LastCountry = "X" \<table> SCAN     IF Region <> LastRegion        \<tr><td>Region: <<Region>></td></tr>     ENDIF     IF Country <> LastCountry        \<tr><td>Country: <<Country>></td></tr>     ENDIF * Each row in the table is another small two-column table     \<tr><td><table><tr><td width="80%"><<Company>></td>     \                   <td width="20%" align="right">     \<<TRANSFORM(Order_Amt,"#,###,###.##")>></td>     \               </tr>     \        </table></td>     \</tr>     LastCountry = Country     LastRegion  = Region ENDSCAN \</table> USE SET TEXTMERGE TO 

This is another easy way to build report pages for publication on the Internet, and it gives you additional control over formatting that you can't get with REPORT FORM and SAVE AS HTML .

This would be fine for relatively static information, like a company phone directory. However, it still doesn't do what you usually want to do, which is to format and send output directly to the Internet at the moment the user asks for it. That requires an Internet application. Internet applications have to know how to interact with Internet Information Server (IIS), and that's a little more involved. In the last chapter, we'll explore Internet database application development in more detail.

Exporting to a PDF

Probably the best way to publish a report on the Internet is to create an Adobe Acrobat PDF. PDF is the acronym for Portable Document Format. It was developed by Adobe and really does create portable documents that can be sent to another user and printed on whatever printer they have. It can also be previewed in the free Acrobat Reader. It's an excellent technology that solves many reporting problems. Unfortunately, the cost of a single workstation license is considered prohibitively high by many of my customers. As of this writing, several inexpensive alternatives have appeared. If your project budget permits , you should consider PDF output for some or all of your reports.