1. Go to the Administrator page.
2. Click Data Objects, or right-click Data Objects and select the Create menu item.

   The Data Objects dialog appears.

3. Type a Name and optionally edit the Display Name.

   The Name is case insensitive, must begin with a letter, and may consist only of letters, numbers, and the underscore character. It may have up to 128 characters. It cannot be changed after the data object is created.

   The Display Name is case insensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 128 characters. It can be changed at any time.

4. Select Simple Data Object as the Type.
5. Select STREAM or RELATION as the Continuous Query Type.
6. If you selected STREAM, check or uncheck the Archived box.

   A relation is always archived.

7. If you selected STREAM and checked Archived, supply the replay information:

   • Replay Unit — Select Rows, Nanoseconds, Microseconds, Milliseconds, Seconds, Minutes, Days, Weeks, Months, or Years.

   • Replay Amount — Specify the number of the units. For example, if the units are Rows and you type 100, then the last 100 rows are archived.

   The replay information specifies the part of the immediate past that is of interest for continuous queries, realtime KPIs, and alerts.

8. Optionally, type a Category for the data object.

   This value can label and group similar data objects, such as BPM data objects.

9. Specify maximum numbers of columns with different data types:

   • Number of String Columns — The default is 25.

   • Number of Long String Columns — The default is 25.

   • Number of Integer Columns — The default is 25.

   • Number of Float Columns — The default is 25.

   • Number of Decimal Columns — The default is 25.

   • Number of Date/Time Columns — The default is 10.

10. Optionally, type a Description of the data object.
11. Click Create.

    The data object opens in a new tab.

1. Go to the Administrator page.
2. Click Data Objects and select the Create menu item.

   The Data Objects dialog appears.

3. Type a Name and optionally edit the Display Name.

   The Name is case insensitive, must begin with a letter, and may consist only of letters, numbers, and the underscore character. It may have up to 128 characters. It cannot be changed after the data object is created.

   The Display Name is case insensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 128 characters. It can be changed at any time.

4. Select Derived Data Object as the Type.
5. Select the Parent data object from which to derive this data object.

   A derived data object must have the same settings for STREAM or RELATION and Archived as its parent.

6. For an Archived STREAM, supply the replay information:

   • Replay Unit — Select Rows, Nanoseconds, Microseconds, Milliseconds, Seconds, Minutes, Days, Weeks, Months, or Years.

   • Replay Amount — Specify the number of the units. For example, if the units are Rows and you type 100, then the last 100 rows are archived.

   The replay information specifies the part of the immediate past that is of interest for continuous queries, realtime KPIs, and alerts.

   A derived data object must have replay information less than or equal to that of the parent data object.

7. Optionally, type a Category for the data object.

   This value can label and group similar data objects, such as BPM data objects.

8. Optionally, type a Description of the data object.
9. Click Create.

   The data object opens in a new tab.

   Note:

   Derived data objects retain the inherited calculation fields and hierarchies of the parent data object, but they cannot be edited.

An external data object is a connection to a table in an external database. You cannot change its data using Oracle BAM.

1. Configure a data source in Oracle WebLogic Server.

   See Configuring JDBC Data Sources in Administering JDBC Data Sources for Oracle WebLogic Server.

2. Go to the Administrator page.
3. Click Data Objects and click the Create icon, or right-click Data Objects and select the Create menu item.

   The Data Objects dialog appears.

4. Type a Name and optionally edit the Display Name.

   The Name is case insensitive, must begin with a letter, and may consist only of letters, numbers, and the underscore character. It may have up to 128 characters. It cannot be changed after the data object is created.

   The Display Name is case insensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 128 characters. It can be changed at any time.

5. Select External Data Object as the Type.
6. Select a Schema Type. You can choose to create an External Fact DO or an External Dimension DO.

   An external fact data object refers to a table from an external database or different schema, used as a fact data object in BAM.

   An external dimension data object refers to a table from an external database or different schema, used as Dimension data object in BAM. This data object generates a simple data object as a dimension table.

   The External Dimension Data Object data is copied to the generated Simple Data Object. You can alter the number of rows that populate a Simple Data Object from an External Data Object by using Oracle Enterprise Manager Fusion Middleware Control.

