Functions
The following functions are available for calculated fields. Click on a function to see its description, syntax, and example.
Note that the data type returned for each function should match the column header unless otherwise indicated by (data type:). For example, the FormatDate function returns a String data type; the GetChildEntityDate function returns a Date. However, some functions can return multiple data types, depending on the data you are returning - these functions are indicated by (various). For example, you might use GetRelatedEntityValue on a project to get a resource's internal rate, which is a number, or their capacity start date, which is a date.
Date |
Logical |
Number |
String |
---|---|---|---|
CalendarDays (data type: integer) DayOfMonth (data type: integer) ElapsedTime (data type: integer) FormatDate (data type: string) Month (data type: integer) Now (data type: date/time) WeekDays (data type: integer) WeekStartDayText (data type: string) Weeknum (data type: integer) Year (data type: integer) |
= != < <= > >= |
FormatNumber (data type: string)
|
GetChildEntityValue (variable) GetParentPortfolioValue (variable) GetRelatedEntityValue (variable) ParseFloat (data type: float) ParseInteger (data type: integer)
|
+
Description |
Adds one or more values. Return value will depend on the data types of individual values. |
Syntax |
value1+ value2 |
Data type Display Type |
Data - Float/integer/string Display - Float/integer/string |
Example |
Integer example: [Project].[Portfolio Count]+1 1 + 5 returns 6 Float example: [Project].[Actual Hrs] + [Project].[Hrs To Complete] In the above example, since both Actual Hrs and Hrs to Complete fields are of type float, the return value is float. The following examples return a float value of 6.0: 5.0 + 1.0 5.0 + 1 5 + 1.0 Date example: This function can also be used to perform additions to date fields. For example: [Project].[Target Date] + 7 returns a date that is 7 days after the target date. String example: You can also add two string fields, for example: [Project].[Title} + [Project].[Status] |
-
Description |
Subtracts one or more values. Return value will depend on the data types of individual values. |
Syntax |
value1 - value2 |
Data type Display Type |
Data - Float/integer/string Display - Float/integer/string |
Example |
Integer example: [Project].[Portfolio Count] -1 Float example: [Project].[Scheduled Hrs] - [Project].[Hrs To Complete] The following examples return a float value of 5.0: 6.0 - 1.- 6.0 - 1 6 - 1.0 Date example: [Project].[Target Date] - 7 returns a date that is 7 days before the target date. String example: [Project].[Title] - "abc" If project title is "Large Project 0001" and you want to remove the word "Project" from the text, you can use the formula [Project].[Title] - "Project", which will return the value "Large 0001". |
*
Description |
Multiplies one or more numbers. Return value will depend on the data types of individual numbers. |
Syntax |
number1 * number2 |
Data type Display Type |
Data - Float/integer Display - Float/integer |
Example |
[Project].[Actual Hrs] *[ Resource].[Rate] 3 * 2 returns integer value Float example: The following examples return a float value of 6.0: 3.0 * 2.0 3.0 * 2 3 * 2.0 |
/
Description |
Divides one or more numbers. |
Syntax |
number1 / number2 |
Data type Display type |
Data - Float/integer Display - Float/integer |
Example |
[Project].[Actual Hrs]/([Project].[Scheduled Hrs]-[Project].[Hours To Complete]) The following examples return an integer value: 6 / 2 5 / 2 (returns 2) The following examples return a float value of 3.0: 6.0 * 2.0 6.0 * 2 6 * 2.0 |
Abs
Description |
Converts number to absolute number (always returns a positive number). |
Syntax |
Absolute(number) |
Data type Display type |
Data - Float/integer Display - Float/integer |
Example |
Absolute([Project].[Allocated Hrs] - [Project].[Scheduled Hrs]) If the result of the formula above is negative, then Absolute returns a positive number. |
Avg
Description |
Returns average across two or more numbers. |
Syntax |
Average(number1, number2, …) |
Data type Display type |
Data - Float/integer Display - Float/integer |
Example |
Average([Project].[Allocated Hrs], [Project].[Scheduled Hrs]) |
CalendarDays
Description |
Number of calendar days between two dates. |
Syntax |
CalendarDays(date1, date2) |
Data type Display Type |
Data type - Integer Display type - Integer |
Example |
CalendarDays([Project].[Start Date], [Project].[End Date]) CalendarDays(Today(),Today()) returns 1 |
Compare
Description |
Compares two strings lexicographically (case sensitive). Returns zero if the two strings are equal, a negative integer if text1 precedes text2 lexicographically, a positive integer if text1 follows text2 lexicographically. |
Syntax |
Compare(text1, text2) |
Example |
Compare("Abc", "abc") returns False |
CompareIgnoreCase
Description |
Compares two strings (case insensitive). Returns zero if the two strings are equal, a negative integer if text1 precedes text2 lexicographically, a positive integer if text1 follows text2 lexicographically. Ignores case considerations. |
Syntax |
Compare(text1, text2) |
Example |
Compare("Abc", "abc") returns True |
Concatenate
Description |
Merges two or more strings into one string. |
Syntax |
Concatenate(text1, text2, …) |
Example |
Concatenate("Day of the Week", [Project].[Start Date],"/",weeknum([Project].[Start Date])) |
CurrentUserID
Description |
Returns the numeric ID for the currently logged in user. |
Syntax |
CurrentUserID() |
Example |
CurrentUserID() |
Date
Description |
Returns a date value formatted according to the parameters specified. |
Syntax |
Date(yyyy, MM, DD, HH, MM, ss) |
Example |
Date(2011, 04, 27) Date(2011, 04, 29, 15, 00) |
DayOfMonth
Description |
Returns an integer between 1 and 31. |
Syntax |
DayOfMonth(date) |
Example |
DayOfMonth([Project.[Start Date]) |
ElapsedTime
Description |
Returns the number of seconds between midnight January 1, 1970 and Date. |
Syntax |
ElapsedTime(date) |
Example |
ElapsedTime(date1) - ElapsedTime(date2) |
EndsWith
Description |
Returns True/False if a string ends with specified search string. |
Syntax |
EndsWith(find_text, within_text) |
Example |
EndsWith("York", "New York") |
Find
Description |
Searches for a text within another text and returns the position where the search text was found. |
Syntax |
Find(find_text, within_text, starting position) |
Example |
Find(" ","First Space",1) |
FormatDate
Description |
Formats a date using the format style specified. |
|
Syntax |
FormatDate(date, format {,TZ})
|
|
Examples |
Format Pattern Note: Formats are case sensitive. |
Output (sample date: 2011-05-03 04:45:15 U.S. Pacific Time) |
FormatDate(Today(), 'yyyy/MM/dd') |
2011/05/03 |
|
FormatDate(Today(), 'yyyy/MM/dd h:mm') |
2011/05/03 4:45 |
|
FormatDate(Today(), "yyyy.MM.dd.G 'at' HH:mm:ss z") FormatDate(Today(), "yyyy.MM.dd.G 'at' HH:mm:ss", 'User') FormatDate(Today(), "yyyy.MM.dd.G 'at' HH:mm:ss", "America/Chicago'') |
2011.05.03 AD at 16:47:15 PDT User parameter applies timezone specified in current user's User Preferences America/Chicago is central time zone. Actual output (for example CDT, CST) depends on the locale. |
|
FormatDate(Today(), "EEE,MMM d, "yy") |
Tue, May 3, '11 |
|
|
FormatDate(Today(), "h:mm a EEE,MMM d, "yy") |
4:45 PM Tue, May 3, '11 |
For additional details on formatting dates, refer to: http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html |
Format patterns
You specify date and time patterns with format strings. Unquoted letters from a-z and A-Z represent parts of a date or time string. Surround text with single quotes ( ' ) to avoid misinterpretation. The letters in the following table are reserved for their documented date or time pattern; all unused letters are also reserved.
Note that the special parameter 'User' applies the timezone specified in the current user's User Preferences. See http://en.wikipedia.org/wiki/List_of_IANA_time_zones for list of available timezones.
Letter |
Represents |
Example |
a |
AM/PM |
AM |
D |
Day of year |
362 |
d |
Day of month |
27 |
E EEEE |
Day in week |
E = first 3 characters of day name (Mon, Tue, Wed, Thu, Fri, Sat, Sun) EEEE = full text of day name (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday) Example: output: 06/03/2013 Mon |
F |
Day in week of month |
4 |
G |
Era |
AD |
H |
Hour of day (0-23) |
0 |
h |
Hour in am/pm (1-12) |
12 |
K |
Hour in am/pm (0-11) |
1 |
k |
Hour of day (1-24) |
24 |
M MM MMM MMMM |
Month of year |
M = Number of month (6) MM = adds leading digit for single digit months (06) MMM = first 3 characters of month name (Jun) MMMM = full text of month name Example: output: Jun/04/2013 Tue |
m |
Minute in hour |
55 |
S |
Millisecond |
675 |
s |
Second |
24 |
yy yyyy (lower-case y)
|
Year |
If the number of pattern letters is 2, the year is truncated to 2 digits. For example, "yy" applied to 01/23/1997 results in "97". If the number of pattern letters is greater than 2, the year is interpreted literally. For example, MM/DD/yyyy applied to 03/18/01 returns "Mar 3, 01". Examples: output 6/4/08 input FormatDate(Date(2008, 6, 4), 'M/d/yyyy') output 6/4/2008 |
YY YYYY |
Week Year |
"Week Year" is the year of the end of the week containing the specified date (when the calendar supports "week year"). |
w |
Week in year |
52 |
W |
Week in month |
3 |
Z |
Time zone - general |
Pacific Standard Time; PST |
FormatNumber
Description |
Formats a number using the pattern specified. |
|
Syntax |
FormatNumber(number, pattern) |
|
Examples |
Format Pattern |
Output |
FormatNumber(1000, '& #,##0.00')) |
$ 1,000.00 |
|
FormatNumber(.794549, '#,##0.00 %') |
79.55 % |
|
FormatNumber([Task].[Estimated Cost], '#,##0.00;(#,##0.00)') where estimated cost = 3020.25 |
3,020.25 Number would be in () if negative. |
|
|
||
For additional details on formatting numbers, refer to: http://docs.oracle.com/javase/6/docs/api/java/text/DecimalFormat.html |
Many characters in a pattern are taken literally; they are matched during parsing and output unchanged during formatting. Special characters, on the other hand, stand for other characters, strings, or classes of characters. They must be quoted, unless noted otherwise, if they are to appear in the prefix or suffix as literals.
Symbol |
Represents |
0 |
Digit |
# |
Digit, zero shows as absent |
. |
Decimal separator or monetary decimal separator |
- |
Minus sign |
, |
Grouping separator. The grouping separator is typically used for thousands, but in some countries it separates ten-thousands. The grouping size is a constant number of digits between the grouping characters, such as 3 for 100,000,000 or 4 for 1,0000,0000. If you supply a pattern with multiple grouping characters, the interval between the last one and the end of the integer is the one that is used. |
E |
Separates mantiss and exponent in scientific notation; does not need to be quoted. |
; |
Separate positive and negative subpatterns |
% |
Multiply by 100 and show as percentage with rounding, if applicable. |
& |
Currency sign; replaced by the currency symbol of the current locale. If doubled, replaced by international currency symbol. If present in a pattern, the monetary decimal separator is used instead of the decimal separator. |
' |
Used to quote special characters in a prefix or suffix. For example, "'#'#" formats 789 to "#123". To create a single quote itself, use two in a row: "# ' 'clock". |
GetChildEntityCount
Description |
Returns the count of the specified child entity. This function returns data for all matching children of an entity, whether or not the user has permission on each child. |
Syntax |
GetChildEntityCount(entityTypeID, filterID) GetChildEntityCount(entityTypeID, "filterName") entityTypeID - the integer that represents the child entity type. Entity type IDs are listed in the table below. (Optional) filterID - integer that represents a filter ID or a status list value; can also accept a string that represents the name of the filter or the status value. If the integer is <=2, the system assumes it is a status list value and is interpreted as follows: 0 = Closed, 1 = Open, 2 = Proposed. If no value is passed, the selected operation is performed on all rows. (Optional) filterName - string in single or double quotes See list of valid IDs below. If a cell is empty, that feature/value is not available for that entity/child. |
Example |
For given project (entity type ID 5), count all tasks that are returned by the filter with ID 1234567. GetChildEntityCount(5,12345) For given project (entity type ID 5), count all tasks that are returned by the filter named Deliverables Completed. GetChildEntityCount(5, 'Deliverables Completed') |
Entity Reference for all GetChildEntity functions
Note that entityTypeID is used only for GetChildEntity count. All other "GetChild" functions use the field name in square brackets: [task].[Hours To Complete]
Entity |
Child Entity |
entityType ID |
Status |
filterID |
Example |
---|---|---|---|---|---|
Account |
Team Member |
53 |
|
|
GetChildEntityCount(53) |
|
Attachment |
8 |
|
|
GetChildEntityCount(8) |
|
Contact |
19 |
|
|
GetChildEntityCount(19) |
Asset |
Attachment |
8 |
GetChildEntityCount(8) |
||
Issue |
Attachment |
8 |
GetChildEntityCount(8) |
||
Portfolio |
Attachment |
8 |
GetChildEntityCount(8) |
||
Program |
Team Member |
53 |
|
|
GetChildEntityCount(53) |
|
Attachment |
8 |
|
|
GetChildEntityCount(8) |
|
Project |
4 |
All |
|
GetChildEntityCount(4) |
|
Project |
4 |
All Closed |
0 |
GetChildEntityCount(4,0) |
|
Project |
4 |
All Open |
1 |
GetChildEntityCount(4,1) |
|
Project |
4 |
All Proposed |
2 |
GetChildEntityCount(4,2) GetChildEntityCount( |
Project |
Attachment |
8 |
|
|
GetChildEntityCount(8) |
Financial Entry | 300 | GetChildEntityCount(300) | |||
|
Issue |
6 |
All |
|
GetChildEntityCount(6) |
|
Issue |
6 |
All Closed |
0 |
GetChildEntityCount(6,0) |
|
Issue |
6 |
All Open |
1 |
GetChildEntityCount(6,1) |
|
Issue |
6 |
All Proposed |
2 |
GetChildEntityCount(6,2) |
|
Task |
5 |
All |
|
GetChildEntityCount(5) |
|
Task |
5 |
All Closed |
0 |
GetChildEntityCount(5,0) |
|
Task |
5 |
All Open |
1 |
GetChildEntityCount(5,1) |
|
Task |
5 |
All Proposed |
2 |
GetChildEntityCount(5,2) |
|
Allocation |
54 |
|
|
GetChildEntityCount(54) |
|
Task Role |
52 |
|
|
|
|
Task Schedule |
79 |
|
|
|
Task |
Attachment |
8 |
|
|
GetChildEntityCount(8) |
|
Task Role |
52 |
|
|
GetChildEntityCount(52) |
|
Task Schedule |
55 |
|
|
|
|
Expense Entry |
71 |
|
|
|
GetChildEntityData
Description |
Returns aggregated (rollup) data for fields in the specified child entity. You can also use this function to return data for immediate children only. This function returns data for all matching children of an entity, whether or not the user has permission on each child. Note: Calculated fields that contain this function cannot be used in filters. Check that the Filter Visibility field on the Basic Details tab is set to No. |
Syntax |
GetChildEntityData(Operation, [Entity].[Field], Filter) (Required) Operation values:
(Required) Entity.Field - the entity followed by the field name on which to perform the operation. Note that if you are using the Count operation, the [entity.field] notation is required, but the field value is ignored. (Optional) Filter - integer that represents a filter ID or a status list value; can also accept a string that represents the name of the filter or the status value. If the integer is <=2, the system assumes it is a status list value and is interpreted as follows: 0 = Closed, 1 = Open, 2 = Proposed. If no value is passed, the selected operation is performed on all rows. See list of valid IDs. If a cell is empty, that feature/value is not available for that entity/child. |
Example |
Create a field on a project that gets all tasks of a project, applies the filter 'leaf tasks', and returns the sum of the 'Hours to Complete' fields for every task returned by the filter. GetChildEntityData("Sum", [Task].[Hours to Complete], "leaf tasks") Special case for summary tasks To roll up data from child tasks for a given summary task, create a calculated field on the task entity: GetChildEntityData("sum", [Task].[Scheduled Hours], 'Leaf Task') returns sum of scheduled hours for all leaf tasks on a given summary task. Special case for immediate children Use the special syntax "SumImmediateChildren" For an example, use this task list: Task Plan To roll up data from immediate child tasks for a given summary task, create a calculated field on the task entity: GetChildEntityData("SumImmediateChildren", [Task].[Actual Hrs], My Filter) For T1 - this would return value for T1.1 only |
GetChildEntityDate
Description |
Performs date operations on fields in the specified child entities. This function returns data for all matching children of an entity, whether or not the user has permission on each child. Note: Calculated fields that contain this function cannot be used in filters. Check that the Filter Visibility field on the Basic Details tab is set to No. |
Syntax |
GetChildEntityDate(Operation, [Entity].[Field], Filter) (Required) Operation values:
(Required) Entity.Field - the entity followed by the field name on which to perform the operation. (Optional) Filter - integer that represents a filter ID or a status list value; can also accept a string that represents the name of the filter or the status value. If the integer is <=2, the system assumes it is a status list value and is interpreted as follows: 0 = Closed, 1 = Open, 2 = Proposed. If no value is passed, the selected operation is performed on all rows. See list of valid IDs. If a cell is empty, that feature/value is not available for that entity/child. |
Example |
Gets all tasks of a project, applies filter 'my tasks', and for every task calls property 'Start Date' and returns the earliest start date. GetChildEntityDate("Min", [Task].[Start Date], "my tasks") Special case for summary tasks To find the earliest date in a set of child tasks: GetChildEntityDate("Min", [Task].[Start Date], 'Leaf Task') returns earliest Start Date for all leaf tasks on a given summary task |
GetChildEntityValue
Description |
Returns the value of the field from the child entity that matches the filter criteria. If the filter returns more than one row, then the value for the field on the first row is returned (based on default sort order). This function returns data for all matching children of an entity, whether or not the user has permission on each child. Note: Calculated fields that contain this function cannot be used in filters. Check that the Filter Visibility field on the Basic Details tab is set to No. |
Syntax |
GetChildEntityValue([Entity].[Field], Filter) (Required) Entity.Field - the entity followed by the field name on which to perform the operation. (Optional) Filter - integer that represents a filter ID or a status list value; can also accept a string that represents the name of the filter or the status value. If the integer is <=2, the system assumes it is a status list value and is interpreted as follows: 0 = Closed, 1 = Open, 2 = Proposed. If no value is passed, the selected operation is performed on all rows. See list of valid IDs. If a cell is empty, that feature/value is not available for that entity/child. |
Example |
Gets all tasks from a project, applies filter 'My Go Live Milestone Filter', and returns the Go Live date from the first row that matches the filter. GetChildEntityData ([Task].[Go Live Date], "My Go Live Milestone Filter") Special case for summary tasks Gets all child tasks of a summary task, applies filter 'My Go Live Milestone Filter', and returns the Go Live date from the first row that matches the filter. GetChildEntityValue([Task].Go Live Date], LeafTask') |
GetParentPortfolioValue
Description |
Returns parent portfolio field for the specified parent level. This function returns data for all matching children of an entity, whether or not the user has permission on each child. Note: Calculated fields that contain this function cannot be used in filters. Check that the Filter Visibility field on the Basic Details tab is set to No. |
Syntax |
GetParentPortfolioValue ([Entity Name].[Field Name], Level) (Required) EntityName.FieldName - the name of the entity and the name of the field whose value you wish to retrieve. Level - |
Example |
Imagine a sample 5-level hierarchy of portfolios: PortfolioA (level 1) The following examples are applied to PortfolioE (level 5): GetParentPortfolioValue ([Portfolio].[Title], 1) returns PortfolioA. This formula returns PortfolioA when applied to any portfolio other than A, because PortfolioA is at the first level. GetParentPortfolioValue ([Portfolio].[Category], 1) returns the Category value for PortfolioA. This just illustrates how to return values for different fields. GetParentPortfolioValue ([Portfolio].[Title]) returns PortfolioD. Because no level is specified, the immediate parent is returned, which is the default behavior. GetParentPortfolioValue ([Portfolio].[Title], [Portfolio].[Level] - 1) returns PortfolioD, because "-1" means to return one below current level. Now, apply the same formulas to Project 111, which is linked to PortfolioE: GetParentPortfolioValue ([Portfolio].[Title], 1) returns PortfolioA, because Project 111 is linked to PortfolioE, whose top parent is PortfolioA. GetParentPortfolioValue ([Portfolio].[Category], 1) still returns the Category value for PortfolioA, because Project 111 is linked to PortfolioE, whose top parent is PortfolioA. GetParentPortfolioValue ([Portfolio].[Title]) returns PortfolioE because PortfolioE is the immediate parent to Project 111. GetParentPortfolioValue ([Portfolio].[Title], [Portfolio].[Level] - 1) returns PortfolioD, because "-1" means to return one below the immediate parent. |
GetRelatedEntityValue
Description |
Retrieves field values from related entities. For example, from the Project entity, you can create a lookup list for resources. This function allows you to return fields from the resource entity, such as a resource's department head or supervisor. This function returns data for all matching children of an entity, whether or not the user has permission on each child. Note: Calculated fields that contain this function cannot be used in filters. Check that the Filter Visibility field on the Basic Details tab is set to No. |
Syntax |
GetRelatedEntityValue(entityID, [EntityName].[FieldName]) (Required) entityID - the ID of the related entity. (Required) EntityName.FieldName - the name of the entity and the name of the field whose value you wish to retrieve. |
Example |
GetRelatedEntityValue([Project].[Stakeholder], [Resource].[Department]) In the above example, Stakeholder is a UDF resource picklist list that returns the ID of the stakeholder, which is then used to return the related Department value. |
GetStaffingData
Description |
Returns labor/non/labor data for a specific time period, inclusive of the start date, and exclusive of the end date. For example, to capture 01/01/2017 - 12/31/17, use fromDate = 01/01/2017 and toDate = 01/01/2018. Note: Calculated fields that contain this function cannot be used in filters. |
Syntax |
GetStaffingData([Entity].[Field], fromDate, ToDate, Filter) (Required) Entity.Field - The entity followed by the field name on which to perform the operation. The following entity.field pairs are supported (note that the field names must be used exactly as specified below):
(Required) fromDate, toDate - String date in the form of YYYY-MM-DD, or date field/function. Defines the date range for the data you are requesting - not inclusive of toDate. For example, to capture data from 12/31/2107, use a toDateof 01/01/2018. (Optional) Filter - Filter name ("filter name" in examples below). Notes: Filters are not supported for the Actual Hrs or Actual Costs fields. To get unallocated hours: Create a filter on the entity Allocated Role and check for Resource = Unstaffed. |
Examples |
Every example has 2 versions: one with a filter and one without, except for the [Project].[Actual Hrs] example, which doesn't support filters. GetStaffingData ([Allocated Role].[Total Allocated Hours], Today(), Today()), "My Allocated Role Filter") GetStaffingData ([Allocated Role].[Total Allocated Hours], Today(), Today())) GetStaffingData ([Task Schedule].[Sched Hrs],'2014-04-01', '2014-04-07', 'My Task Schedules Filter') GetStaffingData ([Task Schedule].[Sched Hrs],'2014-04-01', '2014-04-07') GetStaffingData([Task Role].[Est Hours], MonthStartDate(Today()), MonthEndDate(Today()), “My Filter”) GetStaffingData([Task Role].[Est Hours], MonthStartDate(Today()), MonthEndDate(Today()) GetStaffingData ([Project].[Actual Hours], MonthStartDate(Today()), MonthEndDate(Today())) |
HTMLencode
Description |
Processes fields that have tags that need to be retained when exporting to Excel. For example, the name of a project might be Project <x>. The <x> is typically stripped off when exporting to Excel, leaving the title as "Project". Use htmlencode to render the title "Project <x>". |
Syntax |
htmlencode(field name) |
Example |
If project title was “Project <x>”, then CF_Title = htmlencode([Project].[Title]) returns the value “Project <x>” which will export to Excel as “Project <x>”. If HTMLencode function is not used, the field would be exported to Excel as “Project”. |
If
Description |
Evaluates a conditional and if the condition is True, returns ValueA. If the condition is False, returns ValueB. You can also have nested If statements by using another If statement instead of ValueA or ValueB. |
Syntax |
If(condition, ValueA, ValueB) |
Example
Example
Conditional
|
If ([Project].[Target Date] < [Project].[Completion Date], "On Time Project", "Late Project")
(([Project].[Priority] = "High") OR ([Project].[Priority] = "Medium")) AND (([Project].[Investment Type] = "Security") OR ([Project].[Investment Type] = "Compliance")), "Must Do Project", "Optional Project" ) The following conditional operators can be used to compare fields or values in the condition of the If statement: = != < <= > >= |
IfCase
Description |
Evaluates multiple conditions and returns the specified value if the condition matches. If the condition does not have a match, the statement returns the default value. If no default value is required, use "". |
Syntax |
IfCase(expr, match1, value1, match2, value2, ..., defaultValue) |
Examples |
Ifcase([Task].[Target Date], Ifcase (Month([Project].[Start Date]), |
|
IsBoolean
Description |
Returns True if the field type is Boolean. |
Syntax |
IsBoolean(field name or value) |
Example |
IsBoolean([Project].[Can I Score] returns True IsBoolean([Project].[Start Date]) returns False |
IsDate
Description |
Returns True if the field type is a date. |
Syntax |
IsDate(field name or value) |
Example |
IsDate([Project].[Start Date]) returns True IsDate(date(4,4,2011)) returns True IsDate(5) returns False IsDate([Project].[Business Unit]) returns False |
IsFloat
Description |
Returns True if the field type is a float |
Syntax |
IsFloat(field name or value) |
Example |
IsFloat([Project].[Hrs To Comp]) returns True IsFloat(100.0) returns True IsFloat(100.) returns True IsFloat(100) returns False IsFloat("100") returns False IsFloat([Project].[Business Unit] returns False IsFloat([Project].[Last Modified Date]) returns False |
IsInteger
Description |
Returns True is the field type is an integer. |
Syntax |
IsInteger(field name or value) |
Example |
IsInteger([Project].[ID]) returns True IsInteger(100.0) returns False IsInteger(100.) returns False IsInteger(100) returns True IsInteger(“100”) returns False IsInteger([Project].[Business Unit]) returns False IsInteger([Project].[Hrs To Comp]) returns False |
IsNull
Description |
Returns True if value on a field is not set. |
Syntax |
IsNull(field name) |
Example |
IsNull([Project].[Completion Date]) returns True if Completion Date is not entered. IsNull([Project].[ID]) returns False because ID field typically will have a value |
IsText
Description |
Returns True if field is text. |
Syntax |
IsText (fieldname or value) |
Example |
IsText([Project].[Business Unit]) returns True sText("Abc") returns True IsText(100.0) returns False IsText(100.) returns False IsText(100) returns False IsText("100") returns True IsText([Project].[ID]) returns False |
Left
Description |
Returns specified number of characters from the beginning of the string. |
Syntax |
Left(text, number) |
Example |
Left([Project].[Title], 5) |
Length
Description |
Returns length of a string. |
Syntax |
Length(text) |
Example |
Length([Project].[Title]) |
Lower
Description |
Converts string to lower text. |
Syntax |
Lower(text) |
Example |
Lower ("Sample Text") returns "sample text" |
LTrim
Description |
Removes leading spaces from a string |
Syntax |
LTrim(text) |
Example |
Ltrim (" Sample Text ") " Sample Text " The text above has 5 spaces before and after the words “Sample Text”. The length of the string before LTrim is 21 and after Ltrim is 16 after the removal of 5 leading spaces. |
LTrimZero
Description |
Removes leading zeroes from a string |
Syntax |
LTrimZero(text) |
Example |
LTrimZero ("0000100.28001000") returns 100.28001000 |
Max
Description |
Returns the maximum value across two or more numbers. |
Syntax |
Max(number1, number2, ...) |
Example |
Max([Project].[Allocated Hrs], [Project].[Scheduled Hrs]) |
Min
Description |
Returns the minimum value across two or more numbers. |
Syntax |
Min(number1, number2, ...) |
Example |
Min([Project].[Allocated Hrs], [Project].[Scheduled Hrs]) |
Mod
Description |
Returns the remainder after dividing the number by the divisor. |
Syntax |
Mod(number, divisor) |
Example |
Mod(15, 4) returns 3 |
MonthEndDate
Description |
Returns date of the last day of the month. |
Syntax |
MonthEndDate(date) |
Example |
MonthEndDate(Today()) |
MonthStartDate
Description |
Returns date of the first day of the month |
Syntax |
MonthStartDate(date) |
Example |
MonthStartDate(Today()) |
Not
Description |
Used in conjunction with If() for a conditional comparison statement. If value is NOT something, return ValueA. If that statement is false, return ValueB. Can also use != instead of Not(). |
Syntax |
If(Not(condition, ValueA, ValueB) |
Example |
if (Not([Project].[Target Date] = [Project].[Completion Date]), "Target and Completion Dates are NOT same", "Target and Completion Dates are same") if([Project].[Actual Hrs] != [Project].[Budget Hrs], "Actual and Budget Hrs NOT same", "Actual and Budget Hrs same") |
Now
Description |
Returns the current date and time, to the second. |
Syntax |
Now() |
Example |
Now() returns Tue May 03 16:00:27 PDT 2011 FormatDate(Now(), 'MM/dd/yyyy HH:mm:ss') returns 09/19/2012 12:51:33 |
ParseDate
Description |
Parses text to return date value. |
Syntax |
ParseDate(text, format) |
Example |
ParseDate('05/02/2011', 'MM/dd/yyyy') ParseDate('2011/05/02', 'yyyy/MM/dd') |
ParseFloat
Description |
Parses text to return float value. |
Syntax |
ParseFloat(text) |
Example |
ParseFloat("100.0") ParseFloat("100.") ParseFloat("100") Following examples are invalid and return error: ParseFloat(100) ParseFloat(“abc”) |
ParseInteger
Description |
Parses text to return integer value. |
Syntax |
ParseInteger(text) |
Examples |
ParseInteger("100") ParseInteger(100) ParseInteger(“100.0”) ParseInteger(“100.”) ParseInteger(007) Following examples are invalid and return an error: ParseInteger(“abc”) |
PatternMatch
Description |
Performs advanced pattern matching using regex syntax |
Syntax |
PatternMatch(within text, find text) |
Example |
PatternMatch('hello', '.*el.*') returns True PatternMatch('hello', 'el.*') returns False For additional details on the pattern matching syntax, refer to: http://download.oracle.com/javase/6/docs/api/java/util/regex/Pattern.html |
Power
Description |
Returns the number raised to the specified power. |
Syntax |
Power(number1, exponent) |
Example |
Power(10, 2) returns square of the number 10 - returns 100 Power(100, 0.5) returns the square root of the number 100 - returns 10 Power(100, 1.0/2.0) returns square root of the number 100 - returns 10 Power(100, 1/2) converts 1/2 using integer division to 0, and returns 100^0 - returns 1.0 |
Proper
Description |
Converts the first letter of all words to upper case. |
Syntax |
Proper(text) |
Example |
Proper("sample TEXT") returns Sample Text |
Quarter
Description |
Returns calendar quarter for a date (1-4). |
Syntax |
Quarter(date) |
Example |
Quarter(Today()) |
QuarterEndDate
Description |
Returns date of the last day of the quarter |
Syntax |
QuarterEndDate(date) |
Example |
QuarterEndDate(Today()) |
QuarterStartDate
Description |
Returns date of the first day of the quarter |
Syntax |
QuarterStartDate(date) |
Example |
QuarterStartDat(Today()) |
Right
Description |
Returns specified number of characters from the ending of the string |
Syntax |
Right(text, number) |
Example |
Right("Today is Monday", 6) returns Monday |
RTrim
Description |
Removes trailing spaces from a string |
Syntax |
RTrim(text) |
Example |
RTrim (" Sample Text ") returns " Sample Text" The text above has 5 spaces before and after the words “Sample Text”. The length of the string before RTrim is 21 and after Rtrim is 16 after the removal of 5 trailing spaces. |
RTrimZero
Description |
Removes trailing zeroes from a string |
Syntax |
RtrimZero(text) |
Example |
RTrimZero ("0000100.28001000") returns "0000100.28001" |
Repeat
Description |
Repeats a given string for the number of times specified |
Syntax |
Repeat(text, number of times) |
Example |
Repeat ("*", 5) returns ***** |
Round
Description |
Round a number to specified digits |
Syntax |
Round(number, round to digits) |
Example |
Round(1.49,0) returns 1 Round(1.5, 0) returns 2 Round(1.23456, 3) returns 1.235 Round([Project].[Allocated Hrs]/[Project].[ActualHrs], 0) |
StartsWith
Description |
Returns True/False if a string starts with specified search string (case sensitive) |
Syntax |
StartsWith(find_text, within_text) |
Example |
StartsWith("New", "New York") |
StringValue
Description |
Converts numbers to strings. |
Syntax |
StringValue(number) |
Example |
StringValue(2011) - returns string "2011" StringValue(2011+5) - performs the addition and returns string "2016" |
Substitute
Description |
Searches for a pattern in a string and replaces the text with replacement text. |
Syntax |
Substitute(source text, regular expression, replace, replacement text) |
Example |
Substitute("Hello World", "[lr]","*") returns He**o Wo**d For additional details on the pattern matching syntax, refer to: http://download.oracle.com/javase/6/docs/api/java/util/regex/Pattern.html |
Substring
Description |
Returns the string value from the starting position. Ending position is optional. |
Syntax |
Substring(string, start position, end position) |
Example |
(Substring("Hello World", 0) returns Hello World Substring("Hello World", 6) returns World Substring("Hello World", 0, 5) returns Hello (Substring("Hello World", 1, 5) returns ello World |
Sum
Description |
Sums numeric values. |
Syntax |
Sum(number1, number2, …) |
Example |
Sum([Project].[ActualHrs], 2*[Project].[Hrs To Complete]) Sum(1, 2, 3, 4, 5) returns 15 Sum(1.0, 2, 3, 4, 5) returns 15.0 |
Today
Description |
Returns date and time at midnight of current day. |
Syntax |
Today() |
Example |
Today() returns Tue May 03 00:00:00 PDT 2011 FormatDate(Today(),'MM/dd/yyyy HH:mm:SS') returns 09/19/2012 00:00:00 |
Trim
Description |
Removes leading and trailing spaces from a string. |
Syntax |
Trim(text) |
Example |
Trim (" Sample Text ") returns "Sample Text" |
TrimZero
Description |
Removes leading and trailing zeroes from a string. |
Syntax |
TrimZero(text) |
Example |
TrimZero ("0000100.280010") returns "100.28001) |
Upper
Description |
Converts string to upper text. |
Syntax |
Upper(text) |
Example |
Upper ("Sample Text") returns "SAMPLE TEXT" |
WeekDays
Description |
Number of weekdays between two dates. |
Syntax |
Weekdays(date1, date2) |
Example |
WeekDays(Project].[Start Date],[Project].Complete Date]) WeekDays(Today(), Today()) returns 1 |
WeekEndDate
Description |
Returns date of the last day of the week. |
Syntax |
WeekEndDate(date) |
Example |
WeekEndDate(Today()) |
WeekStartDate
Description |
Returns date of first day of the week. |
Syntax |
WeekStartDate(date) |
Example |
WeekStartDate(Today()) |
WeekStartDayText
Description |
Returns first day of the week. |
Syntax |
WeekStartDayText() |
Example |
WeekStartDayText() |
Weeknum
Description |
Returns week number for the specified date. |
Syntax |
Weeknum(date) |
Example |
Weeknum(Today()) |
YearEndDate
Description |
Returns date of the last day of the year. |
Syntax |
YearEndDate(date) |
Example |
YearEndDate(Today()) |
Year
Description |
Returns year (number) from a date. |
Syntax |
Year(date) |
Example |
Year(Today()) returns 2020
|
YearStartDate
Description |
Returns date of the first day of the year. |
Syntax |
YearStartDate(date) |
Example |
YearStartDate(Today()) |