Defining Attributes and Creating Expressions for Custom Fields
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.
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.
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.
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.
The selected calculator opens, depending
on the type of custom field that you are creating.
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.