Cognos Report Studio Calculation Components

Returns the first members found in the set up to "numeric_expression_maximum" + "numeric_ expression_overflow". If "numeric_expression_maximum" + "numeric_expression_overflow" is exceeded, then only the maximum number of members are returned. For a set that has only a few members more than the specified numeric_expression_maximum, the numeric_expression_overflow allows the small set of extra members to be included. If the set has more members than the overflow allows, then only the numeric_expression_maximum members will be returned.

Returns the set containing "member_expression" when the size of "set_expression" is greater than "numeric_expression"; i.e., a new member will be generated if the number of members in "set_expression" is larger than the specified "numeric_expression".

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

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

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

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.

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.

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

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.

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

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.

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

Returns a timestamp constructed from "integer_expression1" (the year), "integer_expression2" (the month), and "integer_expression3" (the day). The time portion defaults to 00:00:00.000 .

Returns a positive or negative integer number representing the number of months between "date_expression1" and "date_expression2". If "date_expression1" is earlier than "date_expression2", then a negative number is returned.

Returns the number of the week of the year of "date_expression" according to the ISO 8601 standard. Week 1 of the year is the first week of the year to contain a Thursday, which is equivalent to the first week containing January 4th. A week starts on Monday (day 1) and ends on Sunday (day 7).

Returns a positive or negative integer number representing the number of years between "date_expression1" and "date_expression2". If "date_expression1" < "date_expression2" then a negative value is returned.

Returns a number representing the difference between "date_expression1" and "date_expression2". 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.

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

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, 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 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.

Returns the smallest integer that is greater than or equal to "numeric_expression".

Returns the smallest integer that is greater than or equal to "numeric_expression".

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.

Returns the number of characters in "string_expression".

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

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

Returns a time with time zone value, representing the current time of the computer that runs the database software if the database supports this function. Otherwise, it represents the current time of the computer that runs IBM® Cognos® BI software.

Returns a datetime with time zone value, representing the current time of the computer that runs the database software if the database supports this function. Otherwise, it represents the current time of the computer that runs IBM® Cognos® BI software.

Returns 'e' raised to the power of "numeric_expression". The constant 'e' is the base of the natural logarithm.

Returns an integer representing the value of datepart (year, month, day, hour, minute, second) in "datetime_expression".

Returns the largest integer that is less than or equal to "numeric_expression".

Returns the natural logarithm of "numeric_expression".

Returns a time value, representing the current time of the computer that runs the database software.

Returns a datetime value, representing the current timestamp of the computer that runs the database software.

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

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

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

Returns the number of bytes in "string_expression".

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

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

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

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

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.

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.

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

Inserts the current system date.

Inserts the current system date and time.

Inserts a zero time with time zone.

Inserts an example of a timestamp with time zone.

Inserts a zero interval: 000 00:00:00.000.

Inserts a zero year interval: 0 year.

Inserts a zero month interval: 0 month.

Inserts a zero year to month interval: 0000-00 year to month.

Inserts a zero day interval: 0 day.

Inserts a zero hour interval: 0 hour.

Inserts a zero minute interval: 0 minute.

Inserts a zero second interval: 0 second.

Inserts a zero day to hour interval: 0 00 day to hour.

Inserts a zero day to minute interval: 0 00:00 day to minute.

Inserts a zero day to second interval: 0 00:00:00.000000000 day to second.

Inserts a zero hour to minute interval: 00:00 hour to minute.

Inserts a zero hour to second interval: 00:00:00.000000000 hour to second.

Inserts a zero minute to second interval: 00:00.000000000 minute to second.

Inserts "null" if the expression conditions are not met.

Inserts the number 0, which can be replaced with a new numeric value.

Inserts an empty string as two single quotation marks between which you can type a string.

Inserts the current system time.

This construct is the template for an if...then...else statement. This construct appears in the Top 10 Retailers for 2005 sample report in the GO Data Warehouse (analysis) package.

This is the template for an in_range expression.

This construct is the template for a search case, including the case, when, else, and end functions.

This construct is the template for a simple case, including the case, when, else, and end functions.

Returns a date as a string in YYYY-MM-DD format.

Converts "date_expression" to a timestamp. The time part of the timestamp will equal zero.

Converts "date_expression" to a timestamp with a time zone. The time and time zone parts of the timestamp will equal zero.

Returns a date time interval as a string in DDDD HH:MM:SS.FFFFFFF or -DDDD HH:MM:SS.FFF format.

Returns a date time interval as a string in HHHH:MM:SS.FFFFFFF or HH:MM:SS.FFF format. Days are converted to hours.

Converts an integer to a date time interval. "String_expression" specifies what "integer_expression" represents: "ns" = nanoseconds, "s" = seconds (default), "m" = minutes, "h" = hours, "d" = days.

Converts "integer_expression" to a year month interval. "String_expression" specifies what "integer_ expression" represents: "y" = years, "m" = months (default).

Converts "numeric_expression" to a string, using the %g format specifier (C/C++ syntax).

Returns "string_expression" as a date in YYYY-MM-DD format.

Returns a floating point number. "String_expression" has the following form: "[whitespace] [sign] [digits] [digits] [ {d|D|e|E }[sign]digits]"

Returns "string_expression" as a date time interval in [-]DD HH:MM[:SS[.FFF]] format.

Returns an integer. "String_expression" has the following form: "[whitespace] [{+|-}] [digits]"

Returns a long integer. "String_expression" has the following form: "[whitespace] [{+|-}] [digits]"

Returns "string_expression" as a time in HH:MM:SS.FFFFFFF format.

Returns "string_expression" as a timestamp in YYYY-MM-DD [T|t|[white space]+] HH:MM:SS.FFFFFFF format.

Returns "string_expression" in YYYY-MM-DD HH:MM:SS.FFFFFFF +HHMM or YYYY-MMDD [T|t] HH:MM:SS.FFF -HHMM format.

Returns "string_expression" as a Year Month Interval in [-]YY MM format.

Returns a time as a string in HH:MM:SS.FFF format.

Converts "timestamp_expression" to a date. The time part of the timestamp will be ignored.

Returns a timestamp as a string in YYYY-MM-DD HH:MM:SS.FFFFFFF format.

Converts "timestamp_expression" to a timestamp with a time zone. The displacement part of the timestamp with the time zone will be zero.

Converts "timestamp_time_zone_expression" to a date. The time and time zone parts of the timestamp will be ignored.

Returns a timestamp with the time zone as a string in YYYY-MM-DD HH:MM:SS.FFFFFFF +HHMM or YYYY-MM-DD HH:MM:SS.FFF -HHMM format.

Converts "timestamp_time_zone_expression" to a timestamp. The displacement part of the timestamp with the time zone will be ignored.

Returns a time with the time zone as a string in HH:MM:SS.FFF+HHMMor HH:MM:SS.FFFFFFF -HHMM format. For example, -05:30 means a timezone of GMT minus 5 hours and 30 minutes

Returns "year_month_interval_expression" as a string in (YY MM) or -(YY MM) format.

Returns the ASCII code value of the leftmost character of the argument as an integer.

Returns the smallest integer greater than or equal to "numeric_expression".

Returns a string representation of a date/time value or a decimal number.

Returns the character that has the ASCII code value specified by "integer_expression". "Integer_ expression" should be between 0 and 255.

Returns a string that is the result of concatenating "string_expression1" with "string_expression2".

Returns a date from a single input value. "Expression" can be a string or integer representation of a date.

Returns the day of the month (1-31) from "date_expression". "Date_expression" can be a date value or a string representation of a date.

Returns a character string containing the data source-specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English, or Sonntag through Samstag for a data source that uses German) for the day portion of "date_expression". "Date_expression" can be a date value or a string representation of a date.

Returns the day of the week in "date_expression" as an integer in the range 1 to 7, where 1 represents Sunday. "date_expression" can be a date value or a string representation of a date.

Returns the day of the week in "date_expression" as an integer in the range 1 to 7, where 1 represents Monday. "date_expression" can be a date value or a string representation of a date.

Returns the day of the year in "date_expression" as an integer in the range 1 to 366. "Date_expression" can be a date value or a string representation of a date.

Returns an integer representation of a date. "Expression" can be a date value or a string representation of a date.

Returns the decimal representation of "string_expression1" with precision "numeric_expression1", scale "numeric_expression2", and decimal character "string_expression2". "String_expression1" must be formatted as an SQL Integer or Decimal constant.

Returns the decimal representation of "string_expression1" with precision "numeric_expression1", scale "numeric_expression2" and decimal character "string_expression2". "String_expression1" must be formatted as an SQL Integer or Decimal constant.

Returns an integer value representing the difference between the values returned by the data sourcespecific soundex function for "string_expression1" and "string_expression2". The value returned ranges from 0 to 4, with 4 indicating the best match. Note that 4 does not mean that the strings are equal.

Returns the character string representation of a non-floating point number.

Returns the floating-point representation of an expression. "Expression" can either be a numeric or string expression.

Returns the operational state of a particular state monitor.

Returns the floating-point representation of a number.

Returns the hexadecimal representation of a value.

Returns the hour, an integer from 0 (midnight) to 23 (11:00 pm), from "time_expression". "Time_expression" can be a time value or a string representation of a time.

Returns a string where "integer_expression2" characters have been deleted from "string_expression1" beginning at "integer_expression1" and where "string_expression2" has been inserted into "string_ expression1" at its start. The first character in the string is at position 1.

Returns the integer representation of an expression. "Expression" can be a numeric value or a string representation of a number.

Returns the integer representation of an expression. "Expression" can be a numeric value or a string representation of a number.

Returns an integer value representing the number of days from January 1, 4712 BC (the start of the Julian date calendar) to the date value specified in "expression". "Expression" can be a date value or a string representation of a date.

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

Returns the leftmost "integer_expression" characters of "string_expression".

Returns the length of the operand in bytes. Exception: double byte string types return the length in characters.