7. Optionally, type a Category for the data object.

   This value can label and group similar data objects, such as BPM data objects.

8. Optionally, type a Description of the data object.
9. Click Create.

   The data object opens in a new tab.

10. Select the Tables subtab.

    This subtab applies only to external data objects.

11. Select the data source you configured in step 1 from the Select a Data Source drop-down list.
12. Optionally type a text string in the Table Name Filter field to filter the selections in the Select a Table drop-down list. You can use the percent (%) character as a wildcard.

    For example, type %ACTIVITY% to restrict the Select a Table list to data sources with ACTIVITY in their names.

13. Select the desired table from the Select a Table drop-down list.

    The columns in the selected table appear.

14. For each column, check or uncheck the Selected Columns box.

    Only checked columns are visible to BAM.

15. For each column, optionally edit the Column Name.
16. For each column, select the Column Type: ATTRIBUTE (the default), DIMENSION, or MEASURE.
17. Click Save.

1. Go to the Administrator page.
2. Click Data Objects and click the Create icon, or right-click Data Objects and select the Create menu item.

   The Data Objects dialog appears.

3. Type a Name and optionally edit the Display Name.

   The Name is case insensitive, must begin with a letter, and may consist only of letters, numbers, and the underscore character. It may have up to 128 characters. It cannot be changed after the data object is created.

   The Display Name is case insensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 128 characters. It can be changed at any time.

4. Select Logical Data Object as the Type.
5. Optionally, type a Category for the data object.

   This value can label and group similar data objects, such as BPM data objects.

6. Optionally, type a Description of the data object.
7. Click Create.

   The data object opens in a new tab.

8. Select the Columns subtab.
9. Click Add Data Object Mapping.

   A data object mapping joins two data objects.

10. Select the following:

    • Primary Data Object — Select the primary data object name. This will constitute the Fact Data Object within the Logical Data Object.

    • Primary Column — Select the internal name of the primary column the in primary data object used in the join.

    • Foreign Data Object — Select the foreign data object name.

    • Foreign Column — Select the internal name of the primary column the in foreign data object used in the join.

    Note:

    The Primary Data Object and Foreign Data Object selection list only lists Simple and Derived Data Objects. Logical and External Data Objects are not listed.
    Check the Standalone Mode box if you want the Logical Data Object to be composed of only one data object. In this case, only the Primary Data Object field remains.
11. Optionally, check the Left Outer Join box. If unchecked, the Logical Data Object will have the Inner Join join type.
12. To join more data objects, repeat steps 9 and 10.
13. Click Save.

The data types include:

• VARCHAR (or String) columns contain a sequence of text characters. The maximum length that Oracle BAM 14c supports is 2000 characters.

  Note:

  There are two set of string length in BAM - STRING and LONGSTRING. The design length range is as follows:

  • STRING – 0 < 256

  • LONGSTRING – 257 < 2000

  You cannot switch length from one range STRING to another range LONGSTRING. You may change the length of a column within its range only.

• INT columns contain numbers from -2^63 ~ 2^63 - 1.

  Boolean columns are INT columns with a size of 1 and values of 0 for false and 1 for true.

• FLOAT columns are double-precision floating point numbers.

  Oracle BAM Float truncates numeric data that has very high precision. If you do not want to see loss of precision, use the Oracle BAM Decimal type (NUMBER in an Oracle database) with the scale you want.

  A FLOAT in Oracle BAM is stored as an Oracle database data type BINARY_FLOAT. The precision is not guaranteed to be exactly the same as entered by the user, and when data flows from one system to another, the data types get mapped to each other (as for example SQL float to Java float). Oracle BAM does not do any manipulation with the data sent by the database. It is the Oracle Java Database Connectivity (JDBC) driver that loses the last few digits.

