Chapter 12 -- Adding Queries and Reports | Microsoft Visual FoxPro 6. 0 Programmer's Guide 1998

Once you create tables and forms for your application, you can add queries and reports to select and display data for your users. Your queries can be directed to a variety of destinations so that you can use them in the other components of your application. You can also run reports separately without using a query. This chapter highlights some of the ways you can use queries, add reports, and expose queries and reports to the user.

When you use a query or a view in your application, you are actually using a SELECT - SQL statement. This chapter describes how you can use a SELECT - SQL statement in your application whether you create it through a query defined in the Query Designer, a view defined in the View Designer, or code entered for an event or procedure. For detailed information about views, see Part 2, Finding Information, in the User s Guide.

This chapter discusses:

Adding Queries
Adding Reports and Labels
Integrating Queries and Reports

Adding Queries

When you add queries to your application, you can combine a variety of data sources, finely filter records, manipulate data, and sort the results all with the SELECT - SQL statement. By using the SQL statement, you have complete control over the results your query produces and where the results are stored.

A query is a SELECT - SQL statement.

You can add SELECT - SQL statements to procedures or to event code. For more information about events, see Chapter 4, Understanding the Event Model.

Creating SELECT - SQL Statements

To create a SELECT - SQL statement

Use the Query Designer or the View Designer to build the statement, then copy the contents of the SQL window into a code window.
-or-
In a code window, type the SELECT - SQL statement.

For example, you can select all the records from the Customer table in the TasTrade database where the country field contains the value Canada :

SELECT * ;    FROM tastrade!customer ;    WHERE customer.country = "Canada"

To execute the command immediately, you can enter the statement in the Command window. If you want each clause to appear on a separate line within the window, end each line except the last one with a semicolon so that Visual FoxPro processes the command only after the last line.

Selecting a Number or Percentage of Records

If you only need a certain number or percentage of records from the result set that your query would return, you can use the Top property on the Miscellaneous tab in the Query or View Designers or you can add a TOP clause to your SELECT - SQL statement. The number you provide in a TOP clause can range from 1 to 32,767. For a percentage, you can use from 0.001 to 99.99.

For example, if you want to select the top 10 customers with the highest order amounts, you can specify a GROUP BY on CUST_ID to show one aggregate record for each customer and sort by ORDER_AMT in the ORDER BY clause. To get a true TOP 10, you need to specify a descending sort on the ORDER_AMT so that the records with the highest order amounts appear first in the results. If you use an ascending sort, the result records are ordered from the least order amount to the most. The top records you select from the result set would actually have the lowest values.

SELECT TOP 10 *; FROM testdata!customer INNER JOIN testdata!orders ; ON Customer.cust_id = Orders.cust_id; GROUP BY Customer.cust_id; ORDER BY Orders.order_amt DESC

Specifying Destinations for Query Results

By using clauses of the SELECT - SQL statement, you can specify several destinations to store your query results.

To send results to this destination	Use this clause
Separate table	INTO TABLE mytable
Array	INTO ARRAY aMyArray
Temporary table	INTO CURSOR mycursor
Active window	TO SCREEN
Browse window	The default if no other destination is specified.

Once the results are stored, you can use commands to control how the stored results are integrated for display or printing.

Storing Results in a Table, Array, or Cursor

You can store your query results in a table, array, or cursor for other uses such as populating forms and printing reports and labels. If you want to store the results only temporarily, send the results to an array or cursor. If you want to store the results permanently, send the results to a table.

To specify a table as the destination

Use the INTO clause of the SELECT - SQL statement to specify a destination.

The following example shows an INTO clause for a table:

SELECT * ;    FROM tastrade!customer ;    WHERE customer.country = "Canada" ;    INTO TABLE mytable

To specify an array as the destination

Use the INTO clause of the SELECT - SQL statement to specify a destination.

The following example shows an INTO clause for an array:

SELECT * ;    FROM tastrade!customer ;    WHERE customer.country = "Canada" ;    INTO ARRAY aMyArray

To specify a cursor as the destination

Use the INTO clause of the SELECT - SQL statement to specify a destination.

The following example shows an INTO clause for a cursor named mycursor:

SELECT * ;    FROM tastrade!customer ;    WHERE customer.country = "Canada" ;    INTO CURSOR mycursor

If you create a table or an array, you can use it like any other table or array in Visual FoxPro. If you create a cursor, you can browse through its contents. The cursor is opened in the lowest available work area. You can access it by using the name you gave it in the SELECT - SQL statement.

The following two procedures describe two common ways to include query results stored in tables and cursors into an application.

Populating a Form Control

If you want to display your query results in a form, you can use a table, array, or cursor to populate a grid, list box, or combo box.

To populate a list box or combo box control with a table or cursor

In the Form Designer, modify the form that has the control you want to populate.
Set the RowSourceType property to 3 - SQL Statement.
In the control s RowSource property, enter a SELECT - SQL statement that includes an INTO TABLE or INTO CURSOR clause.