Returns the starting position of the first occurrence of "string_expression1" within "string_ expression2". The search starts at position start "integer_expression" of "string_expression2". The first character in a string is at position 1. If "string_expression1" is not found, zero is returned.

Returns a long string.

Returns "string_expression" with leading spaces removed.

Returns the microsecond (time-unit) part of a value. "Expression" can be a timestamp or a string representation of a timestamp.

Returns an integer value in the range 0 to 86400 representing the number of seconds between midnight and time value specified in the argument. "Expression" can be a time value, a timestamp or a string representation of a time.

Returns the minute (an integer from 0-59) from "time_expression". "Time_expression" can be a time value, a timestamp, or a string representation of a time.

Returns the month (an integer from 1-12) from "date_expression".

Returns a character string containing the data source-specific name of the month (for example, January through December or Jan. through Dec. for an English data source, or Januar through Dezember for a German data source) for the month portion of "date_expression".

Returns the quarter in "date_expression" as a number in the range 1 to 4, where 1 represents January 1 through March 31.

Returns the number of radians converted from "numeric_expression" degrees.

Returns a string consisting of "string_expression" repeated "integer_expression" times.

Replaces all occurrences of "string_expression2" in "string_expression1" with "string_expression3".

Returns the rightmost "integer_expression" characters of "string_expression".

Returns "numeric_expression" rounded to "integer_expression" places to the right of the decimal point. If "integer_expression" is negative, "numeric_expression" is rounded to the nearest absolute value "integer_expression" places to the left of the decimal point. Rounding takes place before data formatting is applied.

Returns "string_expression" with trailing spaces removed.

Returns the second (an integer from 0-59) from "time_expression".

Returns an indicator of the sign of "numeric_expression": +1 if "numeric_expression" is positive, 0 if zero, or -1 if negative.

Returns the small integer representation of a number.

Returns a 4 character string code obtained by systematically abbreviating words and names in "string_expression" according to phonetics. Can be used to determine if two strings sound the same. For example, does sound-of ('SMITH') = sound-of ('SMYTH').

Returns a string consisting of "integer_expression" spaces.

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

Returns an unqualified name of a table or view based on the object name in "string_expression1" and the schema name given in "string_expression2". It is used to resolve aliases.

Returns the schema name portion of the two-part table or view name based on the object name in "string_expression1" and the schema name in "string_expression2". It is used to resolve aliases.

Returns a time from a value.

Returns a timestamp from a value or a pair of values. "Expression1" must represent a date value, and "expression2" must represent a time value.

Returns a datetime in the ISO format (yyyy-mm-dd hh:mm:ss.nnnnnn) converted from the IBM format (yyyy-mm-dd-hh.mm.ss.nnnnnn). If "expression" is a time, it inserts the value of the CURRENT DATE for the date elements and zero for the fractional time element.

Returns an estimated number of intervals of type "expression1" based on the difference between two timestamps. "Expression2" is the result of subtracting two timestamp types and converting the result to CHAR. Valid values of "expression1" are: 1 Fractions of a second; 2 Seconds; 4 Minutes; 8 Hours; 16 Days; 32 Weeks; 64 Months; 128 Quarters; 256 Years.

Returns the string representation of a timestamp with the format of "string_expression".

Returns "string_expression1" in which characters from "string_expression3" are translated to the equivalent characters in "string_expression2". "String_expression4" is a single character that is used to pad "string_expression2" if it is shorter than "string_expression3". If only "string_expression1" is present, then this function translates it to uppercase characters.

Returns "numeric_expression1" truncated to "numeric_expression2" places to the right of the decimal point. If "numeric_expression2" is negative, "numeric_expression1" is truncated to the absolute value of "numeric_expression2" places to the left of the decimal point.

Returns "numeric_expression1" truncated to "numeric_expression2" places to the right of the decimal point. If "numeric_expression2" is negative, "numeric_expression1" is truncated to the absolute value of "numeric_expression2" places to the left of the decimal point.

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

Returns the first non-null argument (or null if all arguments are null). The Value function takes two or more arguments.

Returns a VARCHAR representation of expression, with length numeric_expression.

Returns the week of the year in "date_expression" as an integer value in the range 1 to 53.

Returns the year from "date_expression".

Returns the natural logarithm of "numeric_expression".

Returns the base ten logarithm of "numeric_expression".

Generates a random number using "integer_expression" as a seed value.

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the arctangent of the x and y coordinates specified by "numeric_expression1" and "numeric_ expression2", respectively, in radians. The returned angle will be between - and π radians, excluding π.

Returns the inverse hyperbolic tangent of "numeric_expression" where "numeric_expression" can be any real number between 1 and -1, excluding 1 and -1.

Returns the hyperbolic cosine of "numeric_expression" where "numeric_expression" can be any real number.

Returns the cotangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns "numeric_expression" radians converted to degrees.

Returns the sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the hyperbolic sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the hyperbolic tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the ancestor of "member" at "level" or at "integer" number of levels above "member". Note: The result is not guaranteed to be consistent when there is more than one such ancestor.

Returns all the ancestors of "member" at "level" or "index" distance above the member. (Most data sources support only one ancestor at a specified level. If the data source supports more than one ancestor, the result is a member set.)

Sorts a set according to the value of "numeric_expression" evaluated at each of the members of "set_expression" and returns the bottom "index_expression" members.

Sorts "numeric_expression2", evaluated at the corresponding members of "set_expression", and picks up the bottommost elements whose cumulative total is equal to or less than "numeric_ expression1" percent of the total.

Sorts "numeric_expression2", evaluated at the corresponding member of "set_expression", and picks up the bottommost elements whose cumulative total is equal to or less than "numeric_ expression1".

Returns the caption values of "level", "member", or "set_expression". The caption is the string display name for an element and does not necessarily match the unique identifier used to generate the business key or member unique name (MUN) for the element. The caption is not necessarily unique; for example, the caption for a month may return the month name without further year details to make the value unique.

Returns the set of children of a specified member.

Returns the last sibling member among the descendants of a member at "level". This function is typically used with a time dimension.

Returns the child member of "member2" with the same relative position as "member1" to its parent. This function appears in the Revenue by GO Subsidiary 2005 sample report in the GO Data Warehouse (analysis) package.

Identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. However, completeTuple () implicitly includes the default member from all dimensions not otherwise specified in the arguments, rather than the current member. CompleteTuple will use the default measure rather than the currentMeasure in the query if the measure is not defined in the completetuple function. This function appears in the Planned Headcount sample report in the GO Data Warehouse (analysis) package.

Returns the current member of the hierarchy during an iteration. If "hierarchy" is not present in the context in which the expression is being evaluated, its default member is assumed. This function appears in the Rolling and Moving Averages interactive sample report.

Returns the default member of "hierarchy".

Returns the set of descendants of "member" or "set_expression" at "level" (qualified name) or "distance" (integer 0..n) from the root. Multiple options may be specified (separated by a space) to determine which members are returned. self: Only the members at the specified level are included in the final set (this is the default behaviour in the absence of any options). before: If there are any intermediate levels between the member's level and the one specified, members from those levels are included. If the level specified is the same as the member upon which the function is applied, the member is included in the final set. beforewithmember: If there are any intermediate levels between the member's level and the one specified, members from those levels are included. The member upon which the function is applied is also included in the final set. after: If other levels exist after the specified level, members from those levels are included in the final set. This function appears in the Sales Commissions for Central Europe sample report in the GO Data Warehouse (analysis) package.

Returns the members of "set_expression1" that are not also in "set_expression2". Duplicates are retained only if the optional keyword all is supplied as the third argument.

Returns the set resulting from filtering a specified set based on the Boolean condition. Each member is included in the result if and only if the corresponding value of "Boolean_expression" is true.

Returns the first child of "member".

Returns the first child of the parent of "member".

Associates a format with the expression. The format_keyword can be PERCENTAGE_0, PERCENTAGE_ 1, or PERCENTAGE_2. PERCENTAGE_1 returns a percentage with one digit to the right of the decimal point, PERCENTAGE_2 returns a percentage with two digits to the right of the decimal point, and PERCENTAGE_3 returns a percentage value out of one with three digits to the right of the decimal point (for example, 0.965).

Returns an empty member set for "hierarchy". This is most often used as a placeholder during development or with dynamic report design (either with the IBM® Cognos® Software Development Kit or via report design). By creating a data item that contains the emptyset function, it is possible to build complex expressions that can later be revised by redefining the emptyset data item.

Evaluates "set_expression2" for each member of "set_expression1" and joins the resulting sets by union. The result retains duplicates only when the optional keyword "all" is supplied as the third argument.

Returns the first "index_expression" elements of "set_expression". The default for "index_expression" is 1.

Orders the members of "set_expression" in a hierarchy. Members in a level are sorted in their natural order. This is the default ordering of the members along a dimension when no other sort conditions are specified.

Returns the hierarchy that contains "level", "member", or "set_expression".

Returns a member from the "index" location within "set_expression". The index into the set is zero based.

Returns the intersection of "set_expression1" and "set_expression2". The result retains duplicates only when the optional keyword "all" is supplied as the third argument.

Returns the sibling member that is "index_expression" number of positions prior to "member".

Returns the last child of a specified member.

Returns the set of members from the same level that ends with "member". The number of members returned is the absolute value of "integer_expression". If "integer_expression" is negative, members following and including the specified member are returned. Typically used with a time dimension. This function appears in the Rolling and Moving Averages interactive sample report.

Returns the last child of the parent of a specified member.

Returns the sibling member that is "index_expression" number of positions after "member". If "index_expression" is negative, returns the sibling member that is "index_expression" number of positions before "member".

Returns the level of "member".

Returns the level in "hierarchy" whose distance from the root is specified by "index".

Returns the corresponding member in "level" or "hierarchy" (of the same dimension). For levelbased hierarchies, a level must be specified as the second argument, and for parent-child hierarchies, a hierarchy must be specified. An exception is thrown when the second parameter does not resolve to a hierarchy of the member's dimension. Note that calculated members are not supported as the first argument.