• DECIMAL columns include decimal points with a scale number defined. The number is stored as a string, which uses a character for each digit in the value.

  The Oracle BAM Decimal data type is stored as a NUMBER (38, X) in the Oracle database. The first argument, 38, is the precision, and this is hard-coded. The second argument, X, is the scale, and you can adjust this value. The scale value cannot be greater than 38.

• DATETIME columns are timestamps generated to milliseconds.

  A DATETIME field is stored as an Oracle database data type TIMESTAMP(6). Depending on how the DATETIME field is populated, Oracle BAM may fill in the time stamp value for you. For instance, in Oracle BAM you cannot specify the value for DATETIME when adding a row, but if the value for DATETIME is specified in a BAMCommand import file, the specified value is added as the value of the DATETIME field instead of the current time.

  The format of DATETIME values in BAM is as follows, with 24-hour time:

  yyyy-MM-dd'T'HH:mm:ss.SSSZ
  

  For example, the following DATETIME value represents November 12, 2013 at 12:15:52 am, plus 103 milliseconds, in the GMT -8 (Pacific) time zone:

  2013-11-12T00:15:52.103-08:00

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Columns subtab.
5. Click Add Column.

   A new column appears as a row in the table, named column_1.

6. Click the table cell under Column Name and edit the name.

   The Column Name is case insensitive, must begin with a letter, and may consist only of letters, numbers, and the underscore character. It may have up to 30 characters. It cannot be changed after the column is created.

7. Optionally click the table cell under Display Name and edit the name.

   The Display Name is case insensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 30 characters. It can be changed at any time.

8. Select the Column Type: ATTRIBUTE (the default), DIMENSION, or MEASURE.
9. Select the Data Type: INT (the default), VARCHAR, DECIMAL DATETIME, or FLOAT.
10. If the selected Data Type makes the Size editable, optionally type a Size in bytes.
11. Check Nullable to allow the column to have null values. The default is checked.
12. Check Hidden to hide the column from Designer users. The default is unchecked.
13. Check Unique to require each column value to be unique. The default is unchecked.

    This setting is not available for a new column in a derived data object.

14. Optionally, click the Default Value cell to make it editable. If the column does not have any inherent value, the default value you specify here will be used.
15. Optionally type a Comment.
16. Repeat steps 5 through 14 to add other columns.
17. Click Save.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Columns subtab.
5. Click Add Logical Column.

   A dialog with Available Columns and Selected Columns lists appears.

6. To add a column to the Selected Columns list, select the column and click the single right arrow.
7. To add all columns to the Selected Columns list, select the column and click the double right arrow.
8. To remove columns from the Selected Columns list, use the single and double left arrows.
9. When the Selected Columns list is final, click Finish.

   The selected columns appear as rows in the table.

10. Optionally click the table cell under Logical Data Object Column Name and edit the name.

    The Logical Data Object Column Name is case insensitive, must begin with a letter, and may consist only of letters, numbers, and the underscore character. It may have up to 30 characters. It cannot be changed after the column is created.

11. Optionally click the table cell under Logical Data Object Column Display Name and edit the name.

    The Logical Data Object Column Display Name is case insensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 30 characters. It can be changed at any time.

12. Select the Column Type: ATTRIBUTE (the default), DIMENSION, or MEASURE.
13. Repeat steps 5 through 12 to add other columns.
14. Click Save.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Indexes subtab.
5. Click Add Index.

   The Edit Index dialog appears.

6. Type a Name for the index. An INDEX prefix is autofilled.

   The Name is case insensitive, must begin with a letter, and may consist only of letters, numbers, and the underscore character. It may have up to 30 characters. It cannot be changed after the data object is created.

7. To add a column to the Selected Columns list, select the column in the Available Columns list and click the single right arrow.
8. To add all columns to the Selected Columns list, select the columns and click the double right arrow.
9. To remove columns from the Selected Columns list, use the single and double left arrows.
10. To change the position of a column in the Selected Columns list, select it and use the Move to Top, Move Up One, Move Down One, or Move to Bottom arrows.

    The position in the Selected Columns list corresponds to the position in the index.

