Creating Expressions for Temporary Fields

In this section:

How to:

Reference:

When you create a temporary field for a report (with the Define tool or the Report Options Computes tab) or in a Master File (with the Synonym Editor), you must specify how to derive the new field value by writing an expression.

For more information, see the Describing Data With Graphical Tools manual.


Top of page

x
Procedure: How to Specify a Field in an Expression With the Define Tool

In the Define tool window, double-click the desired field in the Fields window.

The field name is added to the expression box.

Note: The format of the field you specify in an expression must be consistent with the format of the temporary field you are creating. For example, if you are creating an alphanumeric temporary field, the fields you use in the expression must also be alphanumeric.


Top of page

x
Procedure: How to Use a Function in an Expression With the Define Tool

In the Define tool window:

  1. Click the Functions button.

    The Function Arguments dialog box opens.

  2. A list of predefined functions are grouped into categories. Select a category from the drop-down list.

    Note: A list of predefined functions are grouped into categories that include Character, Data Source and Decoding, Date and Time, Format Conversion, Numeric, and System. Each of the available functions is a program that returns a value. See the Using Functions manual for complete information on functions. There is also a list of user defined functions available. For more information on user defined functions, see Using User Defined Functions.

  3. Select a function.
  4. Enter any other criteria for the function, such as the value, length, format, and so on.
  5. Click OK. The function and placeholders for its arguments are added to the expression.
  6. Repeat steps 3 through 6 for each argument.

Top of page

x
Procedure: How to Specify a Field in an Expression With the Computes Tab

In the Report Options Computes tab, click the Fields button and double-click the desired field.

The field name is added to the expression box.

Note: The format of the field you specify in an expression must be consistent with the format of the temporary field you are creating. For example, if you are creating an alphanumeric temporary field, the fields you use in the expression must also be alphanumeric.


Top of page

x
Procedure: How to Specify Missing Value Attributes Using the Computes Tab
  1. Open the Computes tab by performing one of the following actions.
    • In Report Painter, click Computes from the Report menu.

      or

    • Select the Compute icon from the Setup toolbar.

      or

    • Right-click anywhere in the Fields tab of the Object Inspector and click New Compute Virtual Field.

    The Report Options dialog box opens at the Computes tab.

  2. Create a Computes expression.
  3. Click the Options button and select the Override missing values handling check box to specify how the missing value attributes are handled.

  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 Use a Function in an Expression With the Computes Tab
  1. In the Report Options Computes tab, click the Functions button.

    The Function Arguments dialog box opens. Each of the available functions is a program that returns a value. For a list of functions, see the Using Functions manual.

  2. Select a category from the drop-down list. The available categories are All, Character, Data Source and Decoding, Date and Time, Format Conversion, Numeric, and System.
  3. Select a function.
  4. Enter any other criteria for the function, such as the value, length, format, and so on.
  5. Click OK. The function and placeholders for its arguments are added to the expression.
  6. Repeat steps 3 through 6 for each argument.


Example: Decoding Values With a Function

The DECODE function assigns values based on the coded value of an input field. DECODE is useful for giving a more meaningful value to a coded value in a field.

Note: You can use the DECODE function by supplying values directly in the function or by reading values from a separate file.