To populate a grid control with a table or cursor

In the Form Designer, modify the form that has the control you want to populate.
In the form s Load event, enter a SELECT - SQL statement that includes an INTO TABLE or INTO CURSOR clause.
Set the grid s RecordSource property to the name of the table or cursor you created in Step 2.
Set the grid s RecordSourceType property to 0 Table (for a table) or 1 Alias (for a cursor).

Printing Results in a Report or Label

If your report or label includes groups or you otherwise need to order the data, you can use the various clauses of the SELECT - SQL statement to get the exact results you need.

To send results to an existing report or label

Use the SELECT - SQL statement with a REPORT or LABEL command.

The following example uses the GROUP BY and ORDER BY clauses as well as the REPORT FORM command:

SELECT * ; FROM tastrade!customer ; WHERE customer.country = "Canada" ; GROUP BY customer.region ; ORDER BY customer.postal_code, customer.company_name ; INTO CURSOR MyCursor REPORT FORM MYREPORT.FRX

The following example uses a LABEL FORM command:

SELECT * ; FROM tastrade!customer ; WHERE customer.country = "Canada" ; GROUP BY customer.region ; ORDER BY customer.postal_code, customer.company_name ; INTO CURSOR mycursor LABEL FORM MYLABEL.LBX

While the SELECT - SQL statement is the most flexible method for populating your report or label, it is not the only method. For more information about setting report data sources, see the section Controlling Data Sources later in this chapter. For more information about integrating the report destinations into your application, see Integrating Queries and Reports later in this chapter.

Displaying Results in a Window

If you want to display the results of your SELECT - SQL statement, you can send the results to a window. The Browse window is the default destination for query results and you don't need to include a destination clause. You can also send the results to the main Visual FoxPro window or another active window.

To display results in the main Visual FoxPro window

Use the TO SCREEN clause of a SELECT - SQL statement.

To display results in another active window

Define a window, show it to activate it, and then run a SQL query or other command that displays results in a window.

This code example shows the definition for a temporary window titled Top Customers that displays the names of companies with more than $5,000 in total orders for the year.

Displaying query results in a window

Code Comment

frmMyForm=createobj("form") frmMyForm.Left = 1 frmMyForm.Top = 1  frmMyForm.Width = 130 frmMyForm.Height = 25 frmMyForm.Caption = "Top Customers" frmMyForm.Show

Create and start a temporary window object.

SELECT customer.company_name,   SUM(orders.freight) ;   FROM tastrade!customer,   tastrade!orders ;   WHERE customer.customer_id =   orders.customer_id ;   GROUP BY customer.company_name ;   HAVING SUM(orders.freight) > 5000 ;   ORDER BY 2 DESC

Enter a SELECT - SQL statement.

Adding Reports and Labels

After you collect and organize your data, you can add reports or labels to your application to print the data or display it on the screen. You can control the data in your report through the data sources you choose, and manipulate and combine raw data with report variables. Report variables store values that are calculated and used in a report.

Controlling Data Sources

To control the data sources for a report, you can define a data environment that is stored with the report or you can activate specific data sources in code each time you run a report. For more information about using the Data Environment Designer, see Chapter 9, Creating Forms.

To	Add
Always use the same data sources.	Tables or views to the report s data environment.
	DO query or SELECT - SQL to the Init event of the report s data environment.
Use separate sets of data sources.	USE table, USE view, DO query, or SELECT - SQL to the Click event or other code that precedes a REPORT or LABEL command.

If you use a table as the data source, use aliases on the fields in the report only if you don't plan to use the report with any other data source than the table itself. If you use a view or query as the data source and aliases are included in the report controls, the report might display the same record repeatedly on the report page.

Controlling Record Order

You can use the data sources used by the report to control the order the records print in your report. The records are processed and printed in the order they appear in the table, view, or query. To order the records in a table, you can set an index in code or as part of the report's data environment. For a query, view, or SELECT SQL code, you can use the ORDER BY clause. If you don't order the records using the data sources, the only way to use just the report to order the records is through the ORDER property on a cursor in the data environment.

Controlling Record Selection

In addition to the order records appear in the report, you can select which records are printed by using the data source, the report printing options, or a combination of both.

To use	Add
View or query	Conditions in the Filter tab
SELECT - SQL	WHERE or HAVING clause
Report Designer	Setting in the Print Options dialog box
REPORT command	Scope, FOR, or WHILE expressions
Table	Filtered index

Protecting a Report's Data Session

To prevent your report's data session from being affected by the global data session as a result of changes made in other designers, you can set the report's data session to Private.

To set a private data session

From the Report menu, choose Private Data Session.

For more information about using the Data Environment Designer, see Chapter 9, Creating Forms. For more information about data sessions, see Chapter 17, Programming for Shared Access.