11. When the Selected Columns list is final, click Finish.

    The Edit Index dialog closes, and the index appears as a row in the table. You can edit or remove an index by clicking the icons in the Action column.

12. Click Save.

In Oracle BAM, you can add hierarchies to data objects to provide drill paths for business views. When a Designer user selects a hierarchy for drill-down in a view, the Viewer user can drill down and up the hierarchy of data, displaying data at different levels of detail.

You can create and edit multiple, independent hierarchies in any type of data object. There are two hierarchy types: non-datetime and datetime.

Activity Running Time (millisecs)/60000

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Calculated Fields subtab.
5. Click the Add icon.

   The Expression Builder dialog appears.

6. Type a Field Name and a Display Name.

   The Field Name is case insensitive, must begin with a letter, and may consist only of letters, numbers, and the underscore character. It may have up to 128 characters. It cannot be changed after the field is created.

   The Display Name is case insensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 128 characters. It can be changed at any time.

7. Choose a Column Type. Oracle BAM provides the following Column Type options for calculated fields:

   • Attribute

   • Measure

   • Dimension

   For more information on data object columns and their uses, see Data Object Columns. The default selected value is Measure.

8. To help you create an Expression, BAM provides these components:

   • Functions are of these types:

     • Calculate Functions include references to dates and times, comparisons, and text operations such as concatenation.

     • Control Functions include programming decision operations such as IF, THEN, and ELSE.

     • Aggregation Functions include operations on groups of numbers, such as average, sum, count, maximum, or minimum.

   • Variables reference column data.

   • Operators perform mathematical operations such as addition and subtraction.

   Click the Insert into Expression button to add the function, variable, or operator you select to the expression. See Operators and Functions for Calculated Fields for more information.

   Note:

   If you select a Data Object Parameter, it is added into the expression box within brackets if the display name for the parameter contains certain special characters. This is so the special characters aren’t treated as functions.
9. When the calculated field expression is final, click OK.

   A basic syntax check is performed and any errors are displayed.

   If the calculation passes the syntax check, the Expression Builder dialog closes and the new field appears as a row in the table. You can edit or remove a calculated field by clicking the icons in the Edit and Delete columns.

10. Click Save.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. In the General tab, view the maximum numbers of columns with different data types:

   • Number of String Columns — The default is 25.

   • Number of Integer Columns — The default is 25.

   • Number of Float Columns — The default is 25.

   • Number of Decimal Columns — The default is 25.

   • Number of Date/Time Columns — The default is 10.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Row Security subtab.
5. Click the Add Default (plus with an arrow) icon to create a default filter for all roles with no filters assigned.

   BAMDefaultFilter appears in the Name column of the row security table. You can add a filter as if it were a role.

6. Click the Add icon to add roles to the row security table.

   The Add Application Roles dialog appears.

   See Managing Oracle BAM Users for information about how to add users to roles.

7. Click the Search button to display roles in the Available Members list according to the List selection.

   You can filter the search by typing a partial name in the Name text box.

8. Select roles and click the Move Selected Items arrow icon to move them to the Selected Members list. To move all members, click the Move All Items double arrow icon.

   To remove roles, click the Remove Selected Items or Remove All Items icon.

9. When the Selected Members list is final, click OK.
10. Click the Edit icon for the Name to which to apply the security filter.

    The Edit Security Filter dialog appears. All of the functionality available in query filters is provided. See Filtering Data for more information.

    To set a default row security filter for all users with no row security filters set, edit BAMDefaultFilter, which has no initial filter set.

11. When the filter is final, click OK.

    The Edit Security Filter dialog closes, and the filter appears in the row in the table for the Name you selected. You can edit or remove a filter by clicking the icons in the Action column.

12. Click Save.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Data subtab.
5. Click Add Row.

   A row is added to the table.

6. Click on the row and edit the values in the cells.

   For a DATETIME column, you can type a value or click the Select Date and Time icon. The Select Date and Time dialog uses the 12-hour format as 1-12 AM/PM. Its 12:00 AM is 0:00 and 12:00 PM is 12:00 of 24 hours format.

