Working with the Dynamic Template Designer > Apply Functions to Fields
   
Version 9.1.01
Apply Functions to Fields
Prerequisite: Design a Report Template with the Dynamic Template Designer.
A variety of Oracle built-in functions can be applied to report template fields. These functions are grouped into the following sub-categories.
Aggregation Functions
Comparison Functions
Numeric Functions
Character Functions
Date Functions
Character String Returning Numeric Values
Null Function
Decode Function
Note: Nested functions are only supported within a function sub-category. Nesting of functions across sub-categories is only supported for the NVL function.
To apply a function to a field, take the following steps.
1. In the Dynamic Template Designer, select a field in the Selected Fields list at the right of the Dynamic Template Designer window.
2. Click Functions at the bottom of the Dynamic Template Designer, to launch the Function Builder window.
A drop-down list of functions lets you select the function to be applied to the field.
3. Click Add to view the drop-down list of available functions.
4. Select a function from the list.
Certain functions, such as DECODE, require parameters, so a configuration window will display when parameters need to be configured.
5. Enter values or fields in the Function Details window. The following example illustrates the DECODE function.
Note that as you configure function parameters, the syntax will auto-complete at the top of the Function Details window so that you can view how it will be implemented.
Aggregation Functions
Function
Description
Examples
AVG
Calculates the average of the values from the selected column.
Display the average hours worked for a department.
COLLECT
Takes a database column and creates a nested table from the selected rows;
associates a list of data with a specific value, enabling aggregation of data into a collection.
Create a list of employees within a department.
COUNT
Lists the number of rows returned from the database for the selected column.
Determine the number of employee records returned from a database query.
MAX
Returns the maximum value of the selected column’s values.
Display the maximum hours worked.
MIN
Returns the minimum value of the selected column’s values.
Display the minimum hours worked.
STDDEV
Returns the standard deviation of the selected column’s values.
Apply standard deviation to values, for example, for forecasting.
SUM
Calculates the sum of the values from the selected column.
Display the total hours worked for a department.
Comparison Functions
Function
Description
Examples
GREATEST
From a list of expressions, Oracle determines which has the highest numeric precedence.
The following query results in BELIZE:
SELECT GREATEST('BELIZE','BELGIUM','BELARUS') "GREATEST"
FROM DUAL
LEAST
From a list of expressions, Oracle determines which has the lowest numeric precedence.
The following query results in BELARUS:
SELECT LEAST('BELIZE','BELGIUM','BELARUS') "LEAST"
FROM DUAL
Numeric Functions
Function
Description
Examples
CEIL
Returns the smallest integer value that is greater than or equal to the value (n).
13551.8 becomes 13552
FLOOR
Returns the largest integer value that is less than or equal to the value (n).
13551.8 becomes 13551
ROUND
Rounds a number to the specified number of decimal places:
Negative arguments indicate rounding to the left of the decimal point.
Positive arguments round to the right of the decimal point.
25.193 with an argument of 1 will round to 25.2
SIGN
Returns a number that represents the sign of a value (n).
-1 if n < 0; 0 if n = 0; 1 if n >0
TRUNC
Truncates a value to the specified number of decimal places.
25.193 with an argument of 1 will truncate to 25.1
Character Functions
Function
Description
Examples
aptStringConcat
Creates a comma-separated list of strings.
Note that the Oracle function, aptStringConcat with DISTINCT or UNIQUE, cannot be used to concatenate values in a method, even though the method will validate and save. When that method is used in a report template, it will fail. Use collectString in a method to get this functionality.
aptStringConcat (array_name) can be used to create a comma-separated list of arrays within an array family.
collectString
Use this function in the Method Designer to concatenate distinct values. See the APTARE StorageConsole Database Programmer’s Reference Guide for additional examples.
 