If you want to display your query results in a graph, you can use the Graph Wizard, the Query Designer, or a SELECT - SQL command. To use the Query Designer or a SELECT - SQL command, follow the steps below. You need to include at least one numeric field in your result set to create a graph. After completing the query, you can select one of six types of graphs, each with two variations.

To modify the graph

Browse the table containing the graph.
Double-click the general field to display the graph.
Double-click the graph to open Microsoft Graph and display the Microsoft Graph toolbar.
Modify the graph in Microsoft Graph.

Refining Page Layout

You can refine the layout of your report pages by defining multiple columns and changing the area of the page reserved for a band by changing the height of each band.

Defining Multiple Columns on a Page

To create phone directories, mailing labels, or other types of listings, you can define multiple columns per page.

To define a multi-column report

From the File menu, choose Page Setup.
Page Setup dialog box with columns defined
In the Columns area, enter the number of columns for the page. This is the same as the number of records you want to print across the page.
In the Width box, enter a value for column width.
In the Spacing box, enter a value for the space you want to appear between each column.
Tip If you're printing groups set to start on a new page, don't use the Print Order option.
Choose OK.
The Report Designer reflects your changes.

If your layout already contained report controls in the Detail band, you may need to move or resize them to fit within the boundaries of the new column.

Setting Report Band Height

While designing your report, you can change the height of a report band. The height of a report band determines the amount of space that each report band uses on the page within the page margins. For example, if the Title band is set at a half inch, the Title will appear in the first half inch of space after the top margin. The detail band shows the amount of space devoted to each printed record. The following information applies to all of the report bands. You can set additional parameters for Group Header and Footer bands. For more information about Group bands, see the section Grouping Data on Your Layout in Chapter 7, Designing Reports and Labels, in the User s Guide.

To set a precise band height

Double-click the bar for the appropriate band.
A dialog box for the band appears.
In the Height box, enter a value for the height.
Choose OK.

Using Expressions and Functions in Field Controls

You can include field controls in your report or label to display values from a variety of expressions, including fields from tables and views, variables, and calculations. The following sections describe some commonly used expressions and functions such as multiple fields, dates, and page numbers.

Adding Field Controls

You can add field controls in several ways.

To add table fields from the data environment

Open the report s data environment.
Select a table or view.
Drag fields onto the layout.

To add table fields from the toolbar

From the Report Controls toolbar, insert a Field control.
In the Report Expression dialog box, choose the dialog button after the Expression box.
In the Fields box, double-click the name of the field you want.
The table name and field name appear in the Expression for Field on Report box.
Note If the Fields box is empty, add a table or view to the data environment.
You don't have to keep the table name alias in the expression. You can delete it or you can clear the Expression Builder options.
Choose OK.
In the Report Expression dialog box, choose OK.

After entering the expression, you can change the format or set printing, position, or stretch options. For more information, see Adding a Comment to a Control in Chapter 7, Designing Reports and Labels, in the User s Guide, and see Setting a Control s Print Options later in this chapter.

Inserting Concatenated Field Controls

After you add your table fields, you might notice that they don't print the way you would like on the page. For example, printing the field controls for City, Region, and Postal Code separately produces unwanted spaces between each value. You can trim or concatenate the table fields into one field expression. The space required by each value for this control will vary. You can set the control to adjust for each value.

To combine several table fields into an expression

From the Report Controls toolbar, insert a Field control.
Tip Size the field to the least amount of room the expression will require. If more space is needed, you can set the control to stretch for larger values, but you can t set it to shrink if less space is needed.
In the Report Expression dialog box, select the dialog button after the Expression box.
In the Expression Builder, select ALLTRIM(expC) from the String box.
The string function appears in the Expression box with expC selected.
Double-click the first field name you want to appear in the control.
The field name replaces the expC.
Type a plus sign after the field name or select + from the String functions box.
Type , or select Text from the String functions list and then enter a comma.
Repeat steps 3 and 4 for additional fields to complete the expression and then choose OK.
In the Report Expression dialog box, select Stretch with Overflow.
When the control is populated, the space allocated to the control adjusts downward to accommodate the value of the expression. For more information about Stretch with Overflow, see Printing Controls with Variable-Length Values later in this chapter.

To combine several fields in an expression, place an ALLTRIM( ) function before each field name, place punctuation inside quotation marks, and place a plus sign between each of the elements in the expression. If the field value lengths don't vary, such as with postal codes or abbreviations, you can insert just the field name, as in this example:

ALLTRIM(city)+", "+region+"  "+postal_code

Notice the spaces in quotes, rather than a comma, to separate the region and the postal code.

For more examples, see the report Invoice.frx in the Visual Studio \Sample\Vfp98\Solution\Reports directory.

Trimming and Concatenating Character Expressions

To quickly trim and concatenate character expressions in the Expression Builder, you can place commas between character expressions. The value of the expression preceding the comma is trimmed. You can also use semicolons to place the expression on a new line, provided that the trimmed value has a length greater than zero. The following example shows character expressions for fields in a mailing address:

