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

Custom Fields

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 expression 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 Financial Report Painter.

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

USE_STYLE

Is the name of the stylesheet applied to a field.

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


Top of page

x
Reference: Calculators for Custom Fields

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.

Expression value

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

Define Calculator

The calculator has the following fields and options:

Name

Is the name of the object being created (virtual field (DEFINE), 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.

Fields/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

Enables you to 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      SMITHSONJ
ANE         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