Chapter 7 : Reports and the Report Writer
Microsoft FoxPro Support Engineers
Microsoft Corporation
Created: December 15, 1993
Abstract
This section discusses reports and the Report Writer in Microsoft FoxPro for Windows version 2.5x, and contains the following topics:
·	Introduction
·	Reports from DBF’s vs. Reports from Cursors
·	Environment and Data Grouping
·	Report Expressions
·	Fonts
·	Printing Reports Created with Question Marks
·	Additional Information
Introduction
There are two methods for creating reports in FoxPro for Windows. You can use the Report Writer or “hand code” a report using the question mark (?) command. There are also two ways of supplying data to a report: DBF and Query.
The following sections will emphasize using the Report Writer for creating your reports, with the last section discussing some tips on reports programmed using question mark (?) output.
Report from DBF vs. Report from Cursor
Before creating your report form or coding a report, it is important to decide on the data source and how many tables will be used.
Report from DBF
Reports can be built from DBF’s. If using this system, keep the following in mind:
·	If using more than one table, each field should contain the database name or alias.
·	When using related tables, the parent work area must be selected as the active area.
·	If using multiple one-to-many relationships, the relationship must have been established with a SET SKIP TO from the parent table to all children. For example, if the report is using the CUSTOMER.DBF(parent), INVOICES.DBF(child) and DETAIL.DBF(grandchild), the SET SKIP TO statement would read as follows:

SET SKIP TO Detail, Invoices
·	If using Data Groupings on the report, index the DBF according to your Data Grouping structure. For example if your report needs to be grouped by state and city within state, index the DBF on state + city. (See Environment and Data Groupings below for further information.)
·	Use the FOR clause on the REPORT FORMAT command to filter data for the report. For example:

REPORT FORMAT myreport TO PRINT FOR FOR BETWEEN(IDATE,{03/01/94},{3/31/94})
Report from Cursor
Reports can also be built from a query by using the RQBE tool or a SQL-SELECT statement to organize and filter the information needed. The cursor created by the query would replace the tables, relationships and indexes in the above example.
The following SQL-SELECT statement was created by the RQBE interface. The goal in using RQBE or a SQL-SELECT is to create a temporary cursor that has all of the information in the right order for a Report that has a Data Grouping.
SELECT Customer.company, Customer.contact, Customer.state, Customer.city, ;
  Customer.ytdpurch, Invoices.ino, Invoices.idate, Invoices.itotal, ;
  Invoices.salesman;
  FROM Customer, Invoices ;
  WHERE Invoices.cno = Customer.cno ;
  ORDER BY Customer.state, Customer.city, Customer.ytdpurch DESCENDING, ;
  Invoices.ino ;
INTO CURSOR Query

REPORT FORM report.frx PREVIEW

In preparation for Data Grouping the ORDER is set by State, City, by ytdpurch DESCENDING, and by the ino field from the Invoices table.
Tips on Reports from Cursors
·	Do not alias any fields or report bands on the report. Adding an alias to the fields will cause erroneous output.
TIP:     If you are running a report from a cursor and the first record is repeated, check each field and each report band for a database alias. Also make sure that the environment is not saved with the report.
·	Group By on a SQL-SELECT and a Data Grouping in a report form are not the same. The Group By clause on a SQL-SELECT will group information into one record based on the field you select. For example, the following statement will only produce 50 records from the CUSTOMER.DBF (which has over 500 records):

SELECT *;
 FROM Customer;
 GROUP BY Customer.state
	This statement discards all duplicate states, so the resulting cursor contains only one record for each state.
·	If using Data Grouping on a report created from a cursor, use the Order By clause to arrange the records in correct order. For example, if you want Data Groupings on State, City, select the Order By option on the RQBE screen or use the following syntax in your SQL-SELECT statement:

SELECT *;
 FROM Customer;
 ORDER BY Customer.state, Customer.city
