Defining a Virtual Field

In this section:

How to:

Reference:

A virtual field can be used in a request as though it is a real data source field. The calculation that determines the value of a virtual field is performed on each retrieved record that passes any screening conditions on real fields. The result of the expression is treated as though it were a real field stored in the data source.

You can define a virtual field in the following ways:

Tip: If your environment supports the KEEPDEFINES parameter, you can set KEEPDEFINES to ON to protect virtual fields from being cleared by a subsequent JOIN command.


Top of page

x
Reference: Usage Notes for Creating Virtual Fields

Top of page

x
Procedure: How to Create a Virtual Field

To define a virtual field (Define object) in a procedure:

  1. Right-click the procedure in the Procedures folder and choose Edit in Developer Studio tool from the context menu. The Procedure Viewer opens.
  2. Click a component connector (yellow diamond) at the point where you want to include the virtual field in the procedure, then click the Define button on the component connector toolbar.
  3. The Open dialog box opens. Select the Master File from which you want to create the virtual field and click Open.

    The Define dialog box opens. For details see Define Tool.

  4. Type the name of the virtual field in the Field input box.
  5. Enter an expression in the expressions box. See specific procedures for details on assigning alphanumeric, numeric, date, and CLOB formats.

    For information on expressions, see the Creating Reports With WebFOCUS Language manual.

    You can also use a function in an expression by clicking the Functions button on the Define dialog box. For details on functions, see the Using Functions manual.

Note: If you want to make a virtual field available for use with all procedures during your session, issue the DEFINE command from the Command Console. The virtual field is retained for the duration of the session.


Top of page

x
Procedure: How to Assign an Alphanumeric Format With the Define Tool

In the Define tool window:

  1. Click the Format button.

    The Format dialog box opens.

  2. Select Alphanumeric as the Format Type.

    The default length is 20. It appears in the Length input area.

  3. To assign a different length, specify a number between 1 and 4096 in the Length input area.
  4. Click OK.

The Format dialog box closes.

For more information on field formats, see Assigning Field Formats in the Creating Reports With Report Painter manual.


Top of page

x
Procedure: How to Assign a Numeric Format With the Define Tool
  1. Click the Format button.

    The Format dialog box opens.

  2. Select one of the format option buttons in the Format Types section:

    The default length appears in the Length input area. The Decimal input area shows the number of decimal places for Floating Point, Decimal, and Packed.

  3. To assign a different length, specify a number between 1 and 9 for Floating Point, between 1 and 11 for an Integer, between 1 and 20 for Decimal, or between 1 and 33 for Packed in the Length input area.
  4. To assign a different number of decimal places for Floating Point, Decimal, or Packed, specify the desired number in the Decimal input area.
  5. If you wish to include numeric display options, select these options in the Edit Options section.
  6. Click OK.

The Format dialog box closes.

For more information on field formats, see Assigning Field Formats in the Creating Reports With Report Painter manual.


Top of page

x
Procedure: How to Assign a Date/Time Format With the Define Tool

In the Define tool window:

  1. Click the Format button.

    The Format dialog box opens.

  2. Click the Date/Time button from the Format Type section.

    The Date and Time Formats dialog box opens.

  3. Select Date as the Field Format.

    The Date section options are enabled.

    Note: The Date and Time Formats dialog box enforces valid Date-only and DateTime format combinations, based on the input entered and the field selected. Some options may not be available for your selections.

  4. Select a date format from the Format drop-down list.

    When a date format is selected, the Format String and Sample Date are displayed for the format selected.

  5. Optionally, you may select the Month Name, Day Name, Prefix, and Separator options.
  6. Click OK.

    The Date and Time Formats dialog box closes. The selected format appears in the Date/Time Format section of the Format dialog box.

  7. Click OK to close the Format dialog box.

Note: Administrators and developers should note that date options set in a virtual field will override date options set in the Master File or with a SET command.


Top of page

x
Reference: Define Tool

The following image shows the Define tool.

Define tool

The Define tool has the following fields or options:

Field

Displays the name of the temporary field.

To create a field, type the name that you want to use in the Field text box.

To edit a field, select the field. When you select a field, the corresponding information appears in the Format box, and the expression box. You can edit the information, eliminate the field, or run it.

Add

When selected, it indicates that the current temporary field definitions should be added to the list of temporary fields previously defined for the same data source.

If this button is not selected, the current set of temporary fields replaces those previously defined for the same data source.

In Application View, this action marks all definitions created during the current use of the Define tool to be added to other virtual fields defined for the same data source during earlier use of the tool.

Note: You can define and execute several virtual fields during a single use of the Define tool. However, unless you select Add, running the current list will erase other virtual fields created outside of the Master File for the same data source. Virtual fields created in the Master File remain in effect.