SELECT rtd.collectString(SET(CAST(COLLECT(cast(client_id as varchar2(10))) AS stringListType)),', ') name from apt_v_job;
CONCAT
Concatenates multiple character strings.
'Storage' + 'HQ' = 'StorageHQ'
INITCAP
Displays a string with the first letter of each word in uppercase.
'daily backup schedule' becomes 'Daily Backup Schedule'
LOWER
Displays a string with all letters of each word in lowercase.
'DAILY BACKUP SCHEDULE' becomes 'daily backup schedule'
LPAD
Pads the left of a string with the specified characters, up to the total string length.
Preface an alert message with a string of asterisks; for example, ****Warning
LTRIM
Trims the specified character set from the left of a string. This is useful for removing redundant words, characters, or labels. When no string is supplied, it defaults to a single blank.
'RAID5' and 'RAID6' would be trimmed to simply '5' and '6'
REPLACE
Substitutes one character string for another character string; in addition, you can remove character strings.
Substituting 'HDS' for 'Hitachi' changes 'Hitachi array' to 'HDS array'
RPAD
Pads the right of a string with the specified characters, up to the total string length.
Postfix text with a string of asterisks; for example, Error***
RTRIM
Trims the specified character set from the right of a string. This is useful for removing redundant words, characters, or labels. When no string is supplied, it defaults to a single blank.
'RAID 5' and 'RAID 6' could be trimmed to simply 'RAID'
SUBSTR
Extracts a portion of a character string.
Use this function to list the first three characters of policy names.
TO_DATE
Converts a character string to a date.
to_date('10/09/13', 'DD/mm/YY')
to_date('10-sep-13', 'DD-MON-YYYY')
TRANSLATE
Makes several single-character substitutions; one-to-one substitution in one operation.
Use this function to replace all spaces with an underscore character.
'System Reference Guide' would become 'System_Reference_Guide'
TRIM
Removes leading or trailing characters (or both) from a character string
Use this function to remove leading zeroes from object identifiers (00049 --> 49)
UPPER
Changes all characters in a string to uppercase
'hds' becomes 'HDS'; 'Aptare' becomes 'APTARE'
Date Functions
Function
Description
Examples
ADD_MONTHS
Takes the date and adds n months. The result has the same day as input date.
If the input date is the last day of the month or has fewer days than the resulting month, then the returning date will be the last day of the month.
June 22nd + 3 = September 22nd
ROUND
Rounds the date to the unit format that you specify: Minute, Hour, Day, Month, Quarter, Year.
If you specify Month, the function rounds up on the 16th day of the month
SYSDATE
Returns the current date and time for the system on which the database resides.
06-13-2012 09:34:41 (displayed in the format: MM-DD-YYYY HH:MM:SS)
TO_CHAR
Converts a date or interval value to a character data type in the specified format: Date, Timestamp.
finish_Date, 'DD-MON-YYYY HH24:'
startDate, 'YYYY-MM-DD HH24:MI:SS'
TRUNC
Truncates the time portion of a day to the specified format unit: Minute, Hour, Day, Month, Quarter, Year.
In Oracle, date values contain a year, month, and day and also the hour, minute, and second.
10-July-2012 09:34:41 becomes 10-July-2012
Character String Returning Numeric Values
Function
Description
Examples
INSTR
Searches a string for a sub-string and returns an integer that is the position in the string of the first character of the sub-string.
The INTSR and LENGTH functions are mutually exclusive.
Search for 'error' in the string: 'A big system error caused the problem.' This results in 14.
LENGTH
Returns the number of characters of a character string.
The INTSR and LENGTH functions are mutually exclusive.
Length of 'array type' would be 10
Null Function
Function
Description
Examples
NVL
Checks for a null and substitutes another value.
Use this function to display N/A instead of a blank value
Decode Function
Function
Description
Examples
DECODE
Checks for a value and if there is a match, replaces it with another constant or database field value.
Use this function to display Success if the status is 0, Warning if the status is 1, and Failed if the status is 0.
DECODE(summary_status, 0, 'Success', 1, 'Warning', 2, 'Failed')
Unique Function
Function
Description
Examples
UNIQUE
Enables aggregation on a unique field. This function can be applied to only one field in a report template.
COUNT(UNIQUE(client_id)), MAX(UNIQUE(server_id)), job_type FROM apt_job GROUP BY job_type