Returns the set of members in "hierarchy" or "level". In the case of a hierarchy, the order of the members in the result is not guaranteed. If a predictable order is required, an explicit ordering function (such as hierarchize) must be used.

Returns the next member in the "member" level.

Returns the first sibling member among the descendants of a member at "level". This function is typically used with a time dimension.

Arranges the members of "set_expression" according to their "value_expression" and the third parameter. ASC and DESC arrange members in ascending or descending order, respectively, according to their position in the set hierarchy. Then the children of each member are arranged according to "value_expression". BASC and BDESC arrange members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default.

Returns the zero-based ordinal value (distance from the root level) of "level".

Returns a member from a prior period in the same relative position as "member". This function is similar to the cousin function, but is more closely related to time series. It takes the ancestor of "member" at "level" (called "ancestor") and the sibling of "ancestor" that lags by "integer_ expression" positions, and returns the parallel period of "member" among the descendants of that sibling. When unspecified, "integer_expression" defaults to 1 and "member" defaults to the current member.

Returns the member that is the parent of "member" or "measure".

Returns a set of sibling members from the same level as "member", as constrained by "level". It locates the ancestor of "member" at "level" and returns that ancestor's descendants at the same level as "member" (up to and including "member"). Typically used with a time dimension. This function appears in the Rolling and Moving Averages interactive sample report.

Returns the member that immediately precedes "member" in the same level. This function appears in the Sales Growth Year Over Year sample report in the GO Data Warehouse (analysis) package.

Defines a member based on "value_expression" in "hierarchy". "String1" identifies the member created by this function. It must be unique in the query and different from any other member in the same hierarchy. "String2" is the caption of the member; if it is absent, the caption is empty. To ensure predictable results, it is recommended that you supply the "hierarchy". Note: All calculations used as grouping items whose sibling items are other calculations or member sets should be explicitly assigned to a hierarchy using this function. The results are not predictable otherwise. The only exception is where the calculation involves only members of the same hierarchy as the siblings. In this case, the calculation is assumed to belong to that hierarchy.

Returns the set of members of "set_expression2" evaluated in the context of the current member of "set_expression1".

Returns the list of members defined in the expression. The members must belong to the same hierarchy.

Returns the children of the parent of the specified member.

Returns the last "index_expression" elements of "set expression". The default for "index_expression" is 1.

Sorts a set according to the values of "numeric_expression" evaluated at each of the members of "set_expression" and returns the top "index_expression" members.

Sorts "numeric_expression2", evaluated at the corresponding members of "set_expression", and picks up the topmost elements whose cumulative total is at least "numeric_expression1" percent of the total.

Sorts "numeric_expression2", evaluated at the corresponding members of "set_expression", and picks up the topmost elements whose cumulative total is at least "numeric_expression1".

Identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. This function implicitly includes the current member from all dimensions that are not otherwise specified in the arguments. The current member of any dimension not specified in the evaluating context is assumed to be the default member of that dimension. The value of this cell can be obtained with the "value" function.

Returns data for "set_expression1" and "set_expression2". The result retains duplicates only when the optional keyword "all" is supplied as the third argument.

Returns the value of the attribute that is associated with the role whose name is specified by "string" within the specified context. "Member" or "set_expression" is optional only in a number of limited circumstances, where it can be derived from another context. Applications can be made portable across different data sources and models by accessing attributes by role rather than by query item ID. For dimensionally-modeled relational (DMR) data sources, assignment of roles is the modeler's responsibility. Intrinsic roles that are defined for members of all data source types include: '_businessKey', '_memberCaption', '_memberDescription', '_memberUniqueName'. Additional roles can be defined in Framework Manager for each level in a hierarchy. For example, a Product type level may have an attribute column called "Type Shipping Container", and the Product level may have a "Product Shipping Container" attribute. Each of these could be assigned a custom role in Framework Manager called "Container". The property could then be referenced independently of the actual column name by using the roleValue function.

Returns the root member of a single-root hierarchy. This function appears in the Promotion Success sample report in the GO Data Warehouse (analysis) package.

Returns the root members of a hierarchy.

Returns a subset of members in "set_expression" starting at "index_expression1" from the beginning. If the count "index_expression2" is specified, that many members are returned (if available). Otherwise, all remaining members are returned.

Removes all duplicates from "set_expression". The remaining members retain their original order.

Returns the value of the cell identified by "tuple". Note that the default member of the Measures dimension is the Default Measure.

Returns the string representation of "expression" with the format of "string_expression". "Expression" can either be a date value or a numeric value.

Converts "string_expression1" to a date value as specified by the format "string_expression2".

Converts "string_expression1" to a numeric value as specified by the format "string_expression2".

Converts "string_expression1" to a timestamp value as specified by the format "string_expression2".

Returns "string_expression1" with each occurrence of each character in "string_expression2" replaced by its corresponding character in "string_expression3".

Returns the timestamp to the specified precision.

Returns the string value of the database version.

Returns the base 10 logarithm of "numeric_expression1" or logarithm to the base "numeric_ expression2".

Returns the natural logarithm of "numeric_expression1".

Returns the cube root of "numeric_expression1".

Returns the constant of pi.

Returns the "string_expression1" replacing "string_expression2" from character position "numeric_ expression".

Returns "string_expression1" after removing the longest string of characters in "string_expression2".

Returns "string_expression" with the first letter of each word in uppercase and all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Returns "string_expression1" padded to length "integer_expression" with occurrences of "string_ expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned.

Returns "string_expression1", with leading characters removed up to the first character not in "string_expression2"; for example, ltrim ( 'xyxXxyAB' , 'xy' ) returns XxyAB.

Returns the MD5 hash of "string_expression1".

Returns the hexadecimal string representation of "numeric_expression1".

Returns the "string_expression" repeated "numeric_expression1" times.

Returns "string_expression" having replaced "string_expression2" with "string_expression3".

Returns "string_expression1" right-padded to length "integer_expression" with occurrences of "string_expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned. If "string_expression2" is not specified, then spaces are used.

Returns "string_expression1", with final characters removed after the last character not in "string_ expression2"; for example, rtrim ( 'ABxXxyx' , 'xy' ) returns ABxX. If "string_expression2" is not specified, the final space characters are removed.

Returns "numeric_expression" field having split "string_expression1" on "string_expression2".

Returns a number representing the ascii code value of the leftmost character of "string_expression"; for example, ascii ( 'A' ) is 65.

Returns the character that has the ASCII code value specified by "integer_expression". "Integer_ expression" should be between 0 and 255.

Returns the name of the current database.

Returns the name of the current schema.

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the degree equivalent of "numeric_expression". Results are of the same type as "numeric_ expression". For numeric or decimal expressions, the results have an internal precision of 77 and a scale equal to that of "numeric_expression". When the money datatype is used, an internal conversion to float may cause some loss of precision.

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression". "Numeric_expression" must be between -1 and 1, inclusive.

Returns the degrees where "numeric_expression" is an angle expressed in radians.

Returns the number of elements in a collection column (SET, MULTISET, LIST).

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.

Returns a string that is the result of concatenating, or joining, "string_expression1" to "string_ expression2".

Returns the date value of "string_expression", "date_expression", or "integer_expression".

Returns an integer that represents the day of the month (1-31).

Adjusts the precision of a datetime or date expression. The expression cannot be a quoted string representation of a date value. If you do not specify first and last qualifiers, the default qualifiers are year to fraction (3). If the expression contains fields that are not specified by the qualifiers, the unwanted fields are discarded. If the first qualifier specifies a larger (more significant) field than what exists in the expression, the new fields are filled in with values returned by the current function. If the last qualifier specifies a smaller (less significant) field than what exists in the expression, the new fields are filled in with constant values. A missing month or day field is filled in with 1, and missing hour to fraction fields are filled in with 0.

Returns the hexadecimal encoding of "integer_expression".

Returns "string_expression" with the first letter of each word in uppercase and all other letters in lowercase. A word begins after any character other than a letter. Thus, in addition to a blank space, symbols such as commas, periods, and colons can introduce a new word.

Returns the number of bytes in "string_expression", not including any trailing blank spaces. For byte or text "string_expression", length returns the full number of bytes, including any trailing blank spaces.

Returns "string_expression1" left-padded by "string_expression2" to the total number of characters specified by "integer_expression". The sequence of "string_expression2" occurs as many times as necessary to make the return string the length specified by "integer_expression".

Returns a type date value with three expressions that evaluate to integers that represent the month (integer_expression1), day (integer_expression2), and year (integer_expression3).

Returns an integer corresponding to the month portion of "date_expression".

Returns the value of "expression1" if "expression1" is not NULL. If "expression1" is NULL, then returns the value of "expression2".

Returns the number of bytes in "string_expression", including any trailing spaces.

Returns "string_expression1" in which every occurrence of "string_expression2" is replaced by "string_expression3". If you omit the "string_expression3" option, every occurrence of "string_ expression2" is omitted from the return string.

Returns the rounded value of "numeric_expression". If you omit "integer_expression", the value is rounded to zero digits or to the units place. The digit range of 32 (+ and -) refers to the entire decimal value. Rounding takes place before data formatting is applied.

Returns "string_expression1" right-padded by "string_expression2" to the total number of characters specified by "integer_expression". The sequence of "string_expression2" occurs as many times as necessary to make the return string the length specified by "integer_expression".

Returns the substring of "string_expression" that starts at position "integer_expression1" for "integer_expression2" characters. The first character in "string_expression" is at position 1. If you omit "integer_expression2", returns the substring of "string_expression" that starts at position "integer_expression1" and ends at the end of "string_expression".

Returns the character string "date_expression" with the specified "string_expression" formatting. You can use this function only with built-in data types.

Returns "string_expression1" as a date according to the date format you specify in "string_ expression2". If "string_expression1" is NULL, then a NULL value is returned.

