EXPRESSION

Using the Expression instruction you can create calculated dynamic columns using one or two columns from a database. You can create simple expressions or complex expressions. For example, a simple expression concatenates strings by joining several columns. A complex expression uses mathematical operations such as logarithms. For expressions that generate a decimal value, you can specify precision up to six decimal places.

An Expression instruction must have the following syntax:

Syntax

EXPRESSION [Target table] [Target column] [Expression]

Parameters

Target table

Define the target table where the expression column is created.

Target column

Defines the name of the new column. Do not use the following characters:

/ \ º ª - accents, dieresis, ?, !, *, @, #, &, $, o, ñ

Expression

The expression used to create a column value. To create expressions you use operators, statements, and functions.

BIRT Analytics Loader supports the following operators described in Table 5-4.

Table 5-4

Supported operators and statements

Name	Description	Example
+	Concatenate two text columns.	[Demo].[Customer].[Title]+[Demo].[Customer].[Surname]
+	Concatenate Numeric columns or strings.	STRING([CustomerID])+5+ [DaysSinceLastOrder]-1
+, -, *, /	Mathematical operators for adding, subtracting, multiplying, or dividing numeric columns.	STRING([Demo].[Customer].[Cust_ID]+[DEMO].[Customer].[Household_ID])
GT[>], LT [<], GE[>=], LE[<=], EQ [=], NE[<>], AND, OR, NOT	Assign Boolean values to columns, based on the conditions.	[Demo].[Customer].[Edats]EQ"Adults">0 or [Demo].[Customer].[Edats]EQ"Joves">0
Constants	Assign a constant to a table column.	[Demo].[Customer].[Household_ID]=1
Switch statement	Assign a value to a column depending on a condition.	SWITCH([Demo].[Customer].[Gender])CASE"M":"MALE",CASE"F":"FEMALE"SWITCHEND
Boolean If statement	Return a value depending on a condition. The condition can be simple, or more complex, and the return value can be any type, for example yes or no.	If([DEMO].[Order].[OrderDate]>[Demo].[Customer].[DOB],1,0)

Table 5-5 presents the complete list of the functions. For more information about these functions, see Using BIRT Analytics.

Table 5-5

Supported functions

