Domain functions provide an ability to get data from a different domain. A domain represents an entity type (e. g. project, task, resource). Domain functions allow us to pull data for a project field from task fields, for a task field from parent project and task assignments fields etc. They can be divided into two categories: aggregate functions and lookup functions.
Domain functions accept up to 4 parameters:
- Field – required, a field name from the specified domain;
- Domain – required, the target domain for retrieving data;
- Filter – optional, a filter criteria for retrieving data;
- Sort – optional, a sort expression for data representation.
Field parameter
The field parameter represents a field name from the specified domain surrounded by brackets (e.g. [TaskName], [Cost], [ProjectName]). The following function gets the sum of task costs for the current projects:
DSUM('[Cost]', 'Tasks')'*' can be specified as a field parameter for DCOUNT function if it is necessary to retrieve the count of all rows in the domain. The following function returns the count of tasks:
DCOUNT('*', 'AllTasks')Domain parameter
The domain parameter specifies the target domain for retrieving data. It must be one of the predefined values. The list of domains you can find in the table below. The set of entities which will be processed by the domain function depends on the current entity type and the specified domain. The following examples demonstrate the dependency between the current entity type, the specified domain, and the processed entities set:
DCOUNT('*', ‘Resources’) Returns the number of resources assigned to all tasks in the current project (if called from the project field) and the number of resources assigned to the current task (if called from the task field).
DCOUNT('*', ‘AllResources’) Always returns the number of all resources regardless current field’s entity type.
The full list of dependencies between the current entity type, the specified domain and the entities set processed by the domain function you can find in the following table:
| Entity type | Domain |
Entities list |
| Project | Projects | All projects in the PWA from Reporting database |
| Project | Tasks | Current project’s tasks from PSI |
| Project | Assignments | Current project’s task’s assignments from PSI |
| Project | Resources | Resources assigned to tasks in the current project |
| Task | Projects | The current project from PSI |
| Task | Tasks | Tasks in the current project from PSI |
| Task | Assignments | Current task’s assignments |
| Task | Resources | Resources assigned to the current task |
| Resource | Projects | Projects with the current resource assigned to one of the tasks |
| Resource | Tasks | Tasks with the current resource assigned |
| Resource | Assignments | Current resource’s assignments |
| Resource | Resources | All resources in the PWA from Reporting database |
| Any | AllProjects | All projects in the PWA from Reporting database |
| Any | AllResources | All resources in the PWA from Reporting database |
| Any | AllAssignments | All assignments in the PWA from Reporting database |
| Any | AllTasks | All tasks in the PWA from Reporting database |
Filter parameter
The filter parameter represents an expression which will be evaluated against each entity in the domain. You can use boolean field name such as [Critical], [Milestone] or [Effort Driven]. The following function returns the most expensive effort-driven task’s cost for all projects:
DMAX('[Cost]', 'AllTasks', '[Effort Driven]')More complex expressions require string concatenations and references to the parent entity. You will find such example in the aggregate functions section. Please review operators page to get more information about building boolean expressions.
Sort parameter
The sort parameter is a set of fields separated by coma which specify how domain entities are sorted. The syntax is similar to SQL order by clause (e. g. [Cost] Asc, [Work] Desc). The following function returns the name of the most expensive effort-driven task for all projects: DFIRST('[TaskName]', 'AllTasks', '[Effort Driven]', '[Cost] Asc')
Aggregate Functions
The aggregate functions are: DAVG, DCOUNT, DSUM, DMAX, DMIN, DSTDDEV, DSTDDEVP, DVAR, DVARP. Aggregate functions perform a calculation on a set of values and return a single value. They accept up to 3 parameters: Field, Domain, and Filter. To perform an aggregation on a set of entities it is necessary to determine a field and a domain for calculation. For example, to get a sum of tasks’ costs for the current project use the following project formula:
DSUM('[Cost]', 'Tasks')To get a sum of only critical tasks’ costs add the filter:
DSUM('[Cost]', 'Tasks', '[Critical]')The filter can have a reference to the current entity. For example, the project contains the custom field [Calc tasks category], and tasks have field [Task category]. To calculate the summary cost of all tasks in the category selected in the current project, use the following formula:
DSUM('[Cost]', 'Tasks', '[Task category] = ' + Quote([Calc tasks category]))FluentPro Formulas will do following steps to evaluate this formula:
- Building a filter condition from the filter expression. For example, project field [Calc tasks category] has value “Category 1”, in such case, the filter condition will be [Task category] = ‘Category 1’.
- Testing each task of the current project against the condition and creating a list of suitable tasks.
- Summarizing all [Cost] fields from the suitable tasks’ list and returning the resulting value.
HINT: Use Quote function to add quotes to field value properly. We do not recommend doing it manually.
Another example shows how to find a sum of the active tasks’ costs in the current project in the currently selected category which are at least 50% completed:
DSUM('[Cost]', 'Tasks', '[Task category] = ' + Quote([Calc tasks category]) + ' and [TaskPercentCompleted] >= 50 and [TaskIsActive]')Lookup functions
The lookup functions are: DFIRST, DLAST, DLOOKUP. Lookup functions perform the following steps:
- Filtering and sorting on the list of entities;
- Selecting either the first entity (DFIRST function) or the last entity (DLAST function) or the only entity (DLOOKUP function);
- Returning a field value from selected entity.
Lookup functions accept up to 4 parameters: Field, Domain, Filter and Sort expression. Field, Domain and Filter parameters work the same as for aggregate functions. The important parameter is Sort expression. For example, to get the name of the most expensive task of the current project the following function can be used (the filter expression is not necessary here, so we use empty quotes):
DFIRST('[TaskName]', 'Tasks', '', '[Cost] Asc')