Returns the truncated value of "numeric_expression". If you omit "integer_expression", then "numeric_expression" is truncated to zero digits or to the unit’s place. The digit limitation of 32 (+ and -) refers to the entire decimal value.

Returns an integer that represents the day of the week of "date_expression". Zero (0) represents Sunday, one (1) represents Monday, and so on.

Returns a four-digit integer that represents the year of "date_expression".

Returns the logarithm of "numeric_expression" to base 10.

Returns the natural logarithm of "numeric_expression".

Returns the root value of "numeric_expression1". Requires at least one numeric argument (the radians argument). If only "numeric_expression1" is supplied, 2 is used as a default value for "numeric_expression2". Zero cannot be used as the value of "numeric_expression2".

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the arctangent of the x and y coordinates specified by "numeric_expression1" and "numeric_ expression2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_ expression2" / "numeric_expression1".

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Concatenates two strings.

Returns the timestamp with time zone (as a string) that results from adding "integer_expression" number of days to "string_expression", where "string_expression" represents a timestamp with time zone.

Returns the timestamp with time zone (as a string) that results from adding "integer_expression" number of months to "string_expression", where "string_expression" represents a timestamp with time zone.

Returns the timestamp with time zone (as a string) that results from adding "integer_expression" number of years to "string_expression", where "string_expression" represents a timestamp with time zone.

Constructs an array out of the list of parameters.

Constructs a string from the elements of the array where the values are separated by commas. Optionally, the separator and quote strings can be specified. The default separator is a comma ( , ) and the default quote character is a single quote ( ' ).

Surrounds "string_expression" with double quotes.

Get an entry from the IBM® Cognos® configuration file. The force_decode_flag is optional and must be one of: 'true' , '1', 1 , 'false', '0', 0. The default is 'true'. When true, the value of the configuration entry will be decrypted into plain text if it is encrypted.

Searches for and returns elements of an array that match the pattern specified in "pattern_string".

Returns a timestamp with time zone (as a string) by converting the day value in "string_expression" to 1, where "string_expression" is a timestamp with time zone.

Returns a timestamp with time zone (as a string) that is the last day of the month represented by "string_expression", where "string_expression" is a timestamp with time zone.

Joins the elements of an array using "separator_string".

Prompts the user for a single value. Only "prompt_name" is required. The datatype defaults to "string" when it is not specified. The prompt is optional when "defaultText" is specified. "Text", when specified, will precede the value. "QueryItem" can be specified to take advantage of the prompt information properties of "queryItem". "Trailing_text", when specified, will be appended to the value.

Prompts the user for one or more values. Only "prompt_name" is required. The datatype defaults to string when it is not specified. The prompt is optional when "defaultText" is specified. "Text", when specified, will precede the list of values. "QueryItem" can be specified to take advantage of the prompt information properties of "queryItem". "Trailing_text", when specified, will be appended to the list of values.

Surrounds "string_expression" with square brackets.

Surrounds "string_expression" with single quotes.

Sorts the elements of the array in alphabetical order. Duplicates are retained.

Splits a string or string elements of the array into separate elements.

Searches for a pattern in a string or in the string elements of an array and substitutes the first occurrence of "pattern_string" with "replacement_string".

Returns "string_expression1", representing a timestamp with time zone, trimmed to the format specified in "string_expression2". The format in "string_expression2" must be one of the following: 'yyyy', 'mm', 'dd', 'yyyy-mm', 'yyyymm', 'yyyy-mm-dd', 'yyyymmdd', 'yyyy-mm-dd hh:mm:ss', 'yyyy-mm-dd hh:mm:ss+hh:mm', 'yyyy-mm-dd hh:mm:ss.ff3', 'yyyy-mm-dd hh:mm:ss.ff3+hh:mm', 'yyyy-mm-ddThh:mm:ss', 'yyyy-mm-ddThh:mm:ss+hh:mm', 'yyyy-mm-ddThh:mm:ss.ff3+hh:mm', or 'yyyy-mm-ddThh:mm:ss.ff3+hh:mm'. The macro functions that return a string representation of a timestamp with time zone show a precision of 9 digits for the fractional part of the seconds by default. The format options allow this to be trimmed down to a precision of 3 or 0.

Returns the string representing a timestamp with time zone resulting from adjusting "string_expression" to the time zone of the operating system. Note that the macro function timestampMask () can be used to trim the output.

Returns the string representing a timestamp with time zone resulting from adjusting "string_expression" to the zero-point reference UTC time zone, also known as GMT time. Note that the macro function timestampMask () can be used to trim the output.

Removes duplicate entries from the array. The order of the elements is retained.

URL-encodes the passed argument. This function is useful when specifying XML connection strings.

Uses the identity information of the current authenticated user to look up values in the specified parameter map. Each individual piece of the user's identity (account name, group names, role names) is used as a key into the map. The unique list of values that is retrieved from the parameter map is then returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.

Returns the pieces of the user's identity (account name, group names, role names) as a list of strings. The unique list of values is returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.

Returns the Cognos® Access Manager passport.

Returns the pieces of the user's Cognos® Access Manager ID (CAMID), such as account name, group names, or role names, as a list of values separated by commas.

Returns an array of the user's Cognos® Access Manager IDs (CAMIDs) based on the identity type (account, group, or role). CAMIDListForType can be used with the macro functions csv or join.

Returns the rank of a value within a range that you specify. It returns integers to represent any range of ranks, such as 1 (highest) to 100 (lowest). 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.

Returns the rank of a value, represented as integers from 1 (highest) to 4 (lowest), relative to a group of values. 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.

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. This function appears in the Top 10 Retailers for 2005 sample report in the GO Data Warehouse (analysis) package.

Returns the standard deviation of the selected data items.

Returns the standard deviation population of the selected data items.

Returns the total value of the selected data items.

Returns the variance of the selected data items.

Returns the variance population of the selected data items.

Returns a calculated value using the appropriate aggregation function, based on the aggregation type of the expression. This function appears in the Budget vs. Actual sample report in the GO Data Warehouse (analysis) package.

Returns the number of selected data items excluding null values. Distinct is an alternative expression that is compatible with earlier versions of the product.

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

Returns the median value of selected data items.

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

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

Returns the ascii code value of the leftmost character of "string_expression".

Returns the smallest integer greater than or equal to "numeric_expression".

Returns the character that has the ASCII code value specified by "integer_expression". "Integer_ expression" should be between 0 and 255.

Returns a string that is the result of concatenating, or joining, "string_expression1" to "string_ expression2".

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

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

Returns a character string containing the data source-specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for an English data source, or Sonntag through Samstag for a German data source) for the day portion of "date_expression".

Returns the day of the month (1-31) from "date_expression". Returns the days field (a signed integer) from "interval_expression".

Returns the day of the week in "date_expression" as an integer (1-7), where 1 represents Monday.

Returns the day of the year in "date_expression" as an integer (1-366).

Returns the hour from "time_expression" as an integer from 0 (midnight) to 23 (11:00 pm).

Searches "string_expression1" for the first occurrence of "string_expression2" and returns an integer specifying the position of "string_expression2". "Integer_expression1" sets the starting position for the search. If "integer_expression1" is omitted, the search begins at the first character position of "string_expression1". "Integer_expression2" specifies the type of string comparison. "Integer_expression1" is required if "integer_expression2" is specified.

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

Returns the leftmost "integer_expression" characters of "string_expression".

Returns the number of characters in "string_expression", excluding trailing blanks and the string termination character.

Returns the starting position of the first occurrence of "string_expression1" within "string_ expression2". The search starts at position "integer_expression" of "string_expression2". The first character in a string is at position 1. If "string_expression1" is not found, then zero is returned.

Returns "string_expression" with leading spaces removed.

Returns the minute (an integer from 0-59) from "time_expression".

Returns the month (an integer from 1-12) from "date_expression".

Returns a character string containing the data source-specific name of the month (for example, January through December or Jan. through Dec. for an English data source, or Januar through Dezember for a German data source) for the month portion of "date_expression".

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

Returns the starting position of "string_expression1" in "string_expression2". The first character in a string is at position 1.

Returns the quarter in "date_expression" as a number (1-4), where 1 represents January 1 through March 31.

Returns the rightmost "integer_expression" characters of "string_expression".

Returns "numeric_expression" rounded to the nearest value "integer_expression" places right of the decimal point. If "integer_expression" is negative, "numeric_expression" is rounded to the nearest absolute value "integer_expression" places to the left of the decimal point. Rounding takes place before data formatting is applied.

Returns "string_expression" with trailing spaces removed.

Returns an indicator of the sign of "numeric_expression", +1 if positive, 0 if zero, or -1 if negative.

Returns a string consisting of "integer_expression" spaces.

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

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

Returns "string_expression" with trailing spaces removed.

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

Returns the week of the year in "date_expression" as an integer value (1-53), where 1 represents the first week of the year.

Returns the year from "date_expression".

Returns the value of "expression" cast as a decimal.

Returns the value of "expression" cast as a float.

Returns the value of "expression" cast as an integer.

Returns "string_expression" cast as a numeric value.

Returns the value of "expression" cast as a real value.

Returns "expression" cast as a small integer.

Returns the value of "expression" cast as a variable character field.

Returns the natural logarithm of "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the base 10 logarithm of "numeric_expression1" or logarithm to the base "numeric_ expression2".

Returns the natural logarithm of "numeric_expression1".

Returns the constant of pi.

Returns "string_expression1" padded to length "integer_expression" with occurrences of "string_ expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned.

Returns "string_expression1", with leading characters removed up to the first character not in "string_expression2"; for example, ltrim ( 'xyxXxyAB' , 'xy' ) returns XxyAB.

Returns the hexadecimal string representation of "numeric_expression1".

Returns the "string_expression" repeated "numeric_expression1" times.

Returns "string_expression" having replaced "string_expression2" with "string_expression3".

