Defining Attributes and Creating Expressions for Custom Fields

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.


Top of page

x
Reference: Custom Field Attributes

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 the virtual column (DEFINE) selected.

SQL data source

Custom fields (DEFINE, FILTER, COMPUTE) typically have the following attributes:

General
DEFINE
Is the name of the virtual column.

Note: This attribute only appears when a virtual column (DEFINE) is selected.

FILTER
Is the name of the Master File filter field.

Note: This attribute only appears when a virtual Filter field is selected.

COMPUTE
Is the name of the computed field.

Note: This attribute only appears when a virtual computed field is selected.

EXPRESSION
Is the calculation that creates the virtual column.
TITLE
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.
FORMAT
Describes the data type and format for the virtual column.

Note: This attribute only appears for DEFINE and COMPUTE custom fields.

Allow Missing Data
Allows missing data. If not, the transaction value is supplied.

Note: This attribute only appears for DEFINE and COMPUTE custom fields.

All
Allows all missing data. If not, the transaction value is supplied.

Note: This attribute only appears for DEFINE and COMPUTE custom fields.

Miscellaneous
REDEFINES
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.

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

DESCRIPTION
Contains a description or comments about the virtual column.
WITHIN
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 FML Painter.

Note: This attribute only appears for DEFINE and FILTER custom fields.

Note: The attributes available depend on the type of synonym.


Top of page

x
Reference: Calculators for Custom Fields

To launch the Virtual Column Calculator, Filter Calculator, or Compute Calculator, click the browse (...) button at the right of the EXPRESSION value field in the Synonym Editor.

Expression value

The selected calculator opens, depending on the type of custom field that you are creating.

The selected  Calculator

The calculator has the following fields/options:

Column
Is the name of the object being created (virtual field, filter, computed field).
Format
Is the field format.
Expression tab
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.
Relational Expression tab
Displays the expression building window from which you can add and delete columns, choose the relation and type, and select values for your filter.
Columns/Variables tab
Displays a hierarchical list of available source columns and System Variable folders that you can use in creating an expression.
Functions tab
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.
Function Assist button
Enables you to specify parameters for the function through a dialog box when creating or editing a transformation.
Calculator buttons
Insert numbers and operators.

The following operators are available:

| (single concatenation bar)
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      SMITHSONJANE         JONES
.
.
.
|| (double concatenation bar)
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.

IF
Establishes a conditional test.
THEN
Specifies the action to perform if the result of a conditional test is TRUE.
ELSE
Specifies the action to perform if the result of a conditional test is FALSE.
LT
Returns the value TRUE if the value on the left is less than the value on the right.
NOT
Returns the value TRUE if the operand is false.
LE
Returns the value TRUE if the value on the left is less than or equal to the value on the right.
EQ
Returns the value TRUE if the value on the left is equal to the value on the right.
AND
Returns the value TRUE if both operands are true.
GT
Returns the value TRUE if the value on the left is greater than the value on the right.
GE
Returns the value TRUE if the value on the left is greater than or equal to the value on the right.
NE
Returns the value TRUE if the value on the left is not equal to the value on the right.
OR
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.
a->A
Converts selected text to uppercase.
A->a
Converts selected text to lowercase.
Check expression button
Verifies the validity of the expression.
Sample data button
Produces sample data for the expression.

WebFOCUS