Plain and Domain functions
|
Formula |
Description |
|
1 |
A static value |
|
2+2 |
A plain constants calculation |
|
[Enterprise Project Type Name] |
Pick an entity field |
|
Project: ' + [Project Name] + '; Description: ' + [Description] |
Simple concatenation with field values |
|
DSUM('[Cost]', 'Tasks', '[Critical]') |
Summary cost of all critical tasks for current project |
|
DAVG('[Cost]', 'Tasks', '[Outline Level]=2') |
Average cost of tasks on second level of hierarchy for current project |
|
DCOUNT('*', 'AllTasks', '[Milestone]') |
Number of milestones for all projects |
|
DMAX('[Cost]', 'AllTasks', '[Effort Driven]') |
Most expensive effort-driven task's cost for all projects |
|
DCOUNT('*', 'AllProjects', '[Project Departments]= ' + Quote([Project Departments])) |
Number of projects in same department |
|
DCOUNT('*', 'AllResources', 'not in ([Resource Type], 2,20)') |
Number of non-budget work resources |
|
DSUM('[Assignment Budget Cost]', 'AllAssignments', '[Enterprise Project ID]= ' + Quote([Enterprise Project ID])) |
Summary budget cost of all current project assignments |
|
DLOOKUP('[Project Name]', 'Project') |
Gets project name to a task field |
Generation functions
|
Formula |
Description |
|
NewId() |
Generates an ID, starting with 1, step 1, no scope |
|
NewId(10, 2) |
Generates an ID, starting with 10, step 2, no scope |
|
NewId(50, 5, [Enterprise Project Type Name]) |
Generates an ID, starting with 50, step 5, within current EPT (project formula field) |
|
NewId(1, 1, [Enterprise Project ID]) |
Generates an ID, starting with 1, step 1, within current Project (task formula field) |
General functions, conditional operators
|
Formula |
Description |
|
Iif(IsNull([Phase Name]), 'Project', 'Workflow Project') |
Simple project categorization |
|
Iif( DCOUNT('*', 'Tasks', '[Outline Level]>0') > 0, DSUM('[Cost]', 'Tasks', '[Outline Level]>0') / DCOUNT('*', 'Tasks', [Outline Level]>0'), 0) |
Average task cost |
| switch( DCOUNT('*', 'Tasks') < 10, 'Small', DCOUNT('*', 'Tasks') < 100, 'Average', DCOUNT('*', 'Tasks') >= 100, 'Large' ) | Projects categorization based on schedule size |
|
switch( DCOUNT('*', 'Tasks') < 10, 'Small', DCOUNT('*', 'Tasks') < 100, 'Average', DCOUNT('*', 'Tasks') >= 100, 'Large') + Iif(IsNull([Phase Name]), 'Project', 'Workflow Project') |
Advanced projects categorization based on schedule size |
External functions
|
Formula |
Description |
|
WSSRisksQuery('COUNT(*)', '[Impact]>5') |
Number of risks filtered by impact |
|
WSSIssuesQuery('[Title]', ' ' , 'CreatedDate DESC') |
Gets the most recent issue title |
|
WSSDeliverablesQuery('[CreatedByResource]', '', 'CreatedDate DESC') |
Gets the most recent deliverable author name |
|
SpListQuery('Shared Documents', 'COUNT(*)') |
Gets number of documents in the “Shared Documents” library |
|
SpListQuery('My Documents', 'COUNT(*)', '[Approved]') |
Gets number of “approved” documents in the “My Documents” library |
| SqlQuery( 'select count(*) from msp_epmresource_userview ', Provider=SQLOLEDB;server=ServerName;Integrated Security=SSPI;Initial Catalog=DatabaseName' ) | Gets number of “approved” documents in the “My Documents” library |
| ParseJSON( WebServiceCall( 'http://weather.com', 'GET', HttpEncode('cityid', '34440'), 'text/json', FormatHeaders('referrer', 'http://mysite.com') ), 'Humidity') | Calls external web service, passed request data, headers, receives and parses JSON response |
| ParseXML( WebServiceCall('http://currency.com'), '/rates/usd') | Calls external web service, passed request data, headers, receives and parses XML response |