Returns "string_expression" reversed.

Returns the rightmost "numeric_expression" characters from "string_expression1".

Returns "string_expression1" right-padded to length "integer_expression" with occurrences of "string_expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned. If "string_expression2" is not specified, then spaces are used.

Returns "string_expression1", with final characters removed after the last character not in "string_ expression2"; for example, rtrim ( 'ABxXxyx' , 'xy' ) returns ABxX. If "string_expression2" is not specified, the final space characters are removed.

Returns a soundex string of "string_expression1".

Returns a number representing the ASCII code value of the leftmost character of "string_expression"; for example, ascii ( 'A' ) is 65.

Returns the current database name

Returns the current schema name

Return the user name returned by the client

Return the user name returned by the client

Returns the string value of the database version.

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression". "Numeric_expression" must be between -1 and 1, inclusive.

Returns the cotangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the degrees where "numeric_expression" is an angle expressed in radians.

Returns the radians where "numeric_expression" is an angle expressed in degrees.

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the arctangent of the x and y coordinates specified by "numeric_expression1" and "numeric_ expression2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_ expression2" / "numeric_expression1".

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns a number representing the ASCII code value of the leftmost character of "string_expression"; for example, ascii ( 'A' ) is 65.

Returns the character that has the ASCII code value specified by "integer_expression". "Integer_ expression" should be between 0 and 255.

Compares "expr" to each search value one by one. If "expr" is equal to a search, then it returns the corresponding result. If no match is found, it returns "default". If "default" is omitted, it returns null.

Returns "string_expression", with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Searches "string_expression1" starting at position "integer_expression1" for the "integer_expression2" occurrence of "string_expression2". If "integer_expression1" is negative then the search is backwards from the end of "string_expression1". Returns an integer indicating the position of "string_expression2".

Returns "string_expression1" padded to length "integer_expression" with occurrences of "string_ expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned.

Returns "string_expression1", with leading characters removed up to the first character not in "string_expression2"; for example, ltrim ( 'xyxXxyAB' , 'xy' ) returns XxyAB.

Returns the number of months from "date_expression1" to "date_expression2". If "date_expression1" is later than "date_expression2" then the result will be a positive number. The days and time portions of the difference are ignored, i.e., the months are not rounded, except if "date_expression1" and "date_expression2" are the last days of a month.

Returns the datetime of the first weekday named by "string_expression" that is later than "datetime_ expression". The return value has the same hours, minutes, and seconds as "datetime_expression".

Returns "expression" if not null, otherwise returns "constant". Valid for "numeric_expression", "string_expression", "date_expression", and "time_expression".

Returns "numeric_expression" rounded to the nearest value "integer_expression" places right of the decimal point. If "integer_expression" is negative, "numeric_expression" is rounded to the nearest absolute value "integer_expression" places to the left of the decimal point; for example, round (125, -1) rounds to 130.

Returns "string_expression1" right-padded to length "integer_expression" with occurrences of "string_expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned. If "string_expression2" is not specified, then spaces are used.

Returns "string_expression1", with final characters removed after the last character not in "string_ expression2"; for example, rtrim ( 'ABxXxyx' , 'xy' ) returns ABxX. If "string_expression2" is not specified, the final space characters are removed.

Returns the substring of "string_expression" that starts at position "integer_expression1". The first character in "string_expression" is at position 1. "Integer_expression2" can be used to select fewer characters; by default it selects characters to the end of the string.

Returns the database username (authorization ID) of the current user.

Returns the string representation of "expression" with the format of "string_expression". "Expression" can be either a date value or a numeric value.

Converts "string_expression1" to a datetime value as specified by the format "string_expression2".

Converts "string_expression1" to a numeric value as specified by the format "string_expression2".

Returns "string_expression1", with all occurrences of each character in "string_expression2" replaced by its corresponding character in "string_expression3".

Truncates "date_expression1" to a value as specified by the format "string_expression1".

Truncates digits from "numeric_expression1" using "numeric_expression2" as the precision.

Returns the "string_expression1" value of the database version.

Returns a value indicating how different the two input strings are, calculated according to the Levenshtein edit distance algorithm.

Returns a value indicating how different the two input strings are, calculated according to the Damerau-Levenshtein distance algorithm

Returns the logarithm of "numeric_expression2" to the base "numeric_expression1".

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians where the arctangent is the angle whose tangent is "numeric_expression".

Returns the arctangent of the x and y coordinates specified by "numeric_expression1" and "numeric_ expression2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_ expression2" / "numeric_expression1".

Returns the cosine of "numeric_expression", where "numeric_expression" is an angle expressed in radians.

Returns the degrees where "numeric_expression" is an angle expressed in radians.

Returns the radians where "numeric_expression" is an angle expressed in degrees.

Returns the sine of "numeric_expression", where "numeric_expression" is an angle expressed in radians.

Returns the tangent of "numeric_expression", where "numeric_expression" is an angle expressed in radians.

Returns a Soundex representation of "string_expression" using the New York State Identification and Intelligence System (NYSIIS) variation of Soundex.

Returns a composite 32-bit value of "string_expression".

Returns the 4 character primary metaphone string from "numeric_expression" returned by dbl_mp.

Returns the 4 character secondary metaphone string from "numeric_expression" returned by dbl_mp.

Returns a score for how closely "numeric_expression" and "numeric_expression2" match.

Identifies the beginning of an expression.

Identifies the end of an expression.

Multiplies two numeric values.

Separates expression components.

Divides two numeric values.

Concatenates, or joins, strings.

Adds two numeric values.

Subtracts two numeric values or negates a numeric value.

that are less than "value2".

that are less than or equal to "value2".

that are not equal to "value2".

that are equal to "value2".

that are greater than "value2".

Separates the components in a literal member expression.

that are greater than or equal to "value2".

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

in the query. The scope is context-dependent.

Determines if a value falls in a given range.

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

Determines if "string1" contains "string2".

Keyword that can be used as the first argument of member summary functions. This function appears in the Total Revenue by Country sample report in the GO Data Warehouse (query) package.

Works with the lookup construct.

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

Indicates the end of a case or when construct.

Determines if "string1" ends with "string2".

Works with summary expressions to define the scope of the aggregation in the query.

Works with summary expressions to define the scope to be all the specified grouping columns in the query. See also the for clause.

Works with summary expressions to define the scope to be adjusted based on a subset of the grouping columns in the query. Equivalent to the for clause.

Works with summary expressions to set the scope to be the whole query. See also the for clause. This function appears in the Customer Returns and Satisfaction sample report in the GO Data Warehouse (analysis) package.

Works with the 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. This function appears in the Top 10 Retailers for 2005 sample report in the GO Data Warehouse (analysis) package.

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

Determines if "expression1" exists in a given list of constant values or ranges.

Determines if "value" is undefined in the data.

Determines if "value" is undefined in the data.

Determines if "value" is defined in the data.

Determines if "value" is defined in the data.

Determines if "string1" matches the pattern of "string2".

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

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

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

Performs a summary calculation before applying the summary filter.

Counts the number of rows output by the query. Use with Count ().

Determines if "string1" starts with "string2".

Works with the if or case constructs. When the if condition or the when expression are true, the then expression is used. This function appears in the Top 10 Retailers for 2005 sample report in the GO Data Warehouse (analysis) package.

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

Returns the datetime resulting from adding "integer_expression" months to "date_expression".

Returns a number representing the ASCII code value of the leftmost character of "string_expression".

Returns the smallest integer greater than or equal to "numeric_expression".

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.

Returns the character that has the ASCII code value specified by "integer_expression". "Integer_ expression" should be between 0 and 255.

Returns a string that is the result of concatenating, or joining, "string_expression1" to "string_ expression2".

Compares "expression" to each search value one by one. If "expression" is equal to a search, then it returns the corresponding result. If no match is found, it returns "default", or if "default" is omitted, it returns null.

Returns internal representation of "expression" with the format of "numeric_expression1" starting from position "numeric_expression2" for "numeric_expression3" characters.

Returns the greatest value in "expression_list".

Returns "string_expression" with the first letter of each word in uppercase and all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Searches "string_expression1" starting at position "integer_expression1" for the "integer_expression2" occurrence of "string_expression2". If "integer_expression1" is negative, then the search occurs backwards from the end of "string_expression1". Returns an integer indicating the position of "string_expression2".

Searches "string_expression1" starting at position "integer_expression1" for the "integer_expression2" occurrence of "string_expression2". If "integer_expression1" is negative, then the search occurs backwards from the end of "string_expression1". Returns the position (byte number) where "string_expression2" was found.

Returns the least value in "expression_list".

Returns the number of characters in "string_expression".

Returns the number of bytes in "string_expression".

Returns "string_expression1" left-padded to the length defined by "integer_expression" with occurrences of "string_expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned.

Returns "string_expression1" with leading characters removed up to the first character not in "string_expression2".

Returns the number of months from "date_expression1" to "date_expression2". If "date_expression1" is later than "date_expression2" then the result will be a positive number. The days and time portion of the difference are ignored, so the months are not rounded unless "date_expression1" and "date_expression2" are the last days of a month.

Returns the datetime in "new_timezone" for "datetime_expression" in "old_timezone". "Old_timezone" and "new_timezone" can be one of 'AST', 'ADT', 'BST', 'BDT', 'CST', 'CDT', 'EST', 'EDT', 'HST', 'HDT', 'MST', 'MDT', 'NST', 'PST', 'PDT', 'YST', or 'YDT'.

Returns the datetime of the first weekday named by "string_expression" that is later than "datetime_ expression". The return value has the same format as "datetime_expression".

Returns "string_expression1" with the first letter of each word in uppercase and all other letters in lowercase. A word begins after any character other than a letter. Thus, in addition to a blank space, symbols such as commas, periods, and colons can introduce a new word. "String_expression2" specifies the sorting sequence.

Returns "string_expression1" with all letters in lowercase. "String_expression2" specifies the sorting sequence.

