Reference: |
A custom field is a field whose value is not stored in the data source but can be calculated from the data that is there. You can create a custom field in your synonym by adding a virtual column (DEFINE), Master File filter (FILTER), and a Computed Field (COMPUTE). The fields are available whenever you access the corresponding data source in a reporting tool.
You can define attribute values and create expressions for custom fields by using the Synonym Editor.
The following attributes may be available for custom fields (DEFINE, FILTER, and COMPUTE) in the Synonym Editor.
Note: The attributes available depend on the type of synonym and the type of custom field selected. The following image is an example of an SQL data source with a virtual column (DEFINE) selected.
Custom fields (DEFINE, FILTER, COMPUTE) typically have the following attributes:
General
Is the name of the virtual column.
Note: This attribute only appears when a virtual column (DEFINE) is selected.
Is the name of the Master File Filter field.
Note: This attribute only appears when a virtual filter field is selected.
Is the name of the computed field.
Note: This attribute only appears when a virtual computed field is selected.
Is the expression that creates the virtual column.
Supplies a title to replace the column name that is normally used in reports and enables you to specify multiple language titles for the virtual column.
Describes the data type and format for the virtual column.
Note: This attribute only appears for DEFINE and COMPUTE custom fields.
Allows missing data. If not, the transaction value is supplied.
Note: This attribute only appears for DEFINE and COMPUTE custom fields.
Allows all missing data. If not, the transaction value is supplied.
Note: This attribute only appears for DEFINE and COMPUTE custom fields.
Miscellaneous
A DEFINE expression may not contain qualified field names. REDEFINES enables you to redefine or recompute a column whose name exists in more than one segment.
Note: This attribute only appears for DEFINE custom fields and cannot be used by WebFOCUS.
If no columns from the synonym are used in the expression or have been defined, you can use the WITH option to identify the logical home of the defined calculation. You can also use the WITH option to move the logical home for the virtual column to a lower segment than it would otherwise be assigned (for example, to count instances in a lower segment).
Note: This attribute only appears for DEFINE and FILTER custom fields.
Contains a description or comments about the virtual column.
Contains the name of a field to be included in a dimension.
These WITHIN statements are added to the synonym through the Dimension Builder to OLAP-enable FOCUS files and relational tables. This enables you to perform OLAP analysis using the OLAP Control Panel or to use it with the Financial Report Painter.
Note: This attribute only appears for DEFINE and FILTER custom fields.
Is the name of the stylesheet applied to a field.
Note: The attributes available depend on the type of synonym.
To launch the Define Calculator, Filter Calculator, or Compute Calculator, click the browse (...) button at the right of the EXPRESSION value field in the Properties section of the Synonym Editor.
The selected calculator opens, depending on the type of custom field that you are creating.
The calculator has the following fields and options:
Is the name of the object being created (virtual field (DEFINE), filter, computed field).
Is the field format.
Location for typing an expression. You can add data source fields from the Columns/Variables tab, functions from the Functions tab, and numbers and operators from the calculator as you type.
Displays the expression building window from which you can add and delete columns, choose the relation and type, and select values for your filter.
Displays a hierarchical list of available source columns and System Variable folders that you can use in creating an expression.
A function is a program that returns a value. This tab lists the built-in functions that you can use to derive the value of a temporary field.
Enables you to specify parameters for the function through a dialog box when creating or editing a transformation.
Enables you to insert numbers and operators.
The following operators are available:
Concatenates two values, retaining any trailing blanks after the first one. For example, if FIRST_NAME and LAST_NAME were both in A15 format, the expression
FULL_NAME = FIRST_NAME | LAST_NAME
would produce a column like the following:
MICHAEL SMITHSONJ ANE JONES . . .
Concatenates two values, suppressing any trailing blanks in the first. For example, to construct the full name and insert a comma (,), the syntax
FULL_NAME = LAST_NAME || (', ' | FIRST_NAME)
would produce a column like the following:
SMITHSON, MICHAEL JONES, JANE . . .
The concatenation in the parentheses is done first (preserving the blank space after the comma), and the result is then concatenated to LAST_NAME, suppressing the trailing blanks of LAST_NAME.
Establishes a conditional test.
Specifies the action to perform if the result of a conditional test is TRUE.
Specifies the action to perform if the result of a conditional test is FALSE.
Returns the value TRUE if the value on the left is less than the value on the right.
Returns the value TRUE if the operand is false.
Returns the value TRUE if the value on the left is less than or equal to the value on the right.
Returns the value TRUE if the value on the left is equal to the value on the right.
Returns the value TRUE if both operands are true.
Returns the value TRUE if the value on the left is greater than the value on the right.
Returns the value TRUE if the value on the left is greater than or equal to the value on the right.
Returns the value TRUE if the value on the left is not equal to the value on the right.
Returns the value TRUE if either operand is true.
Raises a value to the specified power.
Adds parentheses.
Inserts two single quotation marks. Enter alphanumeric test values between the quotation marks.
Converts selected text to uppercase.
Converts selected text to lowercase.
Verifies the validity of the expression.
Produces sample data for the expression.
WebFOCUS |