contact_name; address; city, region, postal_code

Note Use these when you don't want to include punctuation in the value.

If you use these methods, make sure the field is set to Stretch with Overflow. For more information, see Printing Controls with Variable-Length Values later in this chapter.

Inserting the Current Date

You can insert a field control that prints the current date.

To insert the current date

From the Report Controls toolbar, insert a Field control.
In the Report Expression dialog box, select the dialog button after the Expression box.
In the Expression Builder, select DATE( ) from the Date list.
Choose OK.
In the Report Expression dialog box, choose OK.

Inserting a Page Number

The Page Header or Footer bands usually contain a page number. If you use a wizard or Quick Report, a page number is inserted for you into the Page Footer band.

To insert a page number

From the Report Controls toolbar, insert a Field control.
In the Report Expression dialog box, select the dialog button after the Expression box.
In the Expression Builder, select _pageno from the Variables list.
Choose OK.
In the Report Expression dialog box, choose OK.
Tip You can use this procedure to insert any of the system variables from the Variables list into your report.

Defining Report Variables

To manipulate data and display calculated results in a report, you can use report variables. Using report variables, you can calculate values and then use those values to calculate subsequent values.

To define a report variable

Open or create a report.
From the Report menu, choose Variables.
In the Report Variables dialog box, select the Variables box and type a name for the variable.
In the Value to store box, type a field name or any other expression.
If appropriate, select a calculation option.
If appropriate, in the Initial value box, type an expression that sets the initial value.
Choose OK.
You can use the variable in any expression you enter in the report.

To count all the Canadian entries in the Company table, use this expression and select Count as the calculation option.

IIF(country="Canada",1,0)

The following example shows three variables for a simple time sheet:

To store this value	Create this variable	Using this expression
Time employee arrived	`tArrive`	`hour_in + (min_in / 60)`
Time employee left	`tLeave`	`hour_out + (min_out / 60)`
Total time employee was present	`tDayTotal`	`tLeave - tArrive`

You can use the tDayTotal variable in a variety of other calculations, such as the number of hours worked in a week, a month, or a year; the average number of hours worked each day; and so on.

For examples of report variables, see the reports Percent.frx and Invoice.frx in the Visual Studio \Samples\Vfp98\Solution\Reports directory.

Reordering Report Variables

Report variables are evaluated in the order that they appear in the list and can affect the value of expressions that use them. For example, if variable 1 is used to define the value of variable 2, variable 1 must appear before variable 2. In the previous time sheet example, tArrive and tLeave must precede tDayTotal.

To change the order of report variables

From the Report menu, choose Variables.
In the Variable box, drag the button to the left of the variable to rearrange the order.
Choose OK.

Setting a Variable s Initial Value

If you use a variable in calculations, be sure that you initialize the variable with a non-zero value to avoid a division-by-zero error. If you don't specify a value, Visual FoxPro assigns a default value of 0.

To set a variable's initial value

From the Report menu, choose Variables.
In the Variable box, select the variable you want to set.
In the Initial value box, enter the value.
Choose OK.

If you reorder the groups in your report, your report variables might not be resetting on the correct field. For example, if your report has two groups, the first grouping by country and the second grouping by date, and you switch the order of the groups, the variables still reset according to the original positions of the groups.

You can change the value of a calculation by specifying when the variable is reset. By default, Visual FoxPro resets the report variables at the end of the report.

To reset a variable at the end of a report, a page, or a column

From the Report menu, choose Variables.
In the Reset at box, choose an option.
Choose OK.

To reset a variable on entry or exit of any band

In the Report Designer, open the report.
Double-click the bar for the report band.
In the Run expression area of the band s dialog box, choose the dialog button at the end of the On entry or On exit box.
Enter an expression to reset the variable each time the band is entered or exited.

Formatting Field Controls

After inserting a field control, you can change the control s data type and print format. Data types can be Character, Numeric, or Date. Each of these data types has its own format options, including the option to create your own format template. The format determines how the field is displayed when the report or label is printed.

You can enter format functions directly in the Expressions box of the Report Expression dialog box or you can select options from the Format dialog box.

Typically, you might convert all alphabetical output to uppercase, insert commas or decimal points in numeric output, display numeric output in currency format, or convert one date format to another.

Formatting Options for Report Controls

For field controls, you can set a variety of format options for each data type.

To format a field control

Choose the Field control.
In the Report Expression dialog box, choose the dialog button after the Format box.
In the Format dialog box, select the data type for the field: Character, Numeric, or Date.
The Editing options area displays the formatting options available for that data type.
Note This data type applies only to the report control. It reflects the data type of the expression and doesn't change the data type of the field in the table.
Select the justification and format options you want.

The Format dialog box displays different options depending on the data type you choose. You can also create a format template by entering characters in the Format box.

Justifying Text in a Field

