Skip to main content
Planview Customer Success Center

Functions

The following functions are available for calculated fields. Click on a function to see its description, syntax, and example.

Date

Logical

Number

String

CalendarDays

Date

DayOfMonth

ElapsedTime

FormatDate

GetChildEntityDate

Month

MonthEndDate

MonthStartDate

Now

ParseDate

QuarterEndDate

QuarterStartDate

Today

WeekDays

WeekEndDate

Weeknum

WeekStartDate

WeekStartDayText

Year

YearEndDate

YearStartDate

If

IfCase

IsBoolean

IsDate

IsFloat

IsInteger

IsNull

IsText

Not

=

!=

<

<=

>

>=

+

-

*

/

Abs

Avg

CurrentUserID

FormatNumber

GetChildEntityCount

GetChildEntityData

GetParentPortfolioValue

GetStaffingData

Max

Min

Mod

Power

Round

Sum

Compare

CompareIgnoreCase

Concatenate

EndsWith

Find

GetChildEntityValue

GetRelatedEntityValue

HTMLencode

Left

Length

Lower

LTrim

LTrimZero

ParseFloat

ParseInteger

PatternMatch

Proper

Repeat

Right

RTrim

RTrimZero

StartsWith

StringValue

Substitute

Substring

Trim

TrimZero

Upper

+

Description

Adds one or more values. Return value will depend on the data types of individual values.

Syntax

value1+ value2

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

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

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

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)

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, …)

Example

Average([Project].[Allocated Hrs], [Project].[Scheduled Hrs])

CalendarDays

Description

Number of calendar days between two dates.

Syntax

CalendarDays(date1, date2)

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 using the number of 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

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:
input FormatDate(Date(2013, 6, 3), 'MM/dd/yyyy E')

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:
input FormatDate(Date(2013, 6, 4), 'MMM/dd/yyyy EEE')

output: Jun/04/2013 Tue

m

Minute in hour

55

S

Millisecond

675

s

Second

24

yy

yyyy

 

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:
input FormatDate(Date(2008, 6, 4), 'M/d/yy')

output 6/4/08

input FormatDate(Date(2008, 6, 4), 'M/d/yyyy')

output 6/4/2008

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'))
Note that the & represents the local currency format. If a $ is used, then the $ will always appear, regardless of currency setting.

$ 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

 

Special Pattern Characters

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

Team Member

53

   

GetChildEntityCount(53)

 

Attachment

8

   

GetChildEntityCount(8)

 

Portfolio

114

   

GetChildEntityCount(114)

Issue

Team Member

53

   

GetChildEntityCount(53)

 

Attachment

8

   

GetChildEntityCount(8)

 

Task

5

   

GetChildEntityCount(5)

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

Team Member

53

 

 

GetChildEntityCount(53)

 

Attachment

8

 

 

GetChildEntityCount(8)

 

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

 

 

 

 

Baseline

105

 

 

GetChildEntityCount(105)

Task

Team Member

53

 

 

GetChildEntityCount(53)

 

Attachment

8

 

 

GetChildEntityCount(8)

 

Issue

6

 

 

GetChildEntityCount(6)

 

Task Role

52

 

 

GetChildEntityCount(52)

 

Task Schedule

55

 

 

 

 

Timesheet Entry

66

 

 

 

 

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:

  • Sum - Sum of values for a field on child entity
  • Min - Minimum value for a field on child entity rows matching optional filter
  • Max - Maximum value for a field on child entity rows matching optional filter
  • Avg - Average value for a field on child entity rows matching optional filter
  • Count - Count of child entity rows matching optional filter (note that the Entity.Field notation is required or the operation will fail, but the field value is ignored for the calculation)

(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
T1 (Summary)
-T1.1 (Not Summary, Not Leaf, Is Parent
--T1.1.1 (Leaf)
--T1.1.2 (Leaf)
T2 (Summary)
--T2.1 (Leaf)
--T2.2 (Leaf)

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:

  • Min - Minimum date for a field on child entity rows matching optional filter
  • Max - Maximum date for a field on child entity rows matching optional filter
  • Value - Value for a field on the first row of the child entity that matches optional 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 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)
  PortfolioB (level 2)
     PortfolioC (level 3)
        PortfolioD (level 4)
           PortfolioE (level 5)

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):

  • Task Role.Est Hours

  • Task Role.Est Cost

  • Task Schedule.Sched Hrs

  • Task Schedule.Scheduled Cost

  • Allocated Role.Total Allocated Hours

  • Allocated Role.Total Allocated Cost

  • Project.Actual Hours

  • Project.Actual Cost

(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 &lt;x&gt;” 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
Operators

 

 

 

 

 

If ([Project].[Target Date] < [Project].[Completion Date], "On Time Project", "Late Project")


If (

    (([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],
  Today(), "Due Today",
  Today()+1, "Due Tomorrow",
  FormatDate([Task].[Target Date, 'MMM-dd-yyyy'))

Ifcase (Month([Project].[Start Date]),
   Month(Today())-1, "Previous Month",
   Month(Today()),   "Current Month",
   Month(Today())+1,"PrevMonth",
   "")

 

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())

Month

Description

Returns month number from a date.

Syntax

Month(date)

Example

Month(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)

Example

ParseInteger("100")

Following examples are invalid and return an error:

ParseInteger(100)

ParseInteger(“100.0”)

ParseInteger(“100.”)

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 from a date.

Syntax

Year(date)

Example

Year(Today())

YearStartDate

Description

Returns date of the first day of the year.

Syntax

YearStartDate(date)

Example

YearStartDate(Today())