For example, using information in the sample data source EMPLOYEE, use the DECODE function to assign a department code based on the current jobcode of the employee. Specifically, assign the department code of MANAGER to all the managerial jobcodes. Assign the department code of ADMINISTRATIVE to all the administrative jobcodes, and the department code of SYSTEMS for all the programming jobcodes. DECODE expands (decodes) these values to ensure correct interpretation on a report.

  1. Create the report:
    • Open the employee.mas data source in Report Painter.
    • Select LAST_NAME as the By sort field.
    • Select CURR_JOBCODE and JOB_DESC as Detail sort fields.
  2. Click Computes from the Report menu, or click the Compute icon from the Setup toolbar.

    The Report Options dialog box opens at the Computes tab.

  3. Type DEPT_CODE as the Field name for the Compute.
  4. Type A15 as the Format for the field.
  5. Click the Functions button.

    The Function Arguments dialog box opens. Each of the available functions is a program that returns a value. For a list of functions, see the Using Functions manual.

  6. Click Data Source and Decoding from the Select a category drop-down list.
  7. Click DECODE from the Select a Function list.

    The DECODE options appear. You may supply values in the function or read values from a file.

  8. To supply values in the function:
    • Click Supply Values in the Function as the DECODE option. This is the default option.
    • Click CURR_JOBCODE as the fieldname.
    • Type a list of the actual jobcode values and the display value for the jobcode. For example, type:
      • A07 as the actual_value, and ADMINISTRATIVE as the display_value.
      • A17 as the actual_value, and MANAGER as the display_value.
      • B02 as the actual_value, and SYSTEMS as the display_value.
      • B03 as the actual_value, and SYSTEMS as the display_value.
      • B04 as the actual_value, and SYSTEMS as the display_value.
      • B14 as the actual_value, and ADMINISTRATIVE as the display_value.
    • Optionally, enter the default value.

      Note: Default is the value assigned if the code is not found. If you omit a default value, DECODE assigns a blank or zero to non-matching codes.

      The following image shows the Function Arguments dialog box with these options.

    • Click OK to close the Function Arguments dialog box.
  9. To read values from a file:
    • Click Read Values from a File as the DECODE option.
    • Click CURR_JOBCODE as the fieldname.
    • Enter the ddname for the file.

      A logical name or a shorthand name that points to the physical file containing the decoded values is a ddname. You may create a ddname name with the Allocation Wizard. For details, see the Developing Reporting Applications manual.

    • Optionally, enter the default value.

      Default is the value assigned if the code is not found. If you omit a default value, DECODE assigns a blank or zero to non-matching codes.

      The following image shows the Function Arguments dialog box with these options.

    • Click OK to close the Function Arguments dialog box.

    The function and placeholders for its arguments are added to the expression in the Computes tab.

  10. Click OK to close the Report Options dialog box.

    The Compute field is added to Report Painter.

  11. Run the report.

The report, in the following image, shows the decoded department code values assigned to the current jobcodes in the data source.


Top of page

x
Reference: Report Options Computes Tab

The Report Options Computes Tab is shown in the following image.

The Computes tab includes the following fields and options for creating expressions.

Field Box

Displays the name of the calculated value. Assign a field name to the value you wish to calculate.

When editing a calculated value, click the down arrow on the Field combo box, and select the field you wish to edit. 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.

Format Box

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

Format Button

Opens the Format dialog box, where you can assign format information to the calculated value.

Expression 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

Provides numbers and operators that you can use to create numeric, alphanumeric, 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.
New Button

Clears the entry box, including the Field combo box and the corresponding expression. It also returns the format to the default value D12.2, and places the cursor in the Field combo box so you can begin to create a new field. The New button becomes available for use once a name for the expression is entered in the Field box. Once a name is entered and you click the New button, the previous expression is saved and can be retrieved by selecting that expression from the Field drop-down list.

Delete Button

Deletes the current expression and clears the tab.

Functions Button

Opens the Function Arguments dialog box, which lists all available built-in functions. A list of predefined functions are grouped into categories that include Character, Data Source and Decoding, Date and Time, Format Conversion, Numeric, and System. Each of the available functions is a program that returns a value. There is also a list of all available user defined functions. For more information on user defined functions, see Using User Defined Functions.

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.

Fields Button

Opens the Insert Field window. The Field tab lists all fields in the data source. The Column tab lists all fields being used in the report. If there are no fields being used, the Column tab will display all fields in the data source. Click Insert while a field is highlighted to insert that field into the expression.

Options Button

Opens the Options window, which enables you to establish how to interpret and represent missing values for the virtual field.

OK Button

Checks the syntax for your calculated value and displays a warning message. You cannot exit until the errors are fixed. When the syntax is correct and you click OK, the Computes tab closes. You can continue building your report.


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
Using the Expression Builder

How to:

Reference:

The Expression Builder enables you to create expressions quickly by selecting fields, relations, operators, and values from lists. You can base selection criteria on a specified value, a variable value, or a field value.

You can access the Expression Builder by clicking Where, If, or Where Total from the Where/If drop-down menu, as shown in the following image.

The Expression Builder dialog box is shown in the following image.

The Expression Builder is divided into four sections. The Data section is located in the upper-left of the Expression Builder. The Criteria section is located to the lower-left of the Expression Builder. The Advanced section is located in the lower-right of the Expression Builder. The Expression Grid is located to the upper-right of the Expression Builder.

The Data section displays a list of all fields in the data source. Double-click or drag the field into the Expression Grid to build an expression using the options provided. You can also drag fields to the Criteria section and the Advanced section, once it is enabled.

The Criteria section displays which expression you are working on, as well as which expressions, of the same type, you have already created. If you double-click a field and it is added to the Expression Grid, the field will be shown in the Criteria section. Alternatively you can drag a field into the Criteria section begin working on a new expression. Dragging more than one field into the Criteria section allows you to create multiple of whatever type of statement you selected (Where, If, or Where Total). For example, if you clicked Where to open the Expression Builder, then dragging more than one field into the Criteria section will create multiple Where expressions. Selecting an expression in the Criteria section will show you the details of that expression in the Expression Grid and/or the Advanced section. The following image shows the Criteria section with multiple Where expressions.

Note: The type of expression you are creating is shown next to the Criteria section. For example, in the image above, a Where expression is being created. Therefore (WHERE) is displayed next to Criteria. If you were creating an If expression, that section would say (IF). If you were creating a Where Total expression, that section would say (WHERE TOTAL).

The Advanced section is where, instead of creating an expression using the Expression Grid, you are creating it using syntax. This is for if you do not want to use the Expression Grid to create an expression. The Advanced check box is only available to be checked once a field is in the Criteria or Expression Grid. Once the Advanced check box is checked, the Advanced section, the Function button, and the Variable button are available for use. The following image shows the Advanced section with the Advanced check box checked and an expression entered.

The Expression Grid is where you build an expression using the drop-down options available. You can add more fields to the Expression Grid to make a more complex expression by using OR and AND. These options are explained in the Basic Expression Builder Dialog Box. An image of the Expression Grid with an expression created is shown in the following image.



x
Reference: Expression Builder Dialog Box

The Expression Builder dialog box, which is shown in the following image, has the following sections/options:

Data section

Displays a list of all of the fields in the data source. Double-click or drag a field to add it to the Expression Grid.

Expression Grid

Create an expression by using the drop-downs in correlation with a field.

You can delete expressions from the Expression Grid using the Delete key or right-click Delete option when either the And/Or or Column to filter columns are selected. If you use the Delete key or right-click Delete option on any other column, it will only delete that column option. You can only delete entire expressions, using the Expression Grid, when there are multiple expressions present. If you want to delete a single expression, you must do it from the Criteria section.

And/Or

Displays the keyword used in the expression listed in the Expression list box. You must select more than one field for the expression to activate this option.

Parentheses

Allows you to add either one, two, or three parentheses before and after an expression.

Column to filter

The field you clicked or dragged in from the Data section. This field can be changed after being added by clicking the drop-down list and selecting a different field.

Logical Relation

Displays a list of possible relations between the selected data source field and the value, parameter, or other field that WebFOCUS will compare it to. Select a relation to activate the Compare Type column.

The following relations are available from the drop-down list:

  • equals
  • does not equal
  • is greater than
  • is greater than or equal to
  • is less than
  • is less than or equal to
  • is
  • is not
  • contains
  • does not contain
  • matches the pattern
  • does not match the pattern
  • is like
  • is not like
  • is missing
  • is not missing
  • includes
  • excludes
  • is from
  • is not from
  • is in literal list
  • is not in literal list
  • is in external file of literals
  • is not in external file of literals
  • none

Note: The is and is not relations are only available for an IF statement. The matches the pattern, does not match the pattern, is like, is not like, is in literal list, is not in literal list, is in external file of literals, and is not in external file of literals relations are only available for a WHERE statement.