You can justify field contents within the control in two ways. This setting does not change the position of the control on the report, only the contents within the space of the control.

To justify text in a field control

Select the controls you want to change.
From the Format menu, choose Text Alignment.
From the submenu, choose the appropriate command.

To justify text in a field

Choose the Field control.
In the Report Expression dialog box, choose the dialog button after the Format box.
Format dialog box for a Numeric expression
In the Format dialog box, select the data type for the field: Character, Numeric, or Date.
Select the justification and format options you want.

Defining Field Format Templates

A format template allows you to customize the format of the field. By entering a combination of characters and codes in the Format box of the Report Expression dialog box or the Format dialog box, you can create a wide variety of print formats. The characters you enter appear as literal text along with the field value. The codes you enter determine the appearance of the field output. For example, if you use the following format template for a 10 -digit numeric field, the characters (parentheses, spaces, and dashes) are printed along with the numeric data.

Format Template	Printed Output
(999) 999-9999	(123) 456-7890

Changing Fonts

You can change the font and size of text for each field or label control or you can change the default font for the report.

To change fonts and size in a report

Select the control.
From the Format menu, select Font.
The Font dialog box appears.
Select the appropriate font and point size and then choose OK.

To change the default font

From the Report menu, choose Default Font.
In the Font dialog box, select the appropriate font and point size you want as the default and then choose OK.
Only the controls inserted after you changed the default font will reflect the new font settings. To change existing objects, select them all, and then change the font using the Font option on the Format menu.

Cropping a Picture or OLE Object

The picture or OLE object you inserted might not fit the frame you drew when you created the control. By default, the picture or object retains its original size. You can clip or scale it to fit into your frame.

If the picture or OLE object is bigger than the frame you created in the Report Designer, only a portion of the picture or object appears in the frame. The picture or object is anchored at the top and at the left of the frame. You cannot see the lower right portion that extends beyond the frame.

To fit a picture in the frame

In the Report Designer, create a Picture/OLE Bound Control.
In the Report Picture dialog box, select Scale picture Retain shape.

The whole picture appears, filling as much of the frame as possible while retaining its relative proportions. This protects your picture from vertical or horizontal distortion.

To fill the frame with the picture

In the Report Designer, create a Picture/OLE Bound Control.
In the Report Picture dialog box, select Scale picture Fill the frame.

The whole picture changes to fill the frame that you sized. If necessary, the picture is stretched vertically or horizontally to fit the frame.

To see an example of a report with pictures, see the report Wrapping.frx in the Visual Studio \Sample\Vfp98\Solution directory.

Centering an OLE Object

The OLE objects included in a General field can vary in shape and size. If the object in a General field is smaller than the frame, it appears in the upper left corner of the frame. You can center it to ensure that all objects smaller than the frame are centered in the frame in the report or label. File pictures don't need to be centered because they don't vary.

To center General field OLE objects

In the Report Designer, create a Picture/OLE Bound Control.
In the Report Picture dialog box, select Center picture.

The printed OLE objects are centered within the area when the report is previewed or printed.

Changing Colors of Report Controls

You can change the color of a field, label, line, or rectangle.

To change colors

Select the controls to change.
In the Color Palette toolbar, choose Foreground Color or Background Color.
Select the color you want.

Saving a Report as HTML

You can use the Save As HTML option on the File menu when you're creating a report to save the contents of a form as an HTML (Hypertext Markup Language) file.

To save a report as HTML

Open the report.
Choose Save As HTML on the File menu. (You will be asked to save the report if it has been modified.)
Enter the name of the HTML file to create and choose Save.

Setting Print Options for Controls

The general layout and band position of your controls determine the time and place they are printed. You can also set specific print options for each control. Each control has a default size based either on its value (fields and labels) or the size you created (lines, rectangles, and pictures). The length of the control on the layout defines the display width of the control. Because the value of some controls will vary from record to record, you can set the height of the control to stretch downward to display the entire value. If you don't set it to stretch, the value will be truncated within the display width. You cannot resize Label controls, but you can resize all other controls.

Printing Controls with Variable-Length Values

If you want a control to use only the space needed by its value, you can set it to stretch. For example, the values in an expression can vary from record to record. Rather than allot a fixed amount of space on the report that accommodates the longest value, you can set the control to stretch downward to display the entire value. You can set controls below the stretching control to float down the page relative to the stretching control.

Examples of controls set to stretch and float

The stretch option is available for fields, vertical lines, rectangles, and rounded rectangles.

For an example of controls that stretch and float, see the report Wrapping.frx in the Visual Studio \Sample\Vfp98\Solution\Reports directory.

To set a field to stretch with its value

Double-click the field control to display its dialog box.
Select Stretch with overflow.

Controls that are placed relative to controls that stretch must be set to float or they will be overwritten.

To set a control to float

