Common Operators and Functions in Cognos 12

(

Identifies the beginning of an expression.

)

Identifies the end of an expression.

*

Multiplies two numeric values.

/

Divides two numeric values.

||

Concatenates, or joins, strings.

+

Adds two numeric values.

-

Subtracts two numeric values or negates a numeric value.

< 

Compares the values that are represented by "value1" against "value2" and retrieves the values that are less than "value2".

<=

Compares the values that are represented by "value1" against "value2" and retrieves the values that are less than or equal to "value2".

<> 

Compares the values that are represented by "value1" against "value2" and retrieves the values that are not equal to "value2".

=

Compares the values that are represented by "value1" against "value2" and retrieves the values that are equal to "value2".

> 

Compares the values that are represented by "value1" against "value2" and retrieves the values that are greater than "value2".

>=

Compares the values that are represented by "value1" against "value2" and retrieves the values that are greater than or equal to "value2".

and

Returns "true" if the conditions on both sides of the expression are true.

between

Determines if a value falls in a given range.

case

Works with when, then, else, and end. Case identifies the beginning of a specific situation, in which when, then, and else actions are defined.

contains

Determines if "string1" contains "string2".

distinct

A keyword used in an aggregate expression to include only distinct occurrences of values. See also the function unique.

else

Works with if or case constructs. If the if condition or the case expression are not true, then the else expression is used.

end

Indicates the end of a case or when construct.

ends with

Determines if "string1" ends with "string2".

if

Works with then and else constructs. If defines a condition; when the if condition is true, the then expression is used. When the if condition is not true, the else expression is used.

in

Determines if "expression1" exists in a given list of expressions.

is missing

Determines if "value" is undefined in the data.

like

Determines if "string1" matches the pattern of "string2", with the character "char" optionally used to escape characters in the pattern string.

lookup

Finds and replaces data with a value you specify. It is preferable to use the case construct.

not

Returns TRUE if "argument" is false or returns FALSE if "argument" is true.

or

Returns TRUE if either of "argument1" or "argument2" are true.

starts with

Determines if "string1" starts with "string2".

then

Works with if or case constructs. When the if condition or the when expression are true, the then expression is used.

when

Works with the case construct. You can define conditions to occur when the WHEN expression is true.

average

Returns the averagevalue of selected data items. Distinct is an alternative expression that is compatible with earlier versions of the product.

count

Returns the number of selected data items excluding null values. Distinct is an alternative expression that is compatible with earlier versions of the product. All is supported in DQM mode only and it avoids the presumption of double counting a data item of a dimension table.

maximum

Returns the maximum value of selected data items. Distinct is an alternative expression that is compatible with earlier versions of the product.

median

Returns the median value of selected data items.

minimum

Returns the minimum value of selected data items. Distinct is an alternative expression that is compatible with earlier versions of the product.

percentage

Returns the percent of the total value for selected data items. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can be used only in the context of relational datasources.

rank

Returns the rank value of selected data items. The sort order is optional; descending order (DESC) is assumed by default. If two or more rows tie, then there is a gap in the sequence of ranked values (also known as Olympic ranking). The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can be used only in the context of relational datasources. Distinct is an alternative expression that is compatible with earlier versions of the product. Null values are ranked last.

standard-deviation

Returns the standard deviation of selected data items.

total

Returns the total value of selected data items. Distinct is an alternative expression that is compatible with earlier versions of the product.

variance

Returns the variance of selected data items.

_round

Returns "numeric_expression" rounded to "integer_expression" decimal places. Notes: "integer_expression" must be a non-negative integer. Rounding takes place before data formatting is applied.

abs

Returns the absolute value of "numeric_expression". Negative values are returned as positive values.

cast

Converts "expression" to a specified data type. Some data types allow for a length and precision to be specified. Make sure that the target is of the appropriate type and size. The following can be used for "datatype_specification": character, varchar, char, numeric, decimal, integer, bigint, smallint, real, float, date, time, timestamp, time with time zone, timestamp with time zone, and interval. When type casting to an interval type, one of the following interval qualifiers must be specified: year, month, or year to month for the year-to-month interval datatype; day, hour, minute, second, day to hour, day to minute, day to second, hour to minute, hour to second, or minute to second for the day-to-second interval datatype. Notes: When you convert a value of type timestamp to type date, the time portion of the timestamp value is ignored. When you convert a value of type timestamp to type 46 IBM Cognos Analytics Version 11.0: Data Modeling Guide time, the date portion of the timestamp is ignored. When you convert a value of type date to type timestamp, the time components of the timestamp are set to zero. When you convert a value of type time to type timestamp, the date component is set to the current system date. It is invalid to convert one interval datatype to the other (for instance because the number of days in a month is variable). Note that you can specify the number of digits for the leading qualifier only, i.e. YEAR(4) TO MONTH, DAY(5). Errors will be reported if the target type and size are not compatible with the source type and size.

char_length

Returns the number of logical characters in "string_expression". The number of logical characters can be distinct from the number of bytes in some East Asian locales.

coalesce

Returns the first non-null argument (or null if all arguments are null). Requires two or more arguments in "expression_list".

lower

Returns "string_expression" with all uppercase characters shifted to lowercase.

mod

Returns the remainder (modulus) of "integer_expression1" divided by "integer_expression2". "Integer_expression2" must not be zero or an exception condition is raised.

nullif

Returns null if "expression1" equals "expression2", otherwise returns "expression1".

position

Returns the integer value representing the starting position of "string_expression1" in "string_expression2" or 0 when the "string_expression1" is not found.

power

Returns "numeric_expression1" raised to the power "numeric_expression2". If "numeric_expression1" is negative, then "numeric_expression2" must result in an integer value.

sqrt

Returns the square root of "numeric_expression". "Numeric_expression" must be non-negative.

substring

Returns the substring of "string_expression" that starts at position "integer_expression1" for "integer_expression2" characters or to the end of "string_expression" if "integer_expression2" is omitted. The first character in "string_expression" is at position 1.

trim

Returns "string_expression" trimmed of leading and trailing blanks or trimmed of a certain character specified in "match_character_expression". "Both" is implicit when the first argument is not stated and blank is implicit when the second argument is not stated.

upper

Returns "string_expression" with all lowercase characters converted to uppercase.

_add_days

Returns the date or datetime, depending on the format of "date_expression", that results from adding "integer_expression" days to "date_expression".

_add_months

Adds "integer_expression" months to "date_expression". If the resulting month has fewer days than the day of month component, then the last day of the resulting month is returned. In all other cases the returned value has the same day of month component as "date_expression".

_add_years

Adds "integer_expression" years to "date_expression". If the "date_expression" is February 29 and resulting year is non leap year, then the resulting day is set to February 28. In all other cases the returned value has the same day and month as "date_expression".

_age

Returns a number that is obtained from subtracting "date_expression" from today's date. The returned value has the form YYYYMMDD, where YYYY represents the number of years, MM represents the number of months, and DD represents the number of days.

_day_of_week

Returns the day of week (1 to 7), where 1 is the first day of the week as indicated by the second parameter (1 to 7, 1 being Monday and 7 being Sunday). Note that in ISO 8601 standard, a week begins with Monday being day 1.

_day_of_year

Returns the day of year (1 to 366) in "date_ expression". Also known as Julian day.

_days_between

Returns a positive or negative number representing the number of days between "date_expression1" and "date_expression2". If "date_expression1" < "date_expression2", then the result will be a negative number.

_days_to_end_of_month

Returns a number representing the number of days remaining in the month represented by "date_expression".

_end_of_day

Returns the end of today as a timestamp.

_first_of_month

Returns a date or datetime, depending on the argument, by converting "date_expression" to a date with the same year and month but with the day set to 1.

_last_of_month

Returns a date or datetime, depending on the argument, that is the last day of the month represented by "date_expression".

_month

Returns the value of the month field in a date expression.

_year

Returns the value of the year field in a date expression.

current_date

Returns a date value representing the current date of the computer that the database software runs on.