Clear

When selected, it clears any current temporary field definitions you previously added to the list of temporary fields.

Format button

Opens the Format dialog box, where you assign a format to the temporary field.

Format box

Displays the field type, field length, and display options. The field type can be alphanumeric, numeric, or date/time.

Options button

Opens the Options window, which enables you to establish a segment location for a temporary field in the associated Master File, and/or assign attributes to set how missing values in the virtual field are handled.

Expressions box

Displays the expression used to evaluate the field.

Type the expression or use the Fields list, calculator, and functions list to help you create the expression.

Calculator buttons

Provide numbers and operators that you can use to create numeric, alphanumeric, Boolean, and conditional expressions.

Click the desired number or operator to add it to the expression box.

  • To enclose a value in parentheses, click the ( ) key in the calculator. Parentheses affect the order in which the specified operations are performed.
  • To enclose a value in single quotation marks, click the ' ' key in the calculator. Use single quotation marks to enclose alphanumeric and date literals.
  • To convert entries in the expression box to uppercase, click the U key in the calculator. Note that field names are case-sensitive.
Check

Displays the Define phrase in code, and specifies any errors.

New

Clears the entry fields of the tools, including the Field text box and the corresponding expression. It also returns the format to the default value D12.2, and places the cursor in the Field text box so you can begin to create a new field.

Delete

Deletes the temporary field identified in the Field text box. The field is no longer available.

Functions

Opens the Function Arguments dialog box, which lists all available built-in functions. (A function is a program that returns a value.)

Double-click the desired function to add it to the expression box. Then, in the expression box, highlight each argument and substitute the value or field name you wish to use. For details, see the Using Functions manual.

Defined Fields

Lists the names of the temporary fields already associated in the Master File.

Fields List

Lists the fields defined in the Master File.



Example: Creating a Virtual Field

Using information in the sample data source EMPLOYEE, the following example shows how to create a virtual field, INCREASE, to calculate the annual salary increase each employee will receive. This example assumes that you have already created a procedure with which you want to use this virtual field.

  1. Open a procedure in which you want to create a virtual field.
  2. Select the EMPLOYEE Master File and click Open.
  3. Open the Define tool from the component connector toolbar.
  4. Type INCREASE in the Field input box.
  5. Click the Format button.

    The Format dialog box opens.

  6. Confirm that the Decimal option button is selected under Format Types.
  7. Click the down arrow in the Length input area to specify the field length 8. Leave the number 2 in the Decimal field.
  8. Select the Floating dollar--M option in the Edit Options list box.
  9. Click OK to make the changes and return to the previous dialog box.
  10. Click the Fields List tab, then double-click CURR_SAL.
  11. Enter the following by typing or using the number and operator buttons in the Expressions window:

    * .05

    The expression for the Define field now appears as CURR_SAL * .05.

  12. Click OK.

You can then select the new field, INCREASE, in the Fields window of the reporting tools.


Top of page

x
Procedure: How to Specify Missing Value Attributes Using the Define Tool
  1. Open the Define tool by:
    • Select Define from the component connector toolbar in the Procedure Viewer.

      or

    • From Report Painter, right-click anywhere in the Fields tab of the Object Inspector and select New Define Virtual Field.

    The Define tool opens.

  2. Create a Define expression.
  3. Click the Options button and select the Override missing values handling check box to specify how the missing value attributes are handled. The Options dialog box is shown in the following image.

    Options

  4. Click OK to close the Options dialog box.

For more information about Missing Field Values, see MISSING Attribute in a DEFINE or COMPUTE Command in the Handling Records With Missing Field Values chapter of the Creating Reports With WebFOCUS Language manual.


Top of page

x
Procedure: How to Establish a Missing Segment Location Using the Define Tool
  1. Open the Define tool by:
    • Select Define from the component connector toolbar in the Procedure Viewer.

      or

    • From Report Painter, right-click anywhere in the Fields tab of the Object Inspector and select New Define Virtual Field.

    The Define tool opens.

  2. Create a Define expression.
  3. Click the Options button.
  4. Select the Associate the temporary field with the real field that is selected check box and select a field. The Options dialog box is shown in the following image.

    Options

  5. Click OK to close the Options dialog box.

For more information about Missing Field Values, see Establishing a Segment Location for a Virtual Field in the Handling Records With Missing Field Values chapter of the Creating Reports With WebFOCUS Language manual.


Top of page

x
Defining Multiple Virtual Fields

How to:

You may wish to have more than one set of virtual fields for the same data source, and to use some or all of the virtual fields in the request. The Add option enables you to specify additional virtual fields without clearing existing ones. If you omit the Add option, previously defined virtual fields in that data source are cleared.