Double-click the control to display its dialog box.
In the control s dialog box, select Float.
Caution Some of your data could be overwritten during printing if: (1) you position a field relative to the bottom of the band and include below this field another field that is positioned relative to the top of the band and can stretch; or (2) you position a field relative to the top of the band and include above this field another field that is positioned relative to the top of the band and can stretch.

You can also set lines, rectangles, and rounded rectangles to stretch. They can stretch relative to the band or, if part of a group of controls, they can stretch relative to the largest control in the group.

To set a line or rectangle to stretch

Double-click the control to display its dialog box.
In the Stretch downwards area, select an option.

To print a border around a stretchable control

Draw a rectangle around the stretchable controls.
Double-click the rectangle to display the Rectangle/Line dialog box.
In the Stretch downwards area, select Stretch relative to the tallest object in group.
Choose OK.
Drag a selection box around the rectangle.
From the Format menu, choose Group.
Selection handles appear at the corners of the rectangle. From now on you can treat all of the controls as one. The rectangle will stretch along with the stretchable field. Regardless of how far down the value in the field stretches, the rectangle will maintain its border around the field. You can place two of these groups side by side in the layout and one will not be affected by the stretching of the other.

To print one stretchable control below another

Insert the two controls one below the other in the layout.
Double-click the top control to display the control s dialog box.
In the Field Position area, select Fix relative to top band, and then choose OK.
Double-click the bottom control to display the control s dialog box.
In the Field Position area, select Float, and then choose OK.

The two record values will print in their entirety and will not overwrite each other.

Setting a Control s Print Options

You can control when and how often each report control is printed in your report. For more information about a control s print options, see Print When Dialog Box.

Suppressing Repeated Values

For field controls, you can suppress values repeated for consecutive records so that the value is printed once for the first record but doesn't appear for subsequent records until the value changes. For example, if you're printing an invoice, and one of the fields contains the date of the transaction, the date would only be printed once for transactions that took place on the same date.

To suppress repeated values

Double-click the control to display the control s dialog box.
Choose Print When to display the Print When dialog box.
Print When dialog box
In the Print Repeated Values area, select No, and then choose OK.

To repeat only on a new page or column

Double-click the control.
Choose Print When.
In the Print repeated values area, select No.
In the Also print area, select In first whole band of new page/column, and then choose OK.

To repeat when the detail band overflows to a new page or column

Double-click the control.
Choose Print When.
In the Also Print area, select When detail overflows to new page/column, and then choose OK.

Building Print Expressions

You can add expressions to a control: they are evaluated before the field prints. If the expression evaluates to false (.F.), the field will not print. If you add an expression, all of the other options in the Print When dialog box are disabled except Remove Line If Blank.

For examples of Print When conditions, see the reports Colors.frx and Ledger.frx in the Visual Studio \Sample\Vfp98\Solution\Reports directory.

To add a print expression

Double-click the control.
Choose Print When.
In the Print only when expression is true box, enter an expression.
-or-
Click the dialog button to create an expression with the Expression Builder.

Suppressing Blank Lines

Your report may include records that don't contain values for every field control. By default, Visual FoxPro leaves the area for that field blank. You can remove these blank areas to create a more pleasing and continuous display of the information.

To suppress blank lines

Double-click the control that's likely to cause blank lines in the report.
Choose Print When.
Select Remove line if blank.

Visual FoxPro will remove the line from the report if it evaluates to blank. If the field doesn t print or if the table field is empty, Visual FoxPro checks for other controls in the line. If none are found, the line is removed. If you don't select this option and no other controls are on the same line, a blank line is printed.

Setting Print Options for Groups

You can control how groups are printed in your report. Sometimes you may want each group to start on a separate page or you may want to control when the group header prints.

Setting Group Page Breaks

In addition to selecting the field or expression to be grouped, the Data Grouping dialog box allows you to specify page break options for groups.

Choosing a Group Header Option

You might want your groups to appear in the next column for multi-column reports, on a new page for forms, or with a new page numbered at 1. The Data Grouping dialog box offers four options to accomplish these tasks. You can:

Start a group on a new column.
Start each group on a new page.
Reset the page number to 1 for each group.
Reprint the group header on each page.

After you enter an expression, you can select these options from the Group Properties area.

Preventing Orphaned Group Headers

Sometimes a group may print partially on one page and then finish on the next. To avoid a group header from printing near the bottom of the page with the majority of records on the next page, you can set the minimum distance from the bottom that a group header will print. If the header would be positioned closer to the bottom of the page than the number of inches or centimeters you enter, Visual FoxPro prints the header on a new page.

To prevent orphaned group headers

From the Report menu, choose Data Grouping.
In the Data Grouping dialog box, choose or enter a value in the Start group on new page box.
Tip To determine a good value for orphan control, add the Group Header height to one to three times the Detail height.

Printing Suppressed Values When the Group Changes

If repeated values are suppressed, you may want them to print when a particular group changes.

To print repeated values when the group changes

