Working with the Dynamic Template Designer > Dynamic Template Function Configurations
  
Version 10.2.01P10
Dynamic Template Function Configurations
Prerequisite: Add Fields and Methods to a Dynamic Template.
A variety of Oracle built-in functions can be applied to report template fields. These functions are grouped into the following sub-categories.
Examples of Dynamic Templates Containing Functions
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. A configuration window will display when parameters need to be configured.
5. Enter values or fields in the Function Details window, as shown in the following DECODE example.
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.
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 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
Examples of Dynamic Templates Containing Functions
The best way to learn how to define functions in a report template is to learn by example.
The following list includes a sampling of the out-of-the-box reports that are shipped with the product, along with a few examples of the relevant information to help you isolate an example of the functionality that you are trying to implement.
To help you identify a field in the template that contains this functionality, the following convention is used in the list of report templates.
Product:Alias combines the values listed in the template. In the following example, the selected Product is All, while the selected Alias is F.
Some templates are vendor product-specific, in which case you may have a Product:Alias such as EMC Avamar:A
 
Report Template
Report Type
Product: Alias
Caption
Field Type
Functions
Backup Manager Reports
Backup Executive Summary
Table
All: F
Failed
Column
DECODE, SUM
 
 
All: G
Failed Count
Hidden
DECODE, UNIQUE, COUNT
 
 
All: U, All: V
Start Date
Hidden
MAX
 
 
All: C
Total
 
COUNT
 
 
All: D
Total
 
UNIQUE, COUNT
Job Status Summary
Bar
All: T
Failure
Bar
DECODE, SUM
Job Summary
Table
Veritas NetBackup: P1
Client
Column
NVL
 
 
Veritas NetBackup: O1
Host Id
Hidden
NVL
 
 
CommVault Simpana: X1
Reason
Column
DECODE
 
 
CommVault Simpana: R
Reason
Hidden
NVL
 
 
Tivoli Storage Manager: C
Server
Column
UPPER
 
 
HP Data Protector: V
Status
Column
NVL, DECODE
Job Volume Summary
Bar
All: D
# of Files Backed Up or Restored
Line
SUM
 
 
All: B
# of Jobs
Hidden
COUNT
 
 
All: C
Backup/Restore Volume
Bar
SUM
Job Duration
Bar
All: F
Job Duration
Bar
SUM
Error Log Summary by Server
Table
All: E
Error Occurrences
Column
COUNT
 
 
All: F
Last Error Date
Column
MAX
Error Log Summary by Client
Table
All: F
Error Occurrences
Column
COUNT
 
 
All: G
Last Error Date
Column
MAX
Error Log Summary by Policy
Table
All: E
Error Occurrences
Column
COUNT
 
 
All: F
Last Error Date
Column
MAX
Consecutive Errors By Client
Table
All: N
# Consecutive Errors
Column
COUNT
 
 
All: I
First Error Date
Column
MIN
Largest Backup Volume
Pie
All: C
Job Size
Sector
SUM
Data Domain Reports
Data Domain Snapshot History
Table
EMC Data Domain: K
Days Left To Expire
hidden
TRUNC
 
 
EMC Data Domain: I
Expires After (Days)
Column
SIGN, DECODE, TO_CHAR, NVL
Capacity Manager Reports
Array Executive Summary
Table
All: A1
Thin
Column
SUM, NVL
LUN Utilization Summary
Table
All: F
Hitachi Data Systems: C
Symmetrix: D
NetApp: E
IBM: E
IBM XIV: E
IBM SVC: E
HP EVA: C
Device Nbr
hidden
TO_CHAR, LPAD
NetApp Cluster-Mode Volume Summary
Table
NetApp Cluster-Mode: N
Type
Column
UPPER
EMC Isilon Nodes
Table
EMC Isilon: Y
# Int Up Interfaces
Column
DECODE, TO_NUMBER, SUM
 
 
EMC Isilon: T
External IP Address
Column
DECODE, aptStringConcat
EMC Isilon File System Performance by Protocol
Line
EMC Isilon: H
Max Bytes Out
Line
MAX
 
 
EMC Isilon: A
# Active Clients
Line
AVG