Environment and Data Grouping
The Data Grouping option in the Report Writer is one of the most common options used. This sections title includes Environment because the two subjects go hand in hand. In short, a Data Grouping is only obtainable as long as the environment is setup correctly.
Environment:
The environment refers to the databases or tables currently loaded into work areas, their indexes order and any relations set.
NOTE:     Setting the environment (tables, relations and indexes) is only necessary when creating a report from .DBF’s. If you creating a report from a cursor, the SQL SELECT statement used should take care of including all necessary fields from all tables and sorting the information into the correct order.
Indexing:
INDEXing is the most common way of organizing data for a Data Grouping. 
NOTE:     A SORTed database could also be used, however SORT creates a new DBF and increases the amount of disk space required.
Complex indexing can be used to put the database in the right order to get the results of more than one Data Grouping, such as grouping the Customer database by State, City and ytdpurch (Year To Date Purchases) fields with an ascending order. Ascending order is the default. The Report Writer in FoxPro can handle as many as 20 Data Groupings. The following is an example of a complex index that sets the database in the right order to produce a Data Grouping as previously described :
INDEX ON state+city+STR(ytdpurch,10,2) TAG stcityaytd ADDITIVE
Or
INDEX ON state+city+STR(ytdpurch -100000,10,2) TAG stcitydytd ADDITIVE
The second example would be used to get a DESCENDING order to ytdpurch (Year To Date Purchase), putting the largest dollars on the top of the group.
Relations:
Relationships are set between two or more databases using common key fields. This is for the purpose of bringing together related information. The key field is identified early in the creation of the database as part of the layout of an information base. This is the basis of Relational Databases, making it possible to maintain a normalized information source. 
The requirement for this key field is the field structure (data type and size). The structure must match the key field structure used in any other database(s) to be related. The name of the field is not a concern.
The relationship between two databases is commonly referred to as a Parent-Child Relationship. The Parent database is the master database containing primary information and the Child contains data related to the Parent. 
The Child database must be indexed on the key field. The Parent does not have to be indexed for the purpose of setting up a relationship, although it does need to be indexed to establish an accurate data grouping.
It is possible to have a complex or compound index on the Child database. The key field must be first in the list of fields to be concatenated. Concatenated values or fields must be the same data type. The following is an example of a compound index :
INDEX ON cno+ DTOS(idate) TAG Cnodate ADDITIVE
NOTE: Unless the fields to be concatenated are converted to character using the STR() function, the resulting index value will be the sum of the fields.
Once the indexes are in place a relationship can be established. The following code is an example of setting a relation:
SELECT Customer 			&&Go to Parent work area or Alias
SET RELATION TO cno INTO Invoices	&&Child Alias
The above example established a “one-to-one” relationship - that is one child record for one parent record. In many cases a “one-to-many” relationship is necessary - one or more child records for one parent record. The following code example sets a one-to-many relationship between CUSTOMER.DBF and INVOICES.DBF:
SELECT Customer 			&&Go to Parent work area or Alias
SET RELATION TO cno INTO Invoices	&&Establish relationship
SET SKIP TO Invoices			&&Create a one-to-many relationship
With the one-to-many relationship, the data is now grouped on company, followed by all invoices related to that company. With the data source now in the correct order, you can now create a report with Data Groupings.
Data Grouping
The Report Writer makes it easy to create a report that uses Data Grouping. A Data Grouping is used when a hierarchy of information is desired such as the following report :

AK
	Bethel		Company Name	Contact
		Yergen Endeavors	Tom Droege--Year to Date Purchase	 $14820.12
				Invoice #	1158   05/13/90    $1347.72
	Bethel Total$14820.12
	AK Total$14820.12
 AZ
	Mesa		Company Name	Contact
			Great Form	Dorit Mowery--Year to Date Purchase	 $2523.50
				Invoice #	1145   05/12/90    $1488.50
	Mesa	Total $2523.50
	Phoenix	Company Name	 Contact
	Raybank Services  Jerry Campanonoy--Year to Date Purchase $655.91
				Invoice #	1166   05/15/90    $1540.03
	Phoenix  Total  $655.91
	AZ Total $3179.41
CA
	Santa Clara	Company Name 	Contact
	Atec Data Service	Randy Keji--Year to Date Purchase $514.98
				Invoice #	1187   05/17/90    $2721.191
				Invoice #	1254   05/30/90     $163.72
				Invoice #	1255   05/26/90     $441.91
				Invoice #	1256   05/31/90     $762.56
				Invoice #	1257   05/28/90     $744.49
	Wick of Wisconsin	David Jaeger--Year to Date Purchase $933.87
				Invoice #	1165   05/14/90     $591.39
				Invoice #	1238   05/22/90    $2125.80
	Santa Clara Total $4442.64