7. Click Save.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Data subtab.
5. Select a row in the table.
6. Click Edit Row.

   The row becomes editable.

7. Edit the values in the cells.

   For a DATETIME column, you can type a value or click the Select Date and Time icon to choose a date and time.

8. Click Save.

   Note:

   Editing Data Object rows always requires a Save for your changes to take effect. The fix changes warning may not always appear due to UI changes applied to BAM. This is a known issue for this release.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Data subtab.
5. Click Purge.

   The Purge Data Object content dialog appears.

6. Select Purge All or Purge Before.
7. If you selected Purge Before, type a date and time or click the Select Date and Time icon to choose a date and time.
8. Click Purge.

   A warning message appears, asking you to confirm the purge.

9. Click Yes.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Data subtab.
5. If you don't want to export all the data, select the rows you want to export.
6. Click one of the export options:

   • Export All Rows to CSV exports all the data to a .csv file.

     Note:

     You can customize the number for rows to be exported using the Preferences window. For more information, see Modifying Data Tab Preferences.

   • Export Selected Rows to CSV exports the selected rows to a .csv file.

   The file is created. If your computer has software installed that can open .csv files, the file is opened. If you see that there are errors with comma-separated values in the output, ensure that the cells are formatted as text cells so that the software doesn’t apply its own numeric formatting to it.

7. Save the file.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Retention subtab.
5. Type the number of days to retain data in the Retention Days field.

   The default value of zero retains data indefinitely.

   Note: You can now change the data retention schedule in Oracle Enterprise Manager to any time you need using MBean properties. For more information, see Scheduling Automatic Data Purges for a Data Object.

6. Click Save.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the Filter subtab.
5. Add filters. All of the functionality available in query filters is provided. See Filtering Data for more information.
6. Click Save.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the General subtab.
5. Check the Slow Changing Dimension box.
6. Click Save.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the subtab of interest.
5. Make any desired changes and click Save.
6. Click the X on the data object tab to close it.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects in the current project are displayed in a list.

3. Click the data object name, or right-click the data object and select the Edit menu item.

   The data object opens in a new tab.

4. Select the General subtab.
5. Edit the Display Name.

   The Display Name is case insensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 128 characters. It can be changed at any time.

   To move a data object into a folder, include the folder name. See Using Data Object Folders.

6. Click Save.
7. Click the X on the data object tab to close it.

1. Go to the Administrator page.
2. In the left navigation pane, click the arrow to the left of Data Objects.

   All data objects are displayed in a list.

3. Click the data object icon and click the Delete icon, or right-click the data object name and select the Delete menu item.

   A dialog asks you to confirm the data object deletion.

4. Click OK.

   The data object disappears from the list.

Table 14-1 describes the operators you can use to build calculated columns.

if ((CallbackClientTime == NULL) ||
(ReceiveInputTime == NULL)) then (-1) else
(CallbackClientTime-ReceiveInputTime)

Field names containing any special characters, such as the operators listed in Table 14-1, double quotation marks, or spaces, must be surrounded with curly braces {}. If field names contain only numbers, letters and underscores and begin with a letter or underscore they do not need curly braces. For example, if the field name is Sales, the correct way to enter this in a calculation is Sales. However, if the field name is Sales+Costs, the correct way to enter this in a calculation is {Sales+Costs}.