Double-click the control to display the control s dialog box.
Choose the Print When button to display the Print When dialog box.
Select When this group changes.
The groups defined for the report appear in the box.
Select a group from the box, and then choose OK.

Repeating Group Headers

When a group continues onto the next page, you might want the group header to repeat at the top of the group of continued information. If you have multiple data groups on your report, the header on the subsequent pages will be from the last group in the group list. Place all of the controls you want to print for the group header in the header band of the last group on the list.

To repeat the group header on the next page

In the Data Grouping dialog box, select the group you want to repeat and then choose Reprint group header on each page.
If you don't want to repeat the group header, clear this check box.

Controlling Report and Label Output

You can control where report and label output is sent by using one of these keywords with the REPORT or LABEL command:

PRINT
PREVIEW
FILE

If you don't use any keywords, the report is sent to the screen or active window.

Selecting Records to Print

When you print a report, you might want to limit the number of records that appear in the report by providing selection criteria. You can:

Choose a scope of records by specifying a quantity or range.
Build a FOR expression that selects records that match a condition.
Build a WHILE expression that selects records until one record is found that doesn't match a condition.

You can use any combination of these options. The WHILE expression overrides the other criteria.

Printing a Quantity or Range of Records

One way you can limit the number of records is to specify a quantity or range of records. Using the Scope option, you can select a single record or a group of records positioned sequentially in the file.

Note The active index and the current record pointer affect the results of the scope options Next and Rest. For example, the next record in a table indexed by last name is probably different than one in a table indexed by state. This doesn't affect the Record option because the number for a record doesn't change when the table is indexed.

To select a limited number of records

From the File menu, choose Print.
In the Print dialog box, choose Options.
In the Print Options dialog box, choose Options.
Report and Label Print Options dialog box
In the Print and Label Print Options dialog box, choose Scope.

Select the appropriate scope option.

To print	Choose this scope option
Every record from the source file	ALL
A range of records starting with 1	NEXT
A specific record by number	RECORD
The current record plus all of those after it to the end of the file	REST

Visual FoxPro prints the report using data from the records within the scope you selected.

Printing Records That Match a Condition

If the records you want to select are not sequential within the table, you can build a logical expression that specifies selection criteria a record must meet to be printed. For example, you can choose to print all records with a particular value in a field.

To enter criteria for selecting records

From the File menu, choose Print.
In the Print dialog box, choose Options.
In the Print Options dialog box, choose Options.
In the Print and Label Print Options dialog box, choose Scope.
In the For box, enter a FOR expression.
-or-
Make sure the records sources used by the report are open, and then choose the For button to use the Expression Builder.
Note You don't need to include the FOR command in the expression. For example, type country = "Canada" to see only Canadian data.
Visual FoxPro evaluates all of the records and prints the report using those records that match the condition in the expression.

Controlling Selection of Records to Print

When printing, you can specify a condition that must be met to continue evaluating and selecting records. You enter this condition as a WHILE expression. As long as the WHILE expression remains true, Visual FoxPro processes the data source. After finding a record that doesn't meet the condition, Visual FoxPro ends the evaluation process and prints the selected records. This option allows you to select records based on information outside the values contained in the fields.

Tip If you use a WHILE expression on a file that hasn't been indexed, the selection process may end before evaluating all of the appropriate records. Before printing the report, be sure the source table has the appropriate index active for the WHILE expression you want to use.

To enter criteria for ending record selection

From the File menu, choose Print.
In the Print dialog box, choose Options.
In the Print Options dialog box, choose Options.
In the Print and Label Print Options dialog box, choose Scope.
In the While box, enter a WHILE expression.
-or-
Choose the While button to use the Expression Builder.
Note You don't need to include the WHILE command in the statement. For example, type sales > 1000 to see only sales above one-thousand dollars.
Visual FoxPro prints the report using the records it evaluates while the expression is true.

Printing Reports and Labels

If you want to send the report to the printer, you can send it directly to the printer or display the Print Setup dialog box.

To send a report to the printer

Use the TO PRINTER clause of the REPORT or LABEL command.

For example, the following code sends the report MyReport to the default printer and stops the report from printing on the screen:

REPORT FORM MYREPORT.FRX TO PRINTER NOCONSOLE

To display the Print Setup dialog box before sending the report to the printer

Use the TO PRINTER PROMPT clause of the REPORT or LABEL command.

For example, the following code displays the Print Setup dialog box, sends the report MyReport to the default printer, and stops the report from printing in the active window:

REPORT FORM MYREPORT.FRX TO PRINTER PROMPT NOCONSOLE

Previewing Reports and Labels

If you want to display a preview of the report, you can send it to the Preview window in the Report Designer.

To preview a report

Use the PREVIEW clause of the REPORT command

For example, the following code displays the report in a modal window:

REPORT FORM MYREPORT.FRX PREVIEW