Name and syntax	Description
ABS ([db].[table].[column])	Returns the absolute value of a numeric column, regardless of sign.
AGE ([db].[table].[column], date)	Returns the age of each value on a specified date.
ALLMONTHS ([db].[table].[column])	Returns the year and the month of each one of the values of a date column.
ALLQUARTERS ([db].[table].[column])	Returns the year and the quarter of each one of the values of a date column.
CEIL ([db].[table].[column])	Given a continuous numeric column, it returns the next integer value. For example, given the value 3.6, the CEIL function returns 4.
CHR ([db].[table].[column])	Given an integer numeric column, it returns the associated ASCII (American Standard Code for Information Interchange) value.
CODE ([db].[table].[column])	Returns the ASCII code for the first value of the text string. It only works for ASCII string columns, not for Unicode columns.
COUNT ([db].[table].[column])	Returns the number of records in a table.
DATE ([db].[table].[column])	Enters a column containing date data as text strings, date-and-time format or the data for a constant date.
DATETIME ([db].[table].[column])	Enters a column containing date data as text strings, or unicode, or a constant date.
DAY ([db].[table].[column])	Returns the part for the day in a date column.
DAYADD ([db].[table].[column], [db].[table].[column2])	Adds or removes the number of days required in a date column. The column returned is a date column.
DAYSTO ([db].[table].[column], date)	Returns the number of days between each value and a specified date.
DISTINCT ([db].[table].[column])	Returns the unique value or values in a column. For example, in a [Demo].[Customer].[Surname] column, it returns each surname stored in the column only once.
EXP ([db].[table].[column])	Returns the exponential value of each of the values of a numeric column.
FLOOR ([db].[table].[column])	Returns the largest integer number less than or equal to the array.
HOUR ([db].[table].[column])	Returns the time of each date.
IF (A, B, C)	Returns a conditional function. A is the condition to be applied, B is the value to return if the condition is true, C is the value to return if the condition is false.
INSTR ([db].[table].[column], “string”)	Returns an integer that specifies the initial position of the first appearance of a string in another one. First value is 0.
INT ([db].[table].[column])	Returns the integer number that forms part of the value in a column, or converts the column that contains the numbers into a numeric column.
ISNULL ([db].[table].[column])	Returns true for null values and false for not-null values.
ISNOTNULL ([db].[table].[column])	Returns true for not-null values and false for null values.
KURT ([db].[table].[column])	Returns the kurtosis of a column, which represents how values are distributed around the mean.
LCASE ([db].[table].[column])	Returns a lowercase text string, turning uppercase characters into lowercase.
LEFT ([db].[table].[column], n)	Returns the left part of a string of characters, where n is the number of characters to return.
LEN ([db].[table].[column])	Returns the number of characters in a text column, returning a number for each value in the column.
LOG ([db].[table].[column])	Returns the natural logarithm (logarithm in base e) of the values of the column.
LOG10 ([db].[table].[column])	Returns the decimal logarithm (logarithm in base 10) of the values of the column.
LONGINT ([db].[table].[column])	Returns the long integer part of a column, or converts the column that contains a number into a numeric column.
LTRIM ([db].[table].[column])	Returns a string that is a copy of a string with no initial spaces (deletes any initial spaces).
MAX ([db].[table].[column])	Returns the maximum value of a column. The column used must always be numeric (continuous or discrete).
MEAN ([db].[table].[column])	Returns the average value of a numeric column (continuous or discrete).
MEDIAN ([db].[table].[column])	Returns the value that divides the values of a column into two equal parts.
MID ([db].[table].[column], number1, number2)	Returns a string that contains a specified number of characters. Number1 indicates the position where the string to be returned starts and Number2 indicates the number of characters to return (optional). If Number1 is greater than the number of characters in the initial string, it returns a 0-length string.
MIN ([db].[table].[column])	Returns the smallest value of the parameters of a numeric column (continuous or discrete).
MINUTE ([db].[table].[column])	Returns the minutes part of a date and/or time column.
MODE ([db].[table].[column])	Returns the value that is most repeated in the values of a column (the most frequent value).
MONTH ([db].[table].[column])	Returns the month part of a date column.
NOW ( )	Returns the current date and time in the engine’s default format.
POWER ([db].[table].[column], number)	Returns all the values of the column to the specified second parameter (number).
PROPER ([db].[table].[column])	Returns a new column in which all records begin with an uppercase letter and the rest are in lowercase.
PROPERSENTENCE ([db].[table].[column])	Returns a new column in which the first letter of each word in the description of the variables is in uppercase.
QUARTER ([db].[table].[column])	Returns the quarter of the year for each of the records in the date column.
RAND (n)	Returns a column based on random (n). For example, RAND (120) in the Customers table returns a column in which customers are randomly divided into 120 different groups.
REAL ([db].[table].[column])	Returns the real value of a column. The source column must be a numeric column, a text string containing text, or a string representing a numeric column.
REPLACE ([db].[table].[column], pattern, replacement)	Replaces the pattern text with the replacement text. Both pattern and replacement can be columns or text
RIGHT ([db].[table].[column],n)	Returns the right part of a string of characters where n is the number of characters to return.
ROW ([db].[table].[column])	Returns a unique value, starting from 0, for each of the existing records in the column used to create the expression.
RTRIM ([db].[table].[column])	Returns a text string, an exact copy of the specified string without trailing spaces.
SECOND ([db].[table].[column])	Returns the seconds part of a date or time column.
SECSTO ([db].[table].[column], [db].[table].[column])	Returns the seconds elapsed between two columns with date or time format.
SGN ([db].[table].[column])	Returns the values grouped by -1 for negative values, 0 for invalid values, because not entered, for example, and +1 for positive values.
SKEW ([db].[table].[column])	Returns the skewness value of the values of a column with respect to the mean value.
SQRT ([db].[table].[column])	Returns the square root of n, where n is the mean of the values of the column.
STDEV ([db].[table].[column])	Returns the standard deviation of the values of the column, which is the square root of the variance.
STRING ([db].[table].[column])	Returns a text string that represents any other type of data, except Unicode.
STRIP ([db].[table].[column])	Returns the entered column deleting any spaces.
SUM ([db].[table].[column])	Returns a new column with the cumulative sum of the values of the original column.
SUMSQ ([db].[table].[column])	Returns a new column with the cumulative sum of each of the values of the original column squared.
TIME ([db].[table].[column])	Returns the time of a string, date-and-time, or time column.
TODAY ()	Returns current date in engine’s format.
TRIM ([db].[table].[column])	Returns a text string that contains the copy of the specified string with no spaces either at the beginning or the end of the string.
UCASE ([db].[table].[column])	Returns a new column with text in uppercase.
VAL ([db].[table].[column])	Returns a Unicode string that represents any other type of data.
WEEKDAY ([db].[table].[column])	Depending on the locale, returns the day of the week for each of the values. Default locale is en_US. F1=Sunday. In es_ES locale: 1=Monday.
YEAR ([db].[table].[column])	Returns the year of a date or date-and-time column.

How to create an expression

This example creates a column that displays the total dollar amount of car inventory.

1	In Main file, choose New. The list of transformation instructions appears.

2	In the list of transformation instructions, select Expression. Expression appears, as shown in Figure 5-17.

Figure 5-17

Creating an expression

3	In Table, select a target database and a table.

4	In Column, type the name of the new column.

5	In Expression, type the expression.

6	In Description, type the description of the column.

7	In Expression, choose OK. The Expression instruction appears in Main file, as shown in Figure 5-18.

Figure 5-18

Creating an Expression instruction

8	Choose Save to save the project.

9	Validate that the new column was created in Explorer in Data Management.

Copyright Actuate Corporation 2013

BIRT Analytics 4.2