FluentPro Help Center

What do you need help with?

    Sorry, we didn't find any relevant articles for you.

    Send us your queries using the form below and we will get back to you with a solution.


    Domain functions

    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:

    1. 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’.
    2. Testing each task of the current project against the condition and creating a list of suitable tasks.
    3. 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:

    1. Filtering and sorting on the list of entities;
    2. Selecting either the first entity (DFIRST function) or the last entity (DLAST function) or the only entity (DLOOKUP function);
    3. 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')

    Was this article helpful?

    Table of contents

      Still can't find what you're looking for?

      Contact Support
      Back To Top