Compare Type

Indicates the nature of the comparison you wish to make to the field selected in the field section.

The following is a list of the available Compare Types and a brief description of what each is:

  • Value is a set value the user creates, using the Multiple or Single Value Builder, in the Compare Value section.
  • Field is a selected field the user specifies, using the Multiple or Single Value Builder, from the Compare Value section.
  • Parameter is a parameter created by the user, using the Variable Editor, in the Compare Value section.
  • Parameter (Dynamic) is a parameter option that allows a user to multiselect values using a button. This option automatically generates the required syntax so that the Variable Editor does not need to be invoked.
  • Parameter (Static) is a parameter option that presents a value list to select from. This option automatically generates the required syntax so that the Variable Editor does not need to be invoked.
  • Parameter (Simple) is a parameter option that prompts the user to enter a value. This option automatically generates the required syntax so that the Variable Editor does not need to be invoked.
  • Function is a function created by the user, from the Compare Value section, using the Function Arguments dialog box. For more information, see the Using Functions manual or click the Help button from the Function Arguments dialog box.
  • Import Values is a list of values, created by the user, using the Multiple or Single Value Builder, in the Compare Value section.

    Note: The external file should be a text file with new line delimiters.

  • Other allows you to enter your own expression.

    Note: After choosing Other and double-clicking on Compare Value to enter a value, you will be prompted with the following warning message.

    If using a literal value, it must be enclosed in single quotes. 
    Please use "Compare Type" "Value" instead for quotes to be added automatically.
     
    You can choose to not show this message again by selecting the option at the bottom of the warning window.

Compare Value

Specifies the literal value, parameter, or other field to which the selected field is compared.

The choices available here are dependent on the selection you make in the Compare Type column.

Criteria

The Criteria section shows the different expressions you created. Select an expression in the Criteria section to show the expression details in the Expression Grid and/or the Advanced section.

Advanced check box

This option can only be checked if there is an expression in the Criteria or Expression Grid section. This option will enable you to use the Advanced section, the Function button, and the Variable button.

Advanced section

In this section you can type an expression out rather than using the Expression Grid.

Function

Only available when Advanced is checked. Opens the Functions Arguments dialog box to assist in the creation of an expression that is being made with the Advanced section.

Variable

Only available when Advanced is checked. Opens the Functions Arguments dialog box to assist in the creation of an expression that is being made with the Advanced section.

Delete

Deletes an expression.

Up

Moves an expression up one.

Down

Moves an expression down one.



x
Procedure: How to Display Records Based on Specified Values

In the Expression Builder dialog box:

  1. Select a field name from the Data section.

    The field is added to the Expression Grid.

  2. Select a relation from the Logical Relation column.
  3. In the Compare Type column, click Value.
  4. Double-click the Compare Value column. The Multiple Value Builder dialog box opens.
  5. Click the Select a field button and select a value from the list.
  6. Double-click the value to add the value to the list.

    Note: Repeat this process to add other values to the list.

  7. Click OK.

The values are shown in the Compare Value column.



x
Procedure: How to Display Records Based on a Variable Value

In the Expression Builder dialog box:

  1. Select a field name from the Data section.

    The field is added to the Expression Grid.

  2. Select a relation from the Logical Relations column.
  3. In the Compare Type column, click Parameter (Dynamic).

    This creates a Multiselect OR parameter. If you want to make a single select parameter, double-click the Compare Value column to open the Variable Editor and change the Variable Type to Single Select.



x
Procedure: How to Display Records Based on Field Values

In the Expression Builder dialog box:

  1. Select a field name from the Data section.
  2. Select a relation from the Logical Relations column.
  3. In the Compare Type column, click Field.
  4. Double-click on Compare Value to open the Single Value Builder.
  5. Double-click a field from the Data Source section to move it to the Value List.
  6. Click OK to close the Single Value Builder. The field is added to the Compare Value column.


x
Procedure: How to Display Records Based on Imported Values From an External File

In the Expression Builder dialog box:

  1. Select a field name from the field list.

    The field is added to the Expression Grid.

  2. Select a relation from the Logical Relation column.
  3. Click Import Values from the Compare Type list options.
  4. Double-click the Compare Value column.

    The Multiple Value Builder opens.

    Note: A Single or Multiple Value Builder opens, based on your Logical Relation selection. In the Multiple Value Builder, you may select more than one value. In the Single Value Builder, only one value may be selected.

  5. Click the Select File button to import values from an external file.
  6. Select a text file from your local machine and click Open from the Open dialog box, which is shown in the following image.

    Note: The external file should be a text file with new line delimiters.

    The imported values are loaded into the Data Source area of the Multiple Value Builder.

  7. Double-click an imported value to add it to the Values List. The Multiple Value Builder dialog box is shown in the following image.

  8. Click OK to close the Multiple Value Builder dialog box and return to the Expression Builder.

    The imported values are added to the Compare Value area.



x
Procedure: How to Select Multiple Values and Fields

In the Expression Builder dialog box, you can select multiple values or fields to be used for record selection criteria by selecting options in the And/Or column. The And/Or column is only available if you have more than one Column to Filter.

  1. Select more than one field name from the fields window and drag them to the Expression Grid.

    Note: And is already selected for you for every Column to Filter.

  2. Click the down arrow in the And/Or column. Select one of the following:
    And

    To start a new expression that will be combined with the previous expression by the keyword AND.

    Or

    To start a new expression that will be combined with the previous expression by the keyword OR.

  3. Select remaining criteria from the other columns in the Expression Builder to complete the expression.
  4. Click OK.

Note: When the keyword And is used, WebFOCUS only selects data that meet both conditions. When the keyword Or is used, WebFOCUS selects data that meet either condition.


Top of page

x
Using the Basic Expression Builder

Reference:

The Basic Expression Builder enables you to create expressions quickly by selecting fields, relations, operators, and values from lists. You can base selection criteria on a specified value, a variable value, or a field value.

You can access the Basic Expression Builder by clicking the Assist button within the WHEN dialog, as shown in the following image.

Note: The Basic Expression Builder can also be invoked through a Where based Join.



x
Reference: Basic Expression Builder Dialog Box

The Expression Builder dialog box has the following fields or options:

Field

Displays a list of all of the fields in the data source. Double-click a field to add it to the Column to filter column.

And/Or

Displays the keyword used in the expression listed in the Expression list box. You must select more than one field to activate this option.

Logical Relation

Displays a list of possible relations between the selected data source field and the value, parameter, or other field that WebFOCUS will compare it to. Select a relation to activate the Compare Type column.

Compare Type

Indicates the nature of the comparison you wish to make to the field selected in the field section.

Value

Double-click the Compare Value column to launch the Multiple Value Builder dialog box. Click the Select a field ellipsis button to select a value or multiple values to compare the selected field to a literal value.

Field

Click the down arrow in the Compare Value column to select another field in the data source to compare it to the selected field.

Parameter

Double-click the Compare Value column to launch the Variable Editor. The Variable Editor enables you to create variable fields and define lists of acceptable values.

Function

Double-click the Compare Value column to launch the Function Arguments dialog box. This tool lists functions that you can use to calculate the value of a field in an expression (a function is a program that returns a value). For more information, see the Using Functions manual or click the Help button from the Function Arguments dialog box.

Import Values

Double-click the Compare Value column to launch the Multiple (or Single) Values dialog box. Click the Select File ellipsis button to import values from a local external file. The imported value(s) provide a query limit for the selected data.

Note: The external file should be a text file with new line delimiters.

Other

Double-click the <Please Specify> text in the Compare Value column and overwrite it with a new expression.

Compare Value

Specifies the literal value, parameter, or other field to which the selected field is compared.

The choices available here are dependent on the selection you make in the Compare Type column.

See the procedures in this topic for details.

Note: You can add opening and closing parentheses after you select other criteria for your expression. To add opening parentheses, click the down arrow in the column to the right of the And/Or column. To add closing parentheses, click the down arrow in the column to the right of the Compare Value column.


WebFOCUS