Double quotes must be escaped with another set of double quotes if used inside double quotes. For example, Length("""Hello World, "" I said").

Returns an aggregated value from the current time back to a specified time. The time unit can be YEAR, QUARTER, MONTH, DAY, or HOUR.

Oracle Structured Query Language (SQL) supports this function. Oracle Continuous Query Language (CQL) does not support this function.

Syntax:

AGO(expr,time_unit,offset)

Example:

AGO(SUM(SALES),YEAR,1)

Returns the average of all values for the given field. Avg can accept one field parameter of type Integer, Float, or Decimal.

Syntax:

Avg(Number)

Example:

Avg(Revenue)

Creates a Case clause in a Switch statement. Switch can accept fields, expressions, and values of any type as parameters.

Syntax:

Switch(w)
  Case(x):(y)
  Default(z)

Example:

SWITCH(TypeOfChange)
   CASE("Loss"):(OldNum - NewNum)
   CASE("Gain"):(OldNum + NewNum)
   CASE("Revert"):(OldNum)
    DEFAULT(NewNum)

Returns the smallest integer greater than or equal to the specified value. Ceiling(2.9) returns 3 and Ceiling(-2.3) returns -2. Ceiling can accept one field parameter of type Integer, Float, or Decimal or a numeric value may be entered.

Syntax:

Ceiling(Number)

Examples:

Ceiling(Total)
Ceiling(3.7)

Concatenates several strings into one. Concat can accept multiple field parameters of type String, or string values may be entered.

Syntax:

Concat(String1,String2, ... ,StringN)

Example:

Concat("Hello ","World")

Returns a count of all non-null values. Count can accept one field parameter of any type.

Syntax:

Count(Field)

Example:

Count(SaleComplete)

Returns a count of distinct values in a field. CountDistinct can accept one field parameter of any type.

Syntax:

CountDistinct(Field)

Example:

CountDistinct(Salesperson)

Adds an offset to the field value. The first parameter for DateAdd must be a field of type DateTime, and the last seven parameters maybe a field of type Integer or an integer value. Zeros may be used where no offset is needed.

CQL does not support this function. This function does not add the milliseconds portion of a datetime value.

Syntax:

DateAdd(DateTime, Years, Months, Days, Hours, Minutes, Seconds, Milliseconds)

Example:

DateAdd({Last Modified}, 0, 0, 7, 0, 0, 0, 0)
//adds 7 days to the Last Modified value

DateAdd({Last Modified}, 0, 0, DaysToFollowup, 0, 0, 0, 0)
//adds DaysToFolowup number of days to the Last Modified value

Returns the amount of time between two dates or times. The time unit can be SQL_TSI_YEAR, SQL_TSI_QUARTER, SQL_TSI_MONTH, SQL_TSI_WEEK, SQL_TSI_DAY, SQL_TSI_HOUR, SQL_TSI_MINUTE, or SQL_TSI_SECOND.

CQL does not support this function.

Syntax:

DATEDIFF(time_unit,time1,time2)

Example:

DATEDIFF(SQL_TSI_DAY,Process Start Time,NOW())

Returns the day name for a date. DayName accepts one field parameter of type DateTime.

Syntax:

DayName(DateTime)

Example:

DayName({Last Modified})

Returns the day of the month for a date, in the range 1 to 31. DayOfMonth accepts one field parameter of type DateTime.

Syntax:

DayOfMonth(DateTime)

Example:

DayOfMonth({Last Modified})

Returns the day of the week for a date, in the range 1 to 7. DayOfWeek accepts one field parameter of type DateTime.

Syntax:

DayOfWeek(DateTime)

Example:

DayOfWeek({Last Modified})

Returns the day of the year for a date, in the range 1 to 366. DayOfYear accepts one field parameter of type DateTime.

Syntax:

DayOfYear(DateTime)

Example:

DayOfYear({Last Modified})

Creates a Default clause in a Switch statement. Switch can accept fields, expressions, and values of any type as parameters.

Syntax:

Switch(w)
  Case(x):(y)
  Default(z)

Example:

Specifies the third clause of an If-Then-Else statement. Else can accept fields, expressions, and values of any type as parameters.

Syntax:

If(x)
  Then(y)
  Else(z)

Example:

IF(Process Instance Status=="ACTIVE")
  THEN(DATEDIFF(SQL_TSI_DAY,Process Start Time,NOW()))
  ELSE(DATEDIFF(SQL_TSI_DAY,Process End Time,NOW()))

Returns the largest integer less than or equal to the value of the specified field. Floor(2.9) returns 2 and Floor(-2.3) returns -3. Floor can accept one field parameter of type Integer, Float, or Decimal or a numeric value may be entered.

Syntax:

Floor(Number)

Examples:

Floor(Sales)
Floor(46.75)

Returns the hour value in the range 0-23. Hour accepts one field parameter of type DateTime.

Syntax:

Hour(DateTime)

Example:

Hour({Last Modified})

Specifies the first clause of an If-Then-Else statement. If can accept fields, expressions, and values of any type as parameters.

Syntax:

If(x)
  Then(y)
  Else(z)

Example:

IF(Process Instance Status=="ACTIVE")
  THEN(DATEDIFF(SQL_TSI_DAY,Process Start Time,NOW()))
  ELSE(DATEDIFF(SQL_TSI_DAY,Process End Time,NOW()))

Returns a specified value, y, if the test value, x, is null. IfNull accepts two parameters that can be fields of any type or values of any type. However, the two parameters must be of the same data type.

Syntax:

IfNull(x,y)

Example:

IfNull(Quantity, 0)

Returns the length of the string. Length accepts one parameter that can be a field of type String, a string value in quotes, or an expression containing strings or fields of type String.

Syntax:

Length(String)

Example:

Length(Description)
Length("string")
Length(Concat(Description,"Description"))

Converts the string to lowercase letters. Lower accepts one parameter that can be a field of type String, a string value in quotes, or an expression containing strings or fields of type String.

Syntax:

Lower(String)

Example:

Lower(Description)
Lower("Description")
Lower(Concat(Description,"Description"))

Returns the maximum value of the specified field or expression. Max accepts one field parameter of any type, or another valid expression.

Syntax:

Max(x)

Example:

Max(Quantity)
Max(Concat(Description," overstock"))

Returns the median of all values for the given field. Median can accept one field parameter of type Integer, Float, or Decimal.

Syntax:

Median(Number)

Example:

Median(Revenue)

Returns the minimum value of the specified field or expression. Min accepts one field parameter of any type, or another valid expression.

Syntax:

Min(x)

Example:

Min(Quantity)
Min(Concat(Description," overstock"))

Returns the minute value in the range 0-59. Minute accepts one field parameter of type DateTime.

Syntax:

Minute(DateTime)

Example:

Minute({Last Modified})

Returns the month value for a date in the range 1-12. Month accepts one field parameter of type DateTime.

Syntax:

Month(DateTime)

Example:

Month({Last Modified})

Returns the month name for a date. MonthName accepts one field parameter of type DateTime.

Syntax:

MonthName(DateTime)

Example:

MonthName({Last Modified})

Does nothing. Use in a control function when you want one alternative to do nothing.

Syntax:

NOOP(Param)

Example:

IF("Process Running Time (millisecs)"/3600000 > 48)
  THEN("Overdue")
  ELSE(NOOP(1))

Returns the current date and time. Now does not accept any parameters.

Syntax:

Now()

Example:

DateAdd(Now(), 0, 0, 7, 0, 0, 0, 0)

Returns the percent the value represents of the total values for the specified field. PercentOfTotal accepts one field parameter of type Integer, Float, or Decimal.

CQL does not support this function.

Syntax:

PercentOfTotal(Number)

Example:

PercentOfTotal(Quantity)

Returns the quarter value in the range 1-4. Quarter accepts one field parameter of type DateTime.

Syntax:

Quarter(DateTime)

Example:

Quarter({Last Modified})

Returns the rank of a value in comparison to other values in the column. Values are not consecutive when ties occur. For example, if two values have a rank of 3, the next rank is 5.

CQL does not support this function.

Syntax:

RANK(column)

Example:

RANK(SALES)

Repeats a string for the specified number of times. Repeat accepts two parameter, the first of which may be a string value or a field of type String, the second of which may be an integer value or a field of type Integer. Either parameter can use an expression that returns a string for the first parameter and an integer for the second value.

Syntax:

Repeat(String,Integer)

Example:

Repeat("string", 5)
Repeat(Description, 2)
Repeat(Description, Quantity)
Repeat(Concat(Description," overstock"),Quantity+2)

Returns a string, x, with all occurrences of the string, y, replaced by the string z. Replace accepts three field parameters of type String, or string values.

Syntax:

Replace(String,String,String)

Example:

Replace(Description, "ing", "tion")

Rounds the specified value in the first parameter to the number of decimal places specified in the second parameter, rounding up if the number in the N+1 decimal place is 5 or greater, and rounding down otherwise. Round accepts two parameters that can be fields of type Integer, Float, or Decimal, or numeric values.

Syntax:

Round(Number,N)

Example:

Round(Sales,2)

In this example, if Sales value is 12.345, it is rounded to 12.35.

Returns the second value in the range 0-59. Second accepts one field parameter of type DateTime.

Syntax:

Second(DateTime)

Example:

Second({Last Modified})

Returns the standard deviation of the values in a column.

Syntax:

STDDEV(column))

Example:

STDDEV(PROCESS_OPEN_HOURS))

Returns the number of standard deviations from the mean for a particular value.

CQL does not support this function.

Syntax:

STDDEVSFROMMEAN(column))

Example:

STDDEVSFROMMEAN(PROCESS_OPEN_HOURS))

Returns a substring z characters long from string x, starting at position y. Substring requires three parameters, the first of which must be a string value, or a field of type String, and the second and third of which must be an integer or field of type Integer.

Syntax:

Substring("source_string", start_position, substring_length)

Example:

Substring("some string", 6, 3)
// returns "str"

Returns a summation of all values for the specified field. Sum accepts one field parameter of type Integer, Float, or Decimal.

Syntax:

Sum(Number)

Example:

Sum(Total)

Creates a Switch statement. Switch can accept fields, expressions, and values of any type as parameters.

Syntax:

Switch(w)
  Case(x):(y)
  Default(z)

Example:

Specifies the second clause of an If-Then-Else statement. Then can accept fields, expressions, and values of any type as parameters.

Syntax:

If(x)
  Then(y)
  Else(z)

Example:

IF(Process Instance Status=="ACTIVE")
  THEN(DATEDIFF(SQL_TSI_DAY,Process Start Time,NOW()))
  ELSE(DATEDIFF(SQL_TSI_DAY,Process End Time,NOW()))

Trims the whitespace characters (space, tab, carriage return, line feed, page feed, form feed, and so on) from the end of the string. TrimEnd accepts one field parameter of type String. You can also enter an expression that returns a string value.

Syntax:

TrimEnd(String)

Example:

TrimEnd(Description)
TrimEnd(Concat(Description,Subcategory))

Trims the whitespace characters (space, tab, carriage return, line feed, page feed, form feed, and so on) from the beginning of the string. TrimStart accepts one field parameter of type String. You can also enter an expression that returns a string value.

Syntax:

TrimStart(String)

Example:

TrimStart(Description)
TrimStart(Concat(Description,Subcategory))

Converts a string to uppercase letters. Upper accepts one parameter of type String. You can also enter an expression that returns a string value.

Syntax:

Upper(String)

Example:

Upper({License Plate Number})

Returns the variance of the values in a column.

Syntax:

VARIANCE(column))

Example:

VARIANCE(PROCESS_OPEN_HOURS))

Returns the variance from the mean for a particular value.

CQL does not support this function.

Syntax:

VARIANCESFROMMEAN(column))

Example:

VARIANCESFROMMEAN(PROCESS_OPEN_HOURS))

Returns the week for a DateTime value, in the range 0 to 53, because there might be the beginning of a week 53, where Sunday is the first day of the week. Week 1 is the first week with a Sunday in this year.

For example, in the year 2006, January 1st is a Sunday, so there is no week 0. The year starts with week 1 and continues to week 53. Week 53 of 2006 includes only one day, which is December 31st (also a Sunday). The Monday through Saturday following this (January 1-6 of 2007) are in week 0 of 2007.

Syntax:

Week(DateTime)

Example:

Week({Last Modified})

Returns the year value in the range 1000-9999. Year accepts one parameter of type DateTime.

Syntax:

Year(DateTime)

Example:

Year({Last Modified})