By default, the Preview window is modal but allows access to the Preview toolbar. If you want to make the preview modeless, you can add the keyword NOWAIT to the REPORT command.

For example, the following code displays the report in a modeless window:

REPORT FORM MYREPORT.FRX PREVIEW NOWAIT

If you want to preview the results in a specific window, you can include the WINDOW clause to specify a window created with DEFINE WINDOW.

REPORT FORM MYREPORT.FRX PREVIEW WINDOW MYWINDOW

Printing Reports to File

If you want to create an electronic version of the report, you can send it to a file formatted for your printer or to an ASCII file. Sending reports to a file allows you to print them in a batch on your printer at a later time.

If you want to create an ASCII file, you can create a file that includes only the text, dashes, and plus signs to represent lines and shapes. Font and color choices are not included. You can also specify the number of characters to place on each line and the number of lines to place on each page.

To print a report to an ASCII file

Use the FILE and ASCII keywords of the REPORT command.

The following example defines the variables for the ASCII page, then prints a report called Myreport.frx to an ASCII file name Myfile.txt.

Printing to an ASCII File
Code	Comment
`_asciirows = nLines`	Define the number of lines per page.
`_asciicols = nChars`	Define the number of characters per line.
`REPORT FORM MYREPORT.FRX TO FILE MYFILE.TXT ASCII`	Run the report.

Saving a Report as HTML

You can use the Save As HTML option on the File menu when creating or modifying a report to save the contents of the report as an HTML (Hypertext Markup Language) file.

To save a report as HTML

Open the report.
Choose Save As HTML on the File menu. This option is only available when the report has been saved to disk.
Enter the name of the HTML file to create and choose Save.

Integrating Queries and Reports

After you've created the components of your application, you can integrate them. The following illustration shows some ways you can add queries and reports to your application.

Some ways to integrate queries and reports

You can add code that executes a query or a report to the following objects in your application.

A button on a form. For more information about forms and buttons, see Chapter 9, Creating Forms.
An item on a menu or a button on a toolbar. For more information, see Chapter 11, Designing Menus and Toolbars.

To add a query, view, or program

Add a DO or USE command to the code behind a command button on a form, a button on a toolbar, or a menu item.
For example, add code similar to one of the following lines:
```
DO MYQUERY.QPR DO MYPROGRAM.PRG USE myview 
```

You have several options for integrating reports into your application.

If you want the user to simply start the report and collect the printout, you can have the user initiate a report by adding the REPORT command to a control on a form, a command on a menu, or a button on a toolbar.
If you want to allow your user to enter some variables used in the report, you can gather values from the user just as you did for queries. For example, the user could enter a specified date range that the report will include. For more information, see Collecting User Input with Queries later in this chapter.
If you want the user to create custom reports, you can offer the user the ability to create new reports or modify existing reports with the Report Designer.

To run reports and labels

Use the REPORT or LABEL commands.
For example, you could use code similar to one of the following lines:
```
REPORT FORM MYREPORT.FRX LABEL FORM MYLABEL.LBX 
```

To modify reports and labels

Use the MODIFY REPORT or MODIFY LABEL commands.
For example, add code similar to one of the following lines:
```
MODIFY REPORT MYREPORT.FRX MODIFY LABEL MYLABEL.LBX 
```

To create reports and labels

Use the CREATE REPORT or CREATE LABEL commands.
For example, you could use code similar to one of the following lines:
```
CREATE REPORT MYREPORT.FRX CREATE LABEL MYLABEL.LBX 
```

Collecting User Input with Queries

If you want to collect values from a form, you can use variables in a SELECT SQL statement and then use them immediately in the statement, or execute the statement later.

To collect values for immediate use, you can either explicitly name the form or use a shortcut reference for the form in your SELECT SQL statement. In this example, shortcut reference is in the WHERE clause.

Collecting Values Using Shortcut References in a SELECT - SQL Statement

Code Comment

SELECT * ;    FROM tastrade!customer ;    WHERE customer.country = ;     THISFORM.ControlName1.Value ;    AND customer.region =  THISFORM.ControlName2.Value ;    GROUP BY customer.postal_code ;    ORDER BY customer.postal_code,  customer.company_name

Use THISFORM as a shortcut reference for the currently active form and substitute control names for ControlName1 and ControlName2.

If you don't want to use references to the control, you can define variables in the code. Use code variables if you want to store the values from a form but don't necessarily expect to use them while the form is active.

Collecting Values for Later Use

Code	Comment
`cValue = THISFORM.ControlName.Value`	Define the variable.
`SELECT * ; FROM tastrade!customer ; WHERE customer.country = cValue ; GROUP BY customer.postal_code ; ORDER BY customer.postal_code, ; customer.company_name`	Use the variable you defined in the SELECT - SQL statement.

If you don't define the variable before you run the query, an error message appears stating that the variable couldn't be found. If the variable isn't defined in code, Visual FoxPro assumes that the variable is pre-initialized.