Report Expressions
A report expression can be as simple as a field name and as complex as a user defined function (UDF). Below are a few examples of what can be done with a report expression. These examples group multiple functions into one expression, IIF() and report variables, using a UDF and using EVALUATE().
Multiple Functions in One Expression
You can use a group of functions together in one Field Expression. For example, to print a long date instead of the MM/DD/YY date format, use the following expression:
cmonth(date())+" "+alltrim(str(day(date())))+", "+alltrim(str(year(date())))
IIF() and Report Variables
Many times you will need to SUM or COUNT a field of a report, but only when that field meets a specific condition. This can be done using the IIF or Immediate IF function and a Report Variable.
The IIF function returns one of two values, depending upon the value of a logical expression. If the logical expression is true, the first value is returned. If logical expression is false, the second value is returned.
NOTE:     The following example is to demonstrate conditional COUNTs or SUMs. To COUNT or SUM all values of a field, DO NOT use the following technique. Instead, sort the database on the desired field, then create a Data Grouping and perform the standard calculations based on that Data Grouping.
To Count Fields
1.	Create a report variable containing an IIF function.
2.	In the logical expression of the IIF, type the condition you want to count.
3.	In the first IIF return value, type a numeric digit 1.
4.	Type a numeric digit 0 in the second IIF return value.
5.	In the Variable Definition dialog box under Calculate, select Sum.
To Sum Fields
1.	Create a report variable containing an IIF function.
2.	In the logical expression of the IIF, type the expression to be evaluated.
3.	Type the name of the numeric field or variable to sum in the first IIF return 
value.
4.	In the second IIF return value, type a digit 0.
5.	In the Variable Definition dialog box under Calculate, select Sum.
Example:
This example creates a database of different types of computers and their prices. The report variable, DESKTOPCT, created in steps 5 through 8, counts the number of records whose type equals 'DESKTOP.' The report variable, DESKTOPSUM, created in steps 9 through 11, sums the price of all records whose type equals 'DESKTOP.'
1.	Use the following code to create the database:
CREATE TABLE test (type C(20), price N(5))
INSERT INTO test (type, price) VALUES ('DESKTOP', 1500)
INSERT INTO test (type, price) VALUES ('LAPTOP', 2000)
INSERT INTO test (type, price) VALUES ('DESKTOP', 4000)
2.	Use the Report Builder to create a new report.
3.	From the Report menu, choose Quick Report, then the OK button.
4.	From the Report menu, choose Title/Summary. 
5.	Under Report Summary, select Summary Band. Choose the OK button.
6.	From the Report menu, choose Variables, then choose the Add button.
7.	In the Variable Name text book, type "desktopct" (without the quotation marks).
8.	Choose the Value to Store button to enter the Expression Builder dialog box. Under Value to Store in Variable desktopct, type the following:
IIF(test.type = 'DESKTOP',1,0)
9.	Choose the OK button.
10.	In the Variable Definition dialog box, under Calculate, select Sum and choose the OK button. (This variable counts the number of records whose type equals 'DESKTOP').
11.	In the Report Variables dialog box, choose the Add button. In the Variable Name text book, type "desktopsum" (without the quotation marks).
12.	Choose the Value to Store button to enter the Expression Builder dialog box. Under Value to Store in Variable desktopsum, type the following:
IIF(test.type = 'DESKTOP',test.price,0)
13.	Choose the OK button.
14.	In the Variable Definition dialog box, under Calculate, select Sum and Choose the OK button twice to return to the Report Design window. (This variable sums the PRICE field for all records whose type equals 'DESKTOP').
15.	From the toolbox, select the Field tool.
16.	Place a field object in the Summary band. In the Report Expression dialog box, choose the Expression button. Under Variables, double- click DESKTOPCT. Choose the OK button to return to the Report design window.
17.	From the toolbox, select the Field tool again.
18.	Place a field object in the Summary band. In the Report Expression dialog box, choose the Expression button. Under Variables, double- click DESKTOPSUM. Choose the OK button to return to the Report design window.
19.	From the Report menu, choose Page Preview. Note that the variable
20.	DESKTOPCT displays "2," and DESKTOPSUM displays "5500."
Using a UDF to Calculate Percentage of Total
This example will show how to create a report with group totals as well as the percentage of that group related to the grand total.
1.	Type the following in the Command window:

SET DEFAULT TO <path>FOXPROW\TUTORIAL
USE detail
INDEX ON ino TAG ino
CREATE REPORT UDFCOUNT
2.	Create a quick report based on the DETAIL.DBF.
3.	Add a Data Grouping on the Ino field.
4.	Add a field to the Group Header. In the Report Expression dialog box, add the following: 
		SUBSUMM(ino) 
NOTE:     This is the name of your UDF (user-defined function). The (ino) is the parameter you are passing to the SUBSUMM function, which you will write after creating this report.
5.	In the Group Footer band, add the following expression:
(Detail.ltotal/summ) * 100
6.	In the Group Footer band, add the following expression:
Detail.ltotal.
	Click on the Calculate option and choose Sum. Make sure that the total is reset at the end of the Data Grouping, which is Detail.ino.
7.	Add a Summary Band. Add the following expression:
Detail.ltotal.
	Click on the Calculate option and choose Sum. Make sure that the total is reset at the End of Report.