Returns "string_expression1" with all letters in uppercase. "String_expression2" specifies the sorting sequence.

Returns "expression" unless it is null. If "expression" is null, returns "constant". Valid for "numeric_ expression", "string_expression", "date_expression", and "time_expression".

Replaces all occurrences of "string_expression2" in "string_expression1" with "string_expression3". If "string_expression3" is not specified, then it removes all occurrences of "string_expression2".

Returns "numeric_expression" rounded to the nearest value "integer_expression" places right of the decimal point. If "integer_expression" is negative, "numeric_expression" is rounded to the nearest absolute value "integer_expression" places to the left of the decimal point. Rounding takes place before data formatting is applied.

Returns "string_expression1" right-padded to length "integer_expression" with occurrences of "string_expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned. If "string_expression2" is not specified, then occurrences of "string_expression2" are replaced with spaces.

Returns "string_expression1" with the final characters removed after the last character not in "string_expression2". If "string_expression2" is not specified, the final space characters are removed.

Returns an indicator of the sign of "numeric_expression", +1 if positive, 0 if zero, or -1 if negative.

Returns a character string containing the phonetic representation of "string_expression".

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.

Returns the substring of "string_expression" that starts at position "numeric_expression1" and ends after "numeric_expression2" bytes. The first byte in "string_expression" is at position 1. If you omit "numeric_expression2", returns the substring of "string_expression" that starts at position "numeric_expression1" and ends at the end of "string_expression".

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

Returns the string representation of "expression" with the format of "string_expression". "Expression" can be either a date value or a numeric value.

Converts "string_expression1" to a datetime value as specified by the format "string_expression2". "String_expression3" specifies the format elements, such as language.

Converts "string_expression1" to a numeric value as specified by the format "string_expression2". "String_expression3" specifies the format elements, such as currency information.

Returns "string_expression1" with all occurrences of each character in "string_expression2" replaced by the corresponding character in "string_expression3".

Truncates "date_expression" using the format specified by "string_expression". For example, if "string_expression" is 'year', then "date_expression" is truncated to the first day of the year.

Truncates digits from "numeric_expression1" using "numeric_expression2" as the precision.

Returns the username of the current Oracle user.

Returns the number of bytes in the internal representation of "expression". "Expression" must be a string expression.

Returns the logarithm of "numeric_expression2" to the base "numeric_expression1".

Returns the hyperbolic sine of "numeric_expression" where "numeric_expression" can be any real number.

Returns the tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the hyperbolic tangent of "numeric_expression" where "numeric_expression" can be any real number.

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the arctangent of the x and y coordinates specified by "numeric_expression1" and "numeric_ expression2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_ expression2" / "numeric_expression1".

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the hyperbolic cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the string representation of "expression" with the format of "string_expression". "Expression" can be either a date value or a numeric value.

Converts "string_expression1" to a date value as specified by the format "string_expression2".

Converts "string_expression1" to a numeric value as specified by the format "string_expression2".

Returns "string_expression1", with each occurrence of each character in "string_expression2" replaced by its corresponding character in "string_expression3".

Returns the string value of the database version.

Returns the cube root of "numeric_expression1".

Returns the constant of pi.

Returns the "string_expression1", replacing "string_expression2" from character position numeric_expression.

Returns "string_expression1", with leading characters removed up to the first character not in "string_expression2"; for example, ltrim ( 'xyxXxyAB' , 'xy' ) returns XxyAB.

Returns "string_expression", having replaced "string_expression2" with "string_expression3".

Returns "string_expression1", with final characters removed after the last character not in "string_ expression2"; for example, rtrim ( 'ABxXxyx' , 'xy' ) returns ABxX. If "string_expression2" is not specified, the final space characters are removed.

Returns the name of the current database.

Returns the name of the current schema

Returns the string representation of "expression" with the format of "string_expression". "Expression" can be either a date value or a numeric value.

Converts "string_expression1" to a date value as specified by the format "string_expression2".

Converts "string_expression1" to a numeric value as specified by the format "string_expression2".

Converts "string_expression1" to a timestamp value as specified by the format "string_expression2".

Returns "string_expression1", with each occurrence of each character in "string_expression2" replaced by its corresponding character in "string_expression3".

Returns the timestamp to the specified precision.

Returns the string value of the database version.

Returns the base 10 logarithm of "numeric_expression1" or logarithm to the base "numeric_ expression2".

Returns the natural logarithm of "numeric_expression1".

Returns the cube root of "numeric_expression1".

Returns the integer quotient of "numeric_expression1" divided by "numeric_expression2".

Returns the constant of pi.

Returns the "string_expression1" replacing "string_expression2" from character position numeric_expression.

Returns string_expression1 after removing the longest string of characters in "string_expression2".

Returns "string_expression", with the first letter of each word in uppercase and all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Returns "string_expression1" padded to length "integer_expression" with occurrences of "string_ expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned.

Returns "string_expression1", with leading characters removed up to the first character not in "string_expression2"; for example, ltrim ( 'xyxXxyAB' , 'xy' ) returns XxyAB.

Returns the MD5 hash of "string_expression1".

Returns the hexadecimal string representation of "numeric_expression1".

Returns the "string_expression" repeated "numeric_expression1" times.

Returns "string_expression" with "string_expression2" replaced with "string_expression3".

Returns "string_expression1" right-padded to length "integer_expression" with occurrences of "string_expression2". If "string_expression1" is longer than "integer_expression", the appropriate portion of "string_expression1" is returned. If "string_expression2" is not specified, then spaces are used.

Returns "string_expression1", with final characters removed after the last character not in "string_ expression2"; for example, rtrim ( 'ABxXxyx' , 'xy' ) returns ABxX. If "string_expression2" is not specified, the final space characters are removed.

Returns "numeric_expression" field having split "string_expression1" on "string_expression2".

Returns a number representing the ASCII code value of the leftmost character of "string_expression"; for example, ascii ( 'A' ) is 65.

Returns the character that has the ASCII code value specified by "integer_expression". "Integer_ expression" should be between 0 and 255.

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression".

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the arctangent of the x and y coordinates specified by "numeric_expression1" and "numeric_ expression2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_ expression2" / "numeric_expression1".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the cotangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the degrees where "numeric_expression" is an angle expressed in radians.

Returns the radians where "numeric_expression" is an angle expressed in degrees.

Returns the sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the smallest integer greater than or equal to "numeric_expression" or "string_expression". Note that "string_expression" must represent a valid numeric value.

Returns a string that is the result of concatenating, or joining, "string_expression1" to "string_ expression2".

Returns a date value. "Expression" can be either characters or a timestamp.

Adds "interval" to "datetime_expression" and returns a result that is the same datetime data type as "datetime_expression". "Datepart" refers to the year, month, day, hour, minute, second. "Interval" must be an integer and "datetime_expression" can be a date, time, or timestamp.

Determines the difference between two datetime expressions and returns an integer result in "datepart" units. "Datepart" refers to a year, month, day, hour, minute, or second. "Datetime_ expression1" and "datetime_expression2" can be dates, times, or timestamps.

Extracts "datepart" of "datetime_expression" and returns its value as a character string. "Datepart" refers to a year, month, day, hour, minute, or second. "Datetime_expression" can be a date, a time, or a timestamp.

Converts "expression" to a decimal value with the data type decimal (precision, scale). The default value of precision is 9. The default value of scale is 0.

Converts "expression" to a decimal value with the data type decimal (precision, scale). The default value of precision is 9. The default value of scale is 0.

Compares and converts "expression" to another value. If "expression" matches "target", it is replaced, otherwise it is replaced by "default" or null if no default is specified. The expressions can be any data type as long as they are all the same data type.

Converts "numeric_expression" into a double-precision floating-point value.

Tests "expression" for missing values and replaces each one with "substitute". If "expression" is null, "substitute" is returned, otherwise it returns the value of "expression". The expressions can be any data type as long as they are all the same data type.

Converts "numeric_expression" into an integer value and returns an integer value. If "numeric_ expression" is null, it returns null.

Converts "numeric_expression" into an integer value and returns an integer value. If "numeric_ expression" is null, it returns null.

Returns an integer result specifying the number of characters in "string_expression". If "string_expression" is null, it returns null.

Returns an integer result specifying the number of bytes in "string_expression". If "string_expression" is null, it returns null.

Removes leading blanks from "string_expression". If "string_expression" is null, it returns null.

Returns null if both "expression1" and "expression2" have the same value. If they have different values, the value of "expression1" is returned. "Expression1" and "expression2" can be any data type as long as they are the same data type.

Returns an integer that is relative to the beginning byte position of "string_expression1" in "string_ expression2". If "string_expression1" is not located, the result is 0. If "string_expression1" is of zero length, the result is 1. If "string_expression1" is null, an error message is returned. If "string_ expression2" is null, the result is 0.

Returns a real value. If "numeric_expression" is null, it returns null.

Returns "numeric_expression" rounded to the nearest value "integer_expression" places to the right of the decimal point. If "integer_expression" is negative, "numeric_expression" is rounded to the nearest absolute value "integer_expression" places to the left of the decimal point. Rounding takes place before data formatting is applied.

Removes trailing blanks from "string_expression". If "string_expression" is null, it returns null.

Determines the sign of "numeric_expression", and returns 1 for a positive value, –1 for a negative value, and 0 for zero.

Converts "expression" to a character string. "Expression" can be either numeric or datetime.

Returns a substring of "string_expression" that begins at position "start_integer" and continues for "length_integer" characters. If "length_integer" is not specified, a substring from "start_integer" to the end of "string_expression" is returned.

Returns a substring of "string_expression" that begins at position "start_integer" and continues for "length_integer" bytes. If "length_integer" is not specified, a substring from "start_integer" to the end of "string_expression" is returned.

Creates a time value from "expression", which can be a character string or a time-stamp data type expression.

