Share via

Using Expressions and Functions in Field Control in a Report

  • Article

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

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

To add table fields from the toolbar

  1. From the Report Controls toolbar, insert a Field control.

  2. In the Report Expression dialog box, choose the dialog button after the Expression box.

  3. 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.

  4. Choose OK.

  5. In the Report Expression dialog box, choose OK.

After entering the expression, you can change the format or set printing, position, or stretch options.

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

  1. 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.

  2. In the Report Expression dialog box, select the dialog button after the Expression box.

  3. In the Expression Builder, select ALLTRIM(expC) from the String box.

    The string function appears in the Expression box with expC selected.

  4. Double-click the first field name you want to appear in the control.

    The field name replaces the expC.

  5. Type a plus sign after the field name or select + from the String functions box.

  6. Type , or select Text from the String functions list and then enter a comma.

  7. Repeat steps 3 and 4 for additional fields to complete the expression and then choose OK.

  8. 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.

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 FoxPro ...\Samples\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.

Inserting the Current Date

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

To insert the current date

  1. From the Report Controls toolbar, insert a Field control.
  2. In the Report Expression dialog box, select the dialog button after the Expression box.
  3. In the Expression Builder, select DATE( ) from the Date list.
  4. Choose OK.
  5. 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

  1. From the Report Controls toolbar, insert a Field control.

  2. In the Report Expression dialog box, select the dialog button after the Expression box.

  3. In the Expression Builder, select _pageno from the Variables list.

  4. Choose OK.

  5. 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.

See Also

Refining Page Layout | Defining Report Variables | Creating Reports and Labels | SELECT - SQL | Adding a General Field | Selecting, Moving, and Resizing Report Controls | Setting a Control's Print Options