ANSI SQL Functions

Interactive Reporting supports ANSI SQL functions, and windowing clauses for the data. Windowing or partions refers to a fixed window or "sub-range" over which the aggregate function is computed for each row in the partition.

The windowing clauses include:

Partition by—Data to be analyzed in subsets.
Order by—Sort parameter in ascending or descending order.
Rows—Defines the rows over which the aggregate functions is computed for each row in the partition.

For example, if you want to compute the three-month moving average sales for each store in the sales_fact table, then partition by StoreID, order by Month, and perform the computation over the current row and the two preceding rows as shown below:

SELECT StoreID, Sales_Month, ProductID, Sales, AVG(Sales) OVER (PARTITION BY StoreID ORDER BY Sales_Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

To apply an ANSI SQL function:

Drag an topic from the Elements Table Catalog to the Content pane.
Drag a topic to the Request line.
Select the topic item on the Request line and on the shortcut menu, select Add Computed Item.
The Modify Item dialog box is displayed.

Select Functions.

The Functions dialog box is displayed.

Note:

If a function does not support windowing clauses, the Function dialog box shows only a Parameters tab. If windowing clauses are supported by the currently selected function, an Advanced tab is available for you to build windowing clauses.

Select a function from the Function Categories list.
A list of supported ANSI functions is shown in the Function list. Database specific ANSI functions are also shown.
In the Partition by field, select a topic item by which to partition data.
To review and select from a list of topic items, click Reference and select an item from the Reference dialog box.
In the Ordered by field, select the topic item by which to sort.
To review and select from a list of topic items, click Reference and select an item from the Reference dialog box.
Select to sort in Ascending or Descending order.
In the Rows fields, specify the number of rows over which the aggregate function is computed for each row in the partition.
To select all values, leave the default value as “Unbounded”.
Select the partition parameters for the Rows values. That is, select the capture area for the partition. Valid options are
- Preceding
- Current Row
To select values between the rows selected in the Row field, specify which rows and indicate the partition parameter. Valid options are:
- Current Row
- Following
Select OK.

Table 142. ANSI SQL Functions

Name	Description	Return Type	Parameters
CORR	Returns the Pearson product moment correlation coefficient of its argument fall all non-null data point pairs.	Real	numeric column expressions x and y
COVAR_POP	Returns the population covariance of its arguments for all non-null data point pairs.	Real	numeric column expressions x and y
COVAR_SAMP	Returns the sample covariance of its arguments for all non-null pairs.	Real	numeric column expression x and y
GROUPING	Returns a value that indicates whether a specified column in the result row was exclude	Integer	an item of a GROUP BY clause
KURTOSIS	Returns the distribution kurtosis of the column expression.	Real	numeric column expression
REGR_AVGX	Returns the mean of the independent_column_expression for all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
REGR_AVGY	Returns the mean of the dependent_column_expression for all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
REGR_COUNT	Returns the count of all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
REGR_INTERCEPT	Returns the intercept of the univariate linear regression line through all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
REGR_R2	Returns the coefficient of determination for all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
REGR_SLOPE	Returns the slope of the univariate linear regression line through all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
REGR_SXX	Returns the sum of the squares of the independent_column_expression for all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
REGR_SXY	Returns the sum of the products of the independent_column_expression and the dependent_column_expression for all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
REGR_SYY	Returns the sum of the squares of the dependent_column_expression for all non-null data pairs of the dependent and independent variable arguments.	Real	dependent and independent column expressions
SKEW	Returns the skewness of the distribution of value_expression.	Real	numeric column expression
STDDEV_SAMP	Returns the sample standard deviation for the non-null data points in value_expression.	Real	numeric column expression
VAR_POP	Returns the population variance for the data points in value_expression.	Real	numeric column expression
VAR_SAMP	Returns the sample variance for the data points in value_expression.	Real	numeric column expression
WIDTH_BUCKET	Returns the number of the partition to which value_expression is assigned.	Integer	value expression, lower bound, upper bound, partition count
PERCENT_RANK	Returns the relative rank of rows for a value_expression.	Real
ROW_NUMBER	Returns the sequential row number, where the first row is number one, of the row within its window partition according to the window ordering of the window.	Integer
POSITION	Returns the position in string_expression_2 where string_expression_1 starts.	Integer	String expression 1, string expression 2
TRANSLATE	Converts a character string or character string expression from one server character set to another server character set.	String	String expression, conversion
CHARACTER_LENGTH	Returns the length of a string either in logical characters or in bytes.	Integer	String expression
OCTET_LENGTH	Returns the length of string_expression in octets.	Integer	String expression
CURRENT_DATE	Returns the current system date.	Date
CURRENT_TIME	Returns the current system time.	Time
CURRENT_TIMESTAMP	Returns the current system timestamp.	Timestamp

An Oracle database loads these functions:

Function
Sum	Abs	Sin	Rpad
Min	Floor	Cos	Substr
Max	Power	Tan	To_char
Avg	Round	Decode	Last_day
Count	Sign	Nvl	Sysdate
Add Months	Sqrt	Ceil	Months_between
Concat	Lower	Stddev	Next_day
Length	Upper	Variance
Trunc	Ascii	Initcap
User	Exp	Lpad

A DB2 database loads these functions:

Function
Sum	Lower	Coalesce	Microsecond	Decimal	Concat	Right
Min	Upper	Current Date	Minute	Digits	Hex	Soundex
Max	Ascii	Current Time	Month	Integer	Insert	Substr
Avg	Exp	Date	Monthname	Log10	Lcase	Ucase
Count	LTrim	Day	Quarter	Mod	Left
Abs	RTrim	Dayname	Second	Rand	Length
Floor	Sin	DayOfWeek	Time	Smallint	Locate
Power	Cos	Dayofyear	Week	Trunc	Posstr
Round	Tan	Days	Year	Char	Repeat
Sqrt	Nullif	Hour	Ceil	Chr	Replace

A SQL: (MSSQL) database loads these functions:

Function
Sum	Sqrt	Grouping	Right
Min	Lower	Width_Bucket	Substring
Max	Upper	Row_Number	Str
Avg	Ascii	Current_Timestamp	User_name
Count	Exp	Rank	Suser_name
Abs	LTrim	Length	Dateadd
Floor	RTrim	Isnull	Datediff
Power	Sin	Ceiling	Datename
Round	Cos	Rand	Datepart
Sign	Tan	Reverse	Getdate