Creates a time-stamp value from "timestamp_expression", which is a character string.

Creates a time-stamp value from "time_expression" and "date_expression". If either "time_expression" or "date_expression" is null, the resulting time-stamp expression is also null.

Converts "source_date" to the character string specified by "format_string". "Source_date" can be a date, time, or timestamp data type.

Returns the datetime resulting from adding "integer_expression" days to "timestamp_expression".

Returns the datetime resulting from adding "integer_expression" months to "timestamp_expression".

Returns the datetime resulting from adding "integer_expression" years to "timestamp_expression".

Returns a number by subtracting "timestamp_expression" from today's date.

Returns the day of the week (between 1 and 7) for "timestamp_expression" where "integer_ expression" indicates which day of that week is day 1. To determine "integer_expression", choose the day of the week and count from Monday; for example, if you choose Wednesday, "integer_ expression" would be 3 because Wednesday is the third day from Monday.

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

Returns a positive or negative number representing the number of days between "timestamp_ expression1" and "timestamp_expression2". If "timestamp_expression1" < "timestamp_expression2", the result will be a negative number.

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

Returns a datetime that is the first day of the month represented by "timestamp_expression".

Returns a datetime that is the last day of the month represented by "timestamp_expression".

Returns a timestamp constructed from "integer_expression1" (the year), "integer_expression2" (the month), and "integer_expression3" (the day). The time portion defaults to 00:00:00.000 .

Returns a positive or negative number representing the number of months between "timestamp_ expression1" and "timestamp_expression2". If "timestamp_expression1" < "timestamp_expression2", the result will be a negative number.

Returns the week number (1-53) of the year for "timestamp_expression". According to the ISO 8601, week 1 of the year is the first week to contain a Thursday, which is equivalent to the first week containing January 4th. A week starts on a Monday (day 1) and ends on a Sunday (day 7).

Returns a positive or negative integer representing the number of years between "timestamp_ expression1" and "timestamp_expression2". If "timestamp_expression1" < "timestamp_expression2", a negative value is returned.

Returns a number representing the difference between "timestamp_expression1" and "timestamp_ expression2". This value has the form YYMMDD, where YY represents the number of years,MM represents the number of months, and DD represents the number of days.

Returns the absolute value of "numeric_expression". If "numeric_expression" is negative, a positive value is returned.

Returns the date value of the AsOfDate expression, if it is defined. Otherwise, AsOfDate returns the report execution date.

Returns the time value of the AsOfTime expression, if it is defined. Otherwise, AsOfTime returns the report execution time.

Returns the burst key.

Returns the distribution list of burst recipients.

Returns the smallest integer that is greater than or equal to "numeric_expression".

Returns the value of the current crosstab cell.

Returns the number of characters in "string_expression".

Returns the current column number.

Returns the date and time when the cube was created. "Dimension" specifies from which cube to retrieve the metadata. If the dimension source is an IBM® Cognos® PowerCube (.mdc), the function returns a blank string because the initial creation date of a PowerCube is not maintained.

Returns the current period for the cube. "Dimension" specifies from which cube to retrieve the metadata.

Returns the date time that data in the cube was last updated. "Dimension" specifies from which cube to retrieve the metadata.

Returns the name of the default measure for the cube. "Dimension" specifies from which cube to retrieve the metadata.

Returns the description of the cube. "Dimension" specifies from which cube to retrieve the metadata.

Returns "true" if the cube is optimized. "Dimension" specifies from which cube to retrieve the metadata.

Returns the name of the cube. "Dimension" specifies from which cube to retrieve the metadata.

Returns the date time that the cube schema was last updated. "Dimension" specifies from which cube to retrieve the metadata.

Returns the constant 'e' raised to the power of "numeric_expression". The constant 'e' is the base of the natural logarithm.

Returns an integer representing the value of "date_part_expression" in "datetime_expression". "Date_part_expression" could be the year, month, day, hour, minute, or second.

Returns the largest integer that is less than or equal to "numeric_expression".

Returns the run locale (deprecated).

Returns the current horizontal page count.

Returns the current horizontal page number.

Returns Boolean 1 (true) when the cell is in the scope of the data items and MUNs; otherwise, returns Boolean 0 (false).

Returns Boolean 1 (true) if the report is run with the accessibility features enabled. Use this function as a variable expression with a conditional block to make your reports accessible. For example, you can add a list or crosstab equivalent to a chart in reports that are run with accessibility features enabled.

Returns Boolean 1 (true) when the report will be distributed to the recipient; otherwise, returns Boolean 0 (false).

Returns Boolean 1 (true) if the current node is a crosstab column node member.

Returns Boolean 1 (true) if the current node is a crosstab row node member.

Returns Boolean 1 (true) if the current column is the first column.

Returns Boolean 1 (true) if the current node is an innermost crosstab column node member.

Returns Boolean 1 (true) if the current node is an innermost crosstab row node member.

Returns Boolean 1 (true) if the current column is the last column.

Returns Boolean 1 (true) if the current node is the last innermost crosstab column node member.

Returns Boolean 1 (true) if the current node is the last innermost crosstab row node member.

Returns Boolean 1 (true) if the current node is an outermost crosstab column node member.

Returns Boolean 1 (true) if the current node is an outermost crosstab row node member.

Returns Boolean 1 (true) if the page count is available for the current execution of the report; otherwise, returns Boolean 0 (false).

Returns the natural logarithm of "numeric_expression".

Returns the run locale.

Returns "string_expression" with all uppercase characters converted to lowercase. This function appears in the Bursted Sales Performance Report sample report in the GO Data Warehouse (query) package.

Adds "integer_expression" to "string_expression".

Returns an integer value representing the remainder (modulo) of "integer_expression1" / "integer_ expression2".

Returns the model path.

Returns the current system time.

Returns null if "string_expression1" equals "string_expression2" (case-insensitive), otherwise returns "string_expression1".

Returns the number of bytes in "string_expression".

Returns the current page count. This function works only when the report output is Adobe® PDF or Microsoft® Excel. If you save the report output, this function works for all formats.

Returns the current page name.

Returns the current page number.

Returns the parameter count of "parameterName".

Returns a string that is the parameter display value of "parameterName". This function appears in the Recruitment Report sample report in the GO Data Warehouse (analysis) package.

Returns the parameter name of "parameterName".

Returns all parameter names.

Returns the parameter value of "parameterName".

Returns the integer value representing the starting position of "string_expression1" in "string_ expression2". Returns 0 if "string_expression1" is not found.

Returns "numeric_expression1" raised to the power of "numeric_expression2".

Returns the author locale.

Returns the date when the report was created.

Returns the report execution date and time.

Returns the report description. This function works only when the report is run from IBM® Cognos® Connection.

Returns the report ID.

Returns the run locale.

Returns the report name. This function works only when the report is run from IBM® Cognos® Connection.

Returns the value of the run option variable identified by "optionName", such as attachmentEncoding, burst, cssURL, email, emailAsAttachment, emailAsURL, emailBody, emailSubject, emailTo, emailToAddress, history, metadataModel, outputEncapsulation, outputFormat, outputLocale, outputPageDefinition, outputPageOrientation, primaryWaitThreshold, print, printer, printerAddress, prompt, promptFormat, saveAs, saveOutput, secondaryWaitThreshold, verticalElements, or xslURL.

Returns the name of the output format, such as CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS, XML, or XLWA.

Returns the report path. This function works only when the report is run from IBM® Cognos® Connection.

Returns the product locale.

Returns the date when the report was last saved.

Returns "numeric_expression" rounded to the nearest value with "integer_expression" significant digits to the right of the decimal point. If "integer_expression" is negative, "numeric_expression" is rounded to the nearest absolute value with "integer_expression" significant digits to the left of the decimal point. Rounding takes place before data formatting is applied.

Returns the current row.

Returns the locale of the server that runs the report.

Returns the name of the web server where the run request originated from. The value may be empty if the request is executed from the scheduler.

Returns the square root of "numeric_expression". "Numeric_expression" must not be a negative value.

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 -1. The first character in "string_expression" is at position 1.

Returns the table of contents heading count for a specified heading level.

Returns the current system date.

Returns "string_expression" trimmed of any leading and trailing blanks or trimmed of the character specified by "match_character_expression". "Trim_what_expression" may be "leading", "trailing", or "both" (default). "Match_character_expression" can be an empty string to trim blanks or can specify a character to be trimmed.

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

Returns the URL encoded value of the input text.

Returns the base ten logarithm of "numeric_expression".

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the hyperbolic cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the hyperbolic sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the hyperbolic tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns a number representing the ascii code value of the leftmost character of "string_expression".

Returns the character that has the ASCII code value specified by "integer_expression". "Integer_ expression" should be between 0 and 255.

Searches "string_expression2" for the first occurrence of "string_expression1" and returns an integer. "Start_location" is the character position to start searching for "string_expression1" in "string_expression2". If "start_location" is not specified, is a negative number, or is zero, the search starts at the beginning of "string_expression2".

Returns the name of the current user.

Returns the length in bytes of "string_expression".

Returns the date resulting from the addition of "integer_expression" units (indicated by "datepart" (day, month, year)) to "date_expression".

Returns the number of "datepart" (day, month, year) units between "date_expression1" and "date_expression2".

Returns "datepart" from "date_expression", which can be a datetime, smalldatetime, date, or time value as an ASCII string. Note that "datepart" must be a keyword representing a datepart or its abbreviation recognized by Microsoft® SQL Server and must be enclosed in curly brackets.

Returns part of "date_expression" (for example, the month) as an integer. "date_expression" can be a datetime, smalldatetime, date, or time value. Note that "datepart" must be a keyword representing a datepart or its abbreviation recognized by Microsoft® SQL Server and must be enclosed in curly brackets.

Returns the day portion of "date_expression". Same as extract (day from date_expression).

Returns an integer value representing the difference between the values returned by the data sourcespecific soundex function for "string_expression1" and "string_expression2". The value returned ranges from 0 to 4, with 4 indicating the best match. Note that 4 does not mean that the strings are equal.

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

