Report expressions are used to calculate or derive new field values which are only placed on report output. For example, you may want to multiply the values held in two columns of a report to derive a new calculated value. Another example may be to subtract one date from another to calculate the number of days between two events. Report expressions are first defined by creating a field in the data dictionary that is placed on the report. The actual calculation or expression is defined in the default value of the field, or in an attribute that is stored with the field within a report. Using the default value allows you to define a calculated field that may be shared across many reports without further configuration. However, you can use a single Expression type field defined in the data dictionary and reuse this on many reports for many purposes. You achieve this by selecting the field for use on any report, then using its Alternative Title and Expression attributes to modify its purpose for that report.
The general syntax of an expression matches a subset of that in ANSI-99 SQL expressions, with arithmetic, string, and date data types. This includes infix and prefix operators, with standard precedence rules. Also, parentheses may be used to group sub-expressions.
Literals of each type are supported and although full ANSI syntax is not promised, it should be sufficient to allow specification of a specific value for each data type. Variable names may refer to fields in the database. Each variable is in the form $$DD_NAME$$ and may refer to a standard ExtraView field name or special variable. Usually, the specification of a variable name refers to its report value (viz., its title or rendered form). However, inside an expression, the value of the variable is used, not its rendered form. Also, see the note on enumerated types below.
Enumerated types (fields with a display type of List, Popup, Checkbox, and Tab) may be referenced only as string variables, in which case their title is used in the expression. Only single-valued list type fields may be referenced.
As much as possible, report expressions are evaluated by ExtraView by processing at two levels:
Syntax checking includes tests for single-quoted strings, double-quoted strings, parentheses and the “cast” function. Syntax checking is done before the attribute is entered and stored as part of the report.
The existence of a null value in an expression renders the result of the entire expression value to be null.
Each report expression is syntax-checked and variable references are validated before allowing the containing report to be updated or run. There may be opportunity for the user to create erroneous expressions, as not all expressions can be determined without reference to the values in the database.
To support report-specific titles on the expression fields, the ALTERNATE FIELD TITLE attribute is supported. This compensates for the difficulty for users to create column headings when they are sharing a data dictionary entry, such as the EXPRESSION type entries. The ALTERNATE FIELD TITLE attribute appears within the GUI of the report builder to allow the user to define a title for a report through a simple selection mechanism.
Session variables defined in the data dictionary may be used as substitutable variables in a report expression. Each referenced session variable value must be set using a runtime filter for the report. Session variables may also be used within runtime filters to provide a mechanism to provide the same value for the occasions when the same field is used multiple times within filter queries. This can happen with advanced queries that use multiple and and or conjunctions. Date fields within the ExtraView database are stored as timestamp type fields, which is done for internal efficiency and to facilitate a wide number of internal computations. This does not make them very simple to work with in expressions, but the following examples should help significantly. The session variable must have been defined with a compatible display type, such as DATE, DAY, or TEXTFIELD for use within the expression.
A frequently use of expressions is to calculate the difference between two date fields. The internal database representation of date fields use the TIMESTAMP data type. This data type does not lend itself to simple addition and subtraction to calculate differences between dated. The date_diff function is an ExtraView function that simplifies date calculations. It is used in this way:
This returns the difference as a whole number, between the two date fields.
$$currency_field$$ * 1.08
case when ($$my_field$$ = 'Yes' and $$other_field$$ = 'val') then 1 else null end
Oracle and SQL Server - $$SYSDATE$$ + 7
MySQL - DATE_ADD($$SYSDATE$$, INTERVAL 7 DAY)
Derby - {fn TIMESTAMPADD(SQL_TSI_DAY,7,$$SYSDATE$$)}
SALUTATION_TITLE, FIRST_NAME, LAST_NAME as the first line of the output. The chr(10) provides a linefeed so that the field ADDRESS appears on the second line. The next line contains ADDRESS_2, then followed in the next line by ADDRESS_3. The next line contains a concatenation of the CITY, STATE and POSTAL_CODE. The final line contains the PHONE field. The decode statements are standard SQL that allow the suppression of the field, if there is no value returned from the query.
decode($$SALUTATION.TITLE$$,'','',$$SALUTATION.TITLE$$ || ' ') ||
decode($$FIRST_NAME$$,'','',$$FIRST_NAME$$) || chr(10) ||
decode($$LAST_NAME$$,'','',$$LAST_NAME$$) || chr(10) ||
decode($$ADDRESS$$,'','',$$ADDRESS$$ || chr(10)) ||
decode($$ADDRESS_2$$,'','',$$ADDRESS_2$$ || chr(10)) ||
decode($$ADDRESS_3$$,'','',$$ADDRESS_3$$ || chr(10)) ||
decode($$CITY$$,'','',$$CITY$$ || ', ') ||
decode($$STATE$$,'','',$$STATE$$ ||' ') ||
decode($$POSTAL_CODE$$,'','',$$POSTAL_CODE$$) || chr(10) ||
decode($$PHONE$$,'','',$$PHONE$$)
The end result is that you have composed a single field that contains the address in the expected format, although the components were each stored as individual fields.