If you want to clear a virtual field for a particular data source, use the Clear option.



x
Procedure: How to Add a Virtual Field to a Set of Virtual Fields
  1. In the Define dialog box, type the field name in the Field input box.
  2. Enter the desired expression in the expression box.

    For information on expressions, see the Creating Reports With WebFOCUS Language manual.

  3. Select the Add option button.
  4. Click OK.

Top of page

x
Displaying Virtual Fields

How to:

You can display all virtual fields with the ? DEFINE command. You can also access this information in the Define tool.



x
Procedure: How to Display Virtual Fields

Click the Defined Fields tab in the Define tool.


Top of page

x
Clearing a Virtual Field

How to:

The following can clear a virtual field created in a procedure:

Unlike fields created in a procedure, virtual fields in the Master File are not cleared in the above ways.



x
Procedure: How to Delete a Virtual Field

In the Define tool window, with the field in the Field text box, click Delete.

The field is no longer available.


Top of page

x
Increasing the Speed of Calculations in Virtual Fields

Virtual fields can be compiled into machine code in order to increase the speed of calculations.

When you enable compilation of expressions, expressions in IF criteria are also compiled. In some cases, an expression in an IF test may contain too many elements to be compiled (the limit is 8192). In this case, a FOC1975 message is generated to indicate that the expression could not be compiled. However, the report completes successfully, and the expression is evaluated correctly without compilation.


Top of page

x
Applying Dynamically Formatted Virtual Fields to Report Columns

How to:

Dynamic formatting enables you to apply different formats to specific data in a column by using a temporary field that contains dynamic data settings. You can create a dynamic format by writing an expression using the Define tool inside or outside of Report Painter.

Before you can format a report column using the dynamic format, you must create the report, then apply the temporary field to a column in the report. For example, you can create a temporary field that contains different decimal currency formats for countries like Japan (which uses no decimal places) and England (which uses 2 decimal places). These currency formats are considered dynamic formats. You can then apply the temporary field containing the dynamic formatting to a Sales column. In a report, the Sales column reflects the different currency formats for each country.



x
Procedure: How to Create a Virtual Field Containing Dynamic Formatting

In the Define tool:

  1. Type the name of the virtual field in the Field input box.
  2. Click Format and type A8 (Alphanumeric, 8 characters). Click OK.
  3. Type the expression in the Expressions window or use the Fields list, calculator, or functions list to help you create the expression. The expression should contain the parameter values necessary for defining the dynamic formatting. All expressions must be written in uppercase.
  4. Click OK.

You can apply dynamic formatting to a report column.



x
Procedure: How to Format a Report Column Using Dynamic Formats
  1. Right-click a report column in Report Painter and select Format.
  2. Click the Dynamic option button in the Format Types area.
  3. Select the field with the desired dynamic format from the Format Field drop-down list.
  4. Click OK to format the report column.

For more information about Report Painter, see the Creating Reports With Report Painter manual.



Example: Setting Appropriate Formats for Different Currencies

The following example demonstrates how the dynamic field format can be used to set different currency formats for specific countries in a report.

  1. Open the procedure for which you want to create the virtual field, then open the Define tool from the component connector toolbar.

    The Open dialog box opens.

  2. Select the CAR Master File and click Open.

    The Define tool opens.

  3. Type MYFORMAT in the Field box.
  4. Click the Format button. Select the Alphanumeric option button, and specify 8 as the length. Click OK.
  5. Click the Functions button to open the Functions Arguments dialog box:
    1. Select DECODE from the Select a function drop-down list. The DECODE function appears with placeholders for its arguments.
    2. Select COUNTRY as the DECODE field name.
    3. Enter the actual and display values by typing the following placeholder fields with specific values that define the decimal precision for each currency: ENGLAND P15.2C JAPAN P15.0 ELSE P15.2M.
    4. Enter the default value P15.2M to be assigned if the code is not found among the list of codes.
    5. Click OK to return to the Define tool. The DECODE function appears as the Define expression.
  6. Click OK to close the Define tool.
  7. Select Report from the Connector toolbox. At the Open dialog box, select CAR from the list of Master Files and click Open.
  8. In Report Painter, double-click the COUNTRY and SALES fields in the Fields list.
  9. Select the SALES column and click the Sum button on the Columns toolbar.
  10. Right-click the SALES column and select Format.
  11. Click the Dynamic option button and select the MYFORMAT field from the Format Field drop-down list.
  12. Click OK to apply the formats associated with the MYFORMAT field to the SALES column.
  13. Select the COUNTRY column and click the By button on the Columns toolbar.
  14. Run the report, as shown in the following image.

Run the report

Notice that the SALES column displays the format variations you defined for each country in the DECODE function.


WebFOCUS