8.	Click on the Close box and save the Report. Give it a name that doesn’t already exist, such as UDFcount. (See udfcount.frx)
9.	In the Command window, type the following:
MODIFY COMMAND subsumm		&&(See UDFcount.prg)
10.	Type in the following program:

PARAMETER invoice
recnum=RECNO()
CALCULATE SUM(ltotal) TO summ
GO recnum
RETURN “”
11.	Close the program and save changes.
12.	Now, in the command window, type the following:
REPORT FROM udfcount PREVIEW NEXT 30
NOTE:     Since there are 1363 records in DETAIL.DBF, previewing all records would take a long time. Notice that SUM(ltotal) gets echoed to the screen for every record processed.
Using EVALUATE()
The syntax for evaluating a variable is different on a Report Form than in a program. Normally in a program you would use macro substitution (i.e.: &myvar) or named expression (i.e.: (myvar)). In a report expression, you must use EVALUATE(myvar).
Fonts
This section discusses:
·	Changing the Default Font in the Report Writer
·	Changing the Font on Selected Fields
Changing the default font in a report
Although there is no option to save a font preference for the Report Writer you can save a default font by creating a report template:
1.	In the command window, type CREATE REPORT or Choose File, New, Report and choose the New button.
2.	From the Report menu, choose Page Layout.
3.	Choose the Font push button.
4.	Select your default font.
5.	Save the report as UNTITLED.FRX in the FoxPro for Windows default directory (Example, C:\FOXPROW).
6.	Set the default directory to the FoxPro for Windows directory. This can be done by typing the following in the command window:
SET DEFAULT TO C:\FOXPROW
7.	Now, from the Command window, type CREATE REPORT or Choose File, New, Report and choose the New button.
8.	Choose the text item from the ToolBox (see the picture below). The new default font should now be used.
Figure 1.  Screen capture of Report Builder with ToolBox.
Changing the Font on Selected Fields
To change the font for selected fields already in a report:
1.	Click the mouse on the first item.
2.	Hold down SHIFT, and click on all other items that you want to re-format with the new font.
3.	Choose Report, Page Layout. Push the Font push button. Choose the desired font.
Printing Reports Created with Question Marks
You may have reports (commonly xBase-programmed) that used to print correctly but now do not align properly. This problem may occur due to the use of proportional fonts in Microsoft Windows.
The following two methods may be used in Microsoft FoxPro for Windows version 2.5 to print reports that are created using question mark (?) commands to send information and escape codes to the printer.
Method 1 - Report previously created in FoxPro for MS-DOS
·	Turn off all open print commands by issuing
SET DEVICE TO SCREEN
SET PRINTER TO
·	Make sure the current resource file (a FoxPro 2.0 or FoxPro 2.5 FOXUSER.DBF) is being used.
·	Load an existing FoxPro for MS-DOS printer driver. The sample program below uses an existing printer driver name HPCOMPRESS. This printer driver will open the printer in "raw mode," which bypasses the Windows printer drivers.
·	Issue SET PRINT ON to redirect all question mark (?) output to the printer.
·	At the end of the report, clear the raw mode and begin using the Windows printer drivers by issuing SET PRINTER TO.
The following code illustrates the above steps:
SET DEVICE TO SCREEN
SET PRINTER TO
SET PDSETUP TO "hpcompress"
SET PRINT ON
? _pdparms(15)	&& This is an example of using _PDPARMS and
			&& escape codes to control printing attributes.
			&& 15 denotes italics on
?"Hello"
? chr(27)+"(s16.66H"	&& This demonstrates the sending of a control code.
?"There"
EJECT
SET PRINTER TO
SET LIBRARY TO
SET PDSETUP TO
NOTE:     For the above method to work correctly, FoxPro 2.0 or 2.5 for MS-DOS must have been previously installed. There must also be a FoxPro for MS-DOS resource file in use (i.e. a FOXUSER.DBF which contains printer driver setup information.)
Method 2 - Report created in FoxPro for Windows
If FoxPro for MS-DOS has not been used and no printer driver setups exist, the results from Method 1 can be achieved by performing the following:
·	Set the Microsoft Windows printer driver to Generic/Text Only (See Printer Installation in the Windows Users Guide)
-OR-
·	Select one of the monospace (non-proportional) fonts, such as Courier, Courier New, FoxFont, or FoxPrint, by issuing SET PRINT FONT "<fontname>",<point size>
Additional Information About Reports/Report Writer
Additional information concerning the use of Reports and the Report Writer tool can be found by looking at the following articles:

Q94809	Q102934	Q104255	
Q95245	Q103581	Q104351	
Q96173	Q103588	Q107403	
Q98702	Q103690		
Q102074	Q103829