Product Documentation

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.

Syntax for Report Expressions

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.

Limitation

Expressions are not supported for use on repeating row fields within your data dictionary.

Variable References to Enumerated Types

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.

Evaluation of Expressions

As much as possible, report expressions are evaluated by ExtraView by processing at two levels:

• Select list construction within the detail report query; and
• Retrieval of the detailed expression values for rendering in the report row

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.

Null Values

The existence of a null value in an expression renders the result of the entire expression value to be null.

Expression Error Handling

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.

Report Field Titles

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.

Use of Session Variables

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.

Date Calculations

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.

Examples

1. Compute the elapsed time, in number of days, from when an issue was created, to when it was last updated, and place the result in a field named DAYS_ELAPSED:
    
   DAYS_ELAPSED = date_diff($$TIMESTAMP$$, $$DATE_CREATED$$)
    
2. Compute a total sale value with sales tax based on a rate of 8%:

   $$currency_field$$ * 1.08
    

3. Count the number of selections of a value in a field named my_field with a value of Yes, when another field named other_field has a specific value of val:

   case when ($$my_field$$ = 'Yes' and $$other_field$$ = 'val') then 1 else null end
    

4. Add seven days to the current date:

   Oracle and SQL Server - $$SYSDATE$$ + 7

   MySQL - DATE_ADD($$SYSDATE$$, INTERVAL 7 DAY)

   Derby - {fn TIMESTAMPADD(SQL_TSI_DAY,7,$$SYSDATE$$)}