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.
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.
In the Define tool window:
The Function Arguments dialog box opens.
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.
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.
or
or
The Report Options dialog box opens at the Computes tab.
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.
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.
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.
The Report Options dialog box opens at the Computes tab.
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.
The DECODE options appear. You may supply values in the function or read values from a file.
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.
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.
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.
The function and placeholders for its arguments are added to the expression in the Computes tab.
The Compute field is added to Report Painter.
The report, in the following image, shows the decoded department code values assigned to the current jobcodes in the data source.
The Report Options Computes Tab is shown in the following image.
The Computes tab includes the following fields and options for creating expressions.
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.
Displays the field type, field length, and display options. The field type can be alphanumeric, numeric, or date/time.
Opens the Format dialog box, where you can assign format information to the calculated value.
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.
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.
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.
Deletes the current expression and clears the tab.
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.
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.
Opens the Options window, which enables you to establish how to interpret and represent missing values for the virtual field.
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.
The following image shows the Define tool.
The Define tool has the following fields or options:
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.
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.
When selected, it clears any current temporary field definitions you previously added to the list of temporary fields.
Opens the Format dialog box, where you assign a format to the temporary field.
Displays the field type, field length, and display options. The field type can be alphanumeric, numeric, or date/time.
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.
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.
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.
Displays the Define phrase in code, and specifies any errors.
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.
Deletes the temporary field identified in the Field text box. The field is no longer available.
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.
Lists the names of the temporary fields already associated in the Master File.
Lists the fields defined in the Master File.
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.
The Format dialog box opens.
* .05
The expression for the Define field now appears as CURR_SAL * .05.
You can then select the new field, INCREASE, in the Fields window of the reporting tools.
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.
The Expression Builder dialog box, which is shown in the following image, has the following sections/options:
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.
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.
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.
Allows you to add either one, two, or three parentheses before and after an expression.
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.
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:
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.
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:
Note: The external file should be a text file with new line delimiters.
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.
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.
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.
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.
In this section you can type an expression out rather than using the Expression Grid.
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.
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.
Deletes an expression.
Moves an expression up one.
Moves an expression down one.
In the Expression Builder dialog box:
The field is added to the Expression Grid.
Note: Repeat this process to add other values to the list.
The values are shown in the Compare Value column.
In the Expression Builder dialog box:
The field is added to the Expression Grid.
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.
In the Expression Builder dialog box:
In the Expression Builder dialog box:
The field is added to the Expression Grid.
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.
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.
The imported values are added to the Compare Value area.
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.
Note: And is already selected for you for every Column to Filter.
To start a new expression that will be combined with the previous expression by the keyword AND.
To start a new expression that will be combined with the previous expression by the keyword OR.
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.
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.
The Expression Builder dialog box has the following fields or options:
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.
Displays the keyword used in the expression listed in the Expression list box. You must select more than one field to activate this option.
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.
Indicates the nature of the comparison you wish to make to the field selected in the field section.
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.
Click the down arrow in the Compare Value column to select another field in the data source to compare it to the selected field.
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.
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.
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.
Double-click the <Please Specify> text in the Compare Value column and overwrite it with a new expression.
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 |