Returns the leftmost "integer_expression" characters of "string_expression".

Returns "string_expression" with leading spaces removed.

Returns the month portion of "date_expression". Same as extract (month from date_expression).

Returns an integer that represents the starting position of the first occurrence of "string_expression1" in the "string_expression2". Returns 0 if "string_expression1" is not found. The % wildcard character must precede and follow "string_expression1", except when searching for first or last characters.

Replaces all occurrences of "string_expression2" in "string_expression1" with "string_expression3".

Returns a string consisting of "string_expression" repeated "integer_expression" times.

Returns "string_expression" in reverse order.

Returns the rightmost "integer_expression" characters of "string_expression".

Returns "numeric_expression" rounded to the nearest value "integer_expression" places to the right of the decimal point. Rounding takes place before data formatting is applied.

Returns "string_expression" with trailing spaces removed.

Returns an indicator of the sign "numeric_expression": +1 if "numeric_expression" is positive, 0 if zero or -1 if negative.

Returns a four character string representing the sound of the words in "string_expression".

Returns a string consisting of "integer_expression" spaces.

Returns a string representation of "numeric_expression" where "integer_expression1" is the length of the string returned and "integer_expression2" is the number of decimal digits.

Returns a string where "integer_expression2" characters have been deleted from "string_expression1" beginning at "integer_expression1", and where "string_expression2" has been inserted into "string_ expression1" at its start. The first character in a string is at position 1.

Returns the year portion of "date_expression". Same as extract (year from date_expression).

Returns the natural logarithm of "numeric_expression".

Returns the base ten logarithm of "numeric_expression".

Returns the constant value of pi as a floating point value.

Generates a random number using "integer_expression" as the seed value.

Generates a random number using "integer_expression" as a seed value.

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the sine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the degrees where "numeric_expression" is an angle expressed in radians.

Returns the radians where "numeric_expression" is an angle expressed in degrees.

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the arctangent of the x and y coordinates specified by "numeric_expression1" and "numeric_ expression2", respectively, in radians. The arctangent is the angle whose tangent is "numeric_ expression1".

Returns the cotangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the running average by row (including the current row) for a set of values. The "<foroption>" 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.

Returns the running count by row (including the current row) for a set of values. 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.

Returns a running difference by row, calculated as the difference between the value for the current row and the preceding row, (including the current row) for a set of values. 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.

Returns the running maximum by row (including the current row) for a set of values. The "<foroption>" 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.

Returns the running minimum by row (including the current row) for a set of values. The "<foroption>" 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.

Returns a running total by row (including the current row) for a set of values. 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.

Returns the standard deviation of selected data items.

Computes the population standard deviation and returns the square root of the population variance.

Returns the total value of selected data items. Distinct is an alternative expression that is compatible with earlier versions of the product. This function appears in the Budget vs. Actual sample report in the GO Data Warehouse (analysis) package.

Returns the variance of selected data items.

Returns the population variance of a set of numbers after discarding the nulls in this set.

Returns a calculated value using the appropriate aggregation function based on the aggregation type of the expression.

Returns the average value of the selected data items.

Returns the number of selected data items excluding null values.

Returns the maximum value of selected data items.

Returns the median value of selected data items.

Returns the minimum value of selected data items.

Returns the percent of the total value for the selected data items.

Returns a value, on a scale from 0 to 100, that indicates the percent of a distribution that is equal to or below the selected data items.

Returns the rank of a value for the specified range. It returns integers to represent any range of ranks, such as 1 (highest) to 100 (lowest).

Returns the rank of a value, represented as integers from 1 (highest) to 4 (lowest), relative to a group of values.

Returns the rank value of the selected data items. The type of ranking returned (Olympic, dense, or serial) is data source dependent. The sort order is optional; DESC is assumed by default.

Returns a moving average by row for a specified set of values of over a specified number of rows. 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.

Returns a moving total by row for a specified set of values over a specified number of rows. 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.

Returns a number representing the ascii code value of the leftmost character of "string_expression".

Converts "integer_expression" to a character value. Char is usually used as the inverse of ascii where "integer_expression" must be between 0 and 255. If the resulting value is the first byte of a multibyte character, the character may be undefined.

Returns an integer that represents the starting position of "string_expression1" within "string_ expression2". If "string_expression1" is not found, zero is returned. If "string_expression1" contains wildcard characters, charindex treats them as literals.

Returns the length in bytes of "string_expression".

Returns the date resulting from adding "integer_expression" units indicated by datepart (day, month, year) to "date_expression". Note that "datepart" must be enclosed in curly brackets.

Returns the number of units indicated by "datepart" (day, month, year) between "date_expression1" and "date_expression2". Note that "datepart" must be enclosed in curly brackets.

Returns "datepart" of "date_expression" as an ASCII string. "Date_expression" can be a datetime, smalldatetime, date, or time value. Note that "datepart" must be enclosed in curly brackets.

Returns "datepart" of "date_expression" as an integer. "Date_expression" can be a datetime, smalldatetime, date, or time value. Note that the datepart argument must be enclosed in curly brackets.

Returns the day of the month (1-31) from "date_expression".

Returns an integer value representing the difference between the values returned by the data sourcespecific soundex function for "string_expression1" and "string_expression2". The value that is returned ranges from 0 to 4, with 4 indicating the best match. Note that 4 does not mean that the strings are equal.

Returns current system date and time.

Returns the leftmost "integer_expression" characters of "string_expression".

Returns "string_expression" with any leading spaces removed.

Returns the month number (1-12) from "date_expression".

Returns an integer representing the starting position of the first occurrence of "string_expression1" in "string_expression2" or returns 0 if "string_expression1" is not found. By default, patindex returns the offset in characters. The offset can be returned in bytes by setting the return type to bytes. The % wildcard character must precede and follow the pattern in "string_expression1", except when searching for first or last characters.

Returns a random float value between 0 and 1, using the optional "integer_expression" as a seed value.

Returns a string with the same datatype as "string_expression", containing the same expression repeated "integer_expression" times or as many times as will fit into a 225-byte space, whichever is less.

Returns the reverse of "string_expression".

Returns the rightmost "integer_expression" characters of "string_expression".

Returns "numeric_expression" rounded to the nearest value "integer_expression" places to the right of the decimal point. Rounding takes place before data formatting is applied.

Returns "string_expression" with trailing spaces removed.

Returns a four-character soundex code for character strings that are composed of a contiguous sequence of valid single- or double-byte Roman letter.

Returns a string with "integer_expression" single-byte spacing.

Returns a string representation of "numeric_expression". "Integer_expression1" is the length of the returned string and has a default setting of 10. "Integer_expression2" is the number of decimal digits and has a default setting of 0. Both are optional values.

Deletes "integer_expression2" characters from "string_expression1" starting at "integer_expression1", and inserts "string_expression2" into "string_expression1" at that position. To delete characters without inserting other characters, "string_expression2" should be null and not " ", which indicates a single space.

Returns the substring of "string_expression" that starts at position "integer_expression1". "Integer_ expression2" specifies the number of characters in the substring.

Returns a unichar expression with the value "integer_expression". If "integer_expression" is in the range 0xD800..0xDFFF, the operation is aborted. If the "integer_expression" is in the range 0..0xFFFF, a single Unicode value is returned. If "integer_expression" is in the range 0x10000..0x10FFFF, a surrogate pair is returned.

Returns 1 if the Unicode value at "integer_expression" is the high half of a surrogate pair (which should appear first in the pair). Otherwise, it returns 0. This function allows you to write explicit code for surrogate handling. Particularly, if a substring starts on a Unicode character where uhighsurr () is true, extract a substring of at least 2 Unicode values, as substr() does not extract just 1. Substr () does not extract half of a surrogate pair.

Returns 1 if the Unicode value at "integer_expression" is the low half of a surrogate pair (which should appear second in the pair). Otherwise, it returns 0. This function allows you to explicitly code around the adjustments performed by substr (), stuff (), and right (). Particularly, if a substring ends on a Unicode value where ulowsurr () is true, extract a substring of 1 less characters (or 1 more), since substr () does not extract a string that contains an unmatched surrogate pair.

Returns the Unicode scalar value for the first Unicode character in "string_expression". If the first character is not the high-order half of a surrogate pair, then the value is in the range 0..0xFFFF. If the first character is the high-order half of a surrogate pair, a second value must be a low-order half, and the return value is in the range 0x10000..0x10FFFF. If this function is called on a Unicode character expression containing an unmatched surrogate half, the operation is aborted.

Returns the year from date_expression.

Returns the natural logarithm of "numeric_expression".

Returns the base ten logarithm of "numeric_expression".

Returns the constant value of pi as a floating point value.

Returns an indicator denoting the sign of "numeric_expression": +1 if "numeric_expression" is positive, 0 if "numeric_expression" is zero, or -1 if "numeric_expression" is negative.

Returns the angle, in radians, whose tangent is "numeric_expression1" / "numeric_expression2".

Returns the cosine of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the cotangent of "numeric_expression", where "numeric_expression" is an angle expressed in radians.

Returns the arccosine of "numeric_expression" in radians. The arccosine is the angle whose cosine is "numeric_expression".

Returns the degrees where "numeric_expression" is an angle expressed in radians.

Returns the radians where "numeric_expression" is an angle expressed in degrees.

Returns the sine of "numeric_exp" where "numeric_expression" is an angle expressed in radians.

Returns the arcsine of "numeric_expression" in radians. The arcsine is the angle whose sine is "numeric_expression".

Returns the arctangent of "numeric_expression" in radians. The arctangent is the angle whose tangent is "numeric_expression".

Returns the tangent of "numeric_expression" where "numeric_expression" is an angle expressed in radians.

Returns the account string for the current user.

Returns the date or the datetime resulting from adding "integer_expression" months to "date_expression" or "datetime_expression".