۶Ƶ

DocumentationWorkfrontWorkfront Guide

Reference collections in a report

Last update: Tue Jun 11 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • User

Building a report in ۶Ƶ Workfront allows you to display a set of objects, their respective fields, or linked objects in a list, a grid, or a chart format.

For more information about building a report in Workfront, see Create a custom report.

Access requirements

Expand to view access requirements for the functionality in this article.

You must have the following access to perform the steps in this article:

table 0-row-2 1-row-2 2-row-2 3-row-2 layout-auto html-authored no-header
۶Ƶ Workfront plan* Any
۶Ƶ Workfront license* Plan
Access level configurations*

Edit access to Filters, Views, Groupings

Edit access to Reports, Dashboards, Calendars

Note: If you still don't have access, ask your Workfront administrator if they set additional restrictions in your access level. For information on how a Workfront administrator can modify your access level, see Create or modify custom access levels.
Object permissions

Manage permissions to a report

Manage permissions to a view, filter, or grouping

For information on requesting additional access, see Request access to objects.

To find out what plan, license type, or access you have, contact your Workfront administrator.

Understand collections

A collection is a list of objects that are linked to another object.

You have the following two relationships between objects in Workfront:

  • A one-to-one relationship: One object can be linked to only one other object at a time.
    For example, a project can only be linked to one portfolio at a time.

  • A one-to-many relationship: One object can be linked to several other objects at a time.
    For example, a project can have multiple tasks. In this case, the list of tasks forms a collection for the project.

IMPORTANT
You can build a report showing the one-to-one relationship between objects by using the standard report builder. However, you can only build a report showing the one-to-many relationship between objects by using the text mode interface in the report builder.

For more information about building a report in the standard report builder, see Create a custom report.

For more information about building a report using the text mode interface, see:

Find collection objects and their fields in the API Explorer find-collection-objects-and-their-fields-in-the-api-explorer

Not all collections can be reported on.

To understand what objects can be associated with a collection of other, you must use the API Explorer.
For more information about the API Explorer table, see the API Explorer.

To find out what collections can be reported on:

  1. Go to the API Explorer.

  2. Find the object of your report.

  3. Select the collections tab.

    note note
    NOTE
    Only the objects listed on this tab can be represented as a collection in a report for the object you selected.

  4. Expand the object of your collection by clicking it.

  5. Click the link displayed to go to the object of your collection.
    This opens the fields tab for the object of your collection.

    note note
    NOTE
    Only the fields listed on this tab can be referenced in the collection report, or the fields associated with objects listed on this tab.

Reference collections in reports

You can reference objects from a collection in the following reporting elements:

  • Views
  • Filters
  • Prompts

You cannot reference objects from a collection in the following reporting elements:

  • Groupings
  • Chart

For example, you could reference the task or issue collections from a project report, to show task or issue information at the project level.

Reference a collection in the View of a report reference-a-collection-in-the-view-of-a-report

You can reference a collection of objects in the view of a report, to show attributes of objects associated with the object of the report.

For example, you can show task or issue information in a project report, by building a collection column for tasks or issue in the view of the report.

You can display information about the tasks or issues, like names, dates, primary assignees, percent complete, etc in the collection view.

The view displays task or issue information in a list format, with every line of the list representing information about a task or an issue. The list of tasks or issues and their fields appears on the same line as the project the tasks or issues belong to.
issue_and_tasks_collections_in_reports.png

Add a collection column in a report View add-a-collection-column-in-a-report-view

To add a collection column in a report view:

  1. Click the Main menu , then click Reports.

  2. Click New Report.

  3. Select the object of your report.

  4. Navigate away from your report, and using the API Explorer, determine what collections are available for the object you selected for your report.

    For more information about selecting the object of your collection, see the section Find collection objects and their fields in the API Explorer in this article.
    Make a note of what the name of the object for the collection is.

  5. Using the API Explorer, go to the list of fields for the object you want to display in the collection.

    For more information about finding the fields of the object of your collection, see the section Find collection objects and their fields in the API Explorer in this article.

    Make a note of what the name of the field you want to display in the collection is.

  6. Navigate back to your report, and in the Columns (View) tab, click Add Column.

  7. Click Switch to Text Mode.

  8. Mouse over the dialog box, and click Click to edit text.

  9. Select all text in the Text Mode dialog box and remove it, then paste the following code if you are referencing a field of the collection object:

    code language-none 
    valueformat=HTML
textmode=true
type=iterate
listdelimiter=<p>
displayname=Column Name
listmethod=nested(collection object name).lists
valuefield=collection object field

  10. Replace Column Name with the name of your column in the displayname line.

  11. Replace collection object name with the name of your collection object in the listmethod line, as it appears in the API Explorer.

  12. Replace collection object field with the name of the field of your collection object in the valuefield line, as it appears in the API Explorer.

    You can replace valuefield with valueexpression, if you want to create a custom expression in your view.

    For more information about calculated custom expressions, see Overview of calculated data expressions.

    For example, if you want to display a list of the tasks in a project report. This collection uses a valuefield line for referencing the names of the tasks.

    Do one of the following:

    • Use the following code to build your column:

      code language-none 
      valueformat=HTML
textmode=true
type=iterate
listdelimiter=<p>
displayname=Project Tasks Names
listmethod=nested(tasks).lists
valuefield=name

    • Use the following code to display a list of issues in the report:

      code language-none 
      displayname=Project Issues Names
listdelimiter=<p>
listmethod=nested(issues).lists
textmode=true
type=iterate
valuefield=name
valueformat=HTML

      Notice that in a collection you must use issues for the listmethod line, instead of opTasks which is the database name for Issues. For information about when to use issue and when to use opTask when referring to issues, see Use “opTask” and “issue” when referencing issues.

    • If you want to display a list of the tasks in a project report along with their primary assignee, you would use a valueexpression line for referencing the names of the tasks adjacent to the names of their primary assignees instead of valuefield.

      Use the following code to build your column:

      code language-none 
      valueformat=HTML
textmode=true
type=iterate
listdelimiter=<p>
displayname=Tasks Names - Primary Assignee
listmethod=nested(tasks).lists
valueexpression=CONCAT({name},' - ',{assignedTo}.{name})

  13. The following column displays in the project report, listing all tasks in each project alongside their primary assignees:

  14. Click Save.

  15. (Optional) Continue editing the report.

    Or

    Click Save + Close to save the report.

Understand the lines of a collection View in Text Mode

The lines in a text mode view for a collection are outlined in the following table:

Sample Line
Description
valueformat=HTML
You can use various values for this line, but we recommend that the valueformat for a collection list should be HTML.
textmode=true
This line indicates that the column has been configured using text mode. If you remove this line, Workfront adds it back by default.
type=iterate
The type of a list is always iterate, when building a view.
listdelimiter=<p>

This is the delimiter which is used to separate the values in your list.
We recommend to use <p> which adds a line break between the values.

You can also use the following:

&zwj; (zero-width joiner). The values of the collection have no separation between them.
, =comma separator. The values of the collection are separated by a comma followed by no space.
/ = slash separator. The values of the collection are separated by a slash.
- = dash separator. The values of the collection are separated by a dash.
Leaving this line empty adds a comma followed by a space between the values of the collection, by default.

displayname=Column Name
Replace Column Name with the actual name of your new column.
listmethod=nested(collection object name).list

This line defines the collection you are referencing.

Replace collection object name with the name of the object you are referencing in your collection, as it appears in the API Explorer. This value is typically the plural form of the collection object name.

valuefield=collection object field

This line defines what field you are referencing from the collection object.

collection object field  with the name of the field of the object you are referencing in your collection, as it appears in the API Explorer.

You can replace this line with:

valueexpression=calculated collection object field/ fields

Using valueexpression, you can  display a calculated custom expression in the column.

For more information about how to format valueexpression lines, see Text mode syntax overview.

Limitations of a collection View limitations-of-a-collection-view

Consider the following limitations when you are building a collection view:

  • You cannot control the order in which collection data is displayed.
  • You cannot apply conditional formatting to a collection view.
  • You cannot make an object in a collection a clickable link.
  • You cannot build a collection view of another collection.
    For example, you cannot display all the assignees on each task in a project report. You can only display the primary assignee on each task in a project view.

Reference a collection in the Filter of a report reference-a-collection-in-the-filter-of-a-report

You can reference a collection of objects in the filter of a report, to filter for the attributes of objects associated with the object of the report.

For example, you can filter for task or issue information in a project report by using a reference to the attributes of tasks or issues on the project in the filter statement.

To add a reference to a collection in a report filter:

  1. Click the Main menu , then click Reports.

  2. Click New Report.

  3. Select the object of your report.

  4. Navigate away from your report, and using the API Explorer, determine what collections are available for the object you selected for your report.

    For more information about selecting the object of your collection, see the section Find collection objects and their fields in the API Explorer in this article.

    Make a note of what the name of the object for the collection is.

  5. Using the API Explorer, go to the list of fields for the object you want to display in the collection.

    For more information about finding the fields of the object of your collection, see the section Find collection objects and their fields in the API Explorer in this article.

    Make a note of the field you want to display in the collection.

  6. Navigate back to your report, and in the Filters tab, click Switch to Text Mode.

  7. In the Set Filter Rules for your Report area, paste the following code:

    code language-none 
    collection object name:collection object field=collection object value
collection object name:collection object field_Mod=value of the modifier

  8. Replace collection object name with the name of your collection object as it appears in the API Explorer. This value is typically the plural form of the collection object name.

  9. Replace collection object field with the name of the field of your collection object in, as it appears in the API Explorer.

  10. Replace collection object value with the value of the collection object as it appears in Workfront.

  11. Replace value of the modifier with a valid modifier.

    For a list of modifiers, see Filter and condition modifiers.
    For example, to build a project report that displays only projects with tasks that have “Marketing” in their name, use the following code:

    code language-none 
    tasks:name=Marketing
tasks:name_Mod=cicontains

    This report only displays projects which have at least one task that has the word “marketing” in their name.

  12. To filter for the name of an issue, use the following code:

    code language-none 
    issues:name=Marketing
issues:name_Mod=cicontains
    note tip
    TIP
    Notice that you must use issues for the collection object name, instead of optask which is how issues appear in the API Explorer.

  13. Click Done.

  14. (Optional) Continue editing the report.

    Or

    Click Save + Close to save the report.

Reference a collection in the custom prompt of a report reference-a-collection-in-the-custom-prompt-of-a-report

You can reference a collection of objects in the custom prompt of a report, to filter the results of the report for the attributes of objects associated with the object of the report.

For example, you can prompt for task information in a project report by using a reference to the attributes of tasks on the project in the custom prompt of the report.

NOTE
You cannot reference collections in a standard prompt.

A custom prompt is a custom filter where the statements are joined by ampersand symbols. We recommend that you build your statement in a filter, first, then join the lines of the statements with ampersands.

For more information about building a filter statement with a collection reference, see the section Reference a collection in the Filter of a report in this article.

To add a reference to a collection in the custom prompt of a report:

  1. Click the Main menu , then click  Reports.

  2. New Report.

  3. Select the object of your report.

  4. Build a filter with a collection reference as described in the section Reference a collection in the Filter of a report in this article.

  5. Click Report Settings.

  6. Click Report Prompts.

  7. Click Add Prompt.

  8. Click Custom Prompt.

  9. Specify the name of the prompt in the  Field****name field.

  10. Specify a Dropdown Item Label.

  11. Specify the following in the Condition field:

    code language-none 
    collection object name:collection object field_Mod=value of the modifier

  12. (Optional) Specify if this choice is displayed by default in the prompt.

  13. collection object name  with the name of your collection object as it appears in the API Explorer. This value is typically the plural form of the collection object name.

  14. collection object field  with the name of the field of your collection object, as it appears in the API Explorer.

  15. collection object value  with the value of the collection object as it appears in Workfront.

    For example, if you are filtering for projects in which the name of the task contains “Marketing”, replace  collection object value ɾٳ marketing.

  16. value of the modifier  with a valid modifier.

    For a list of modifiers, see  Filter and condition modifiers.

    Example: For example, to build a project report with a custom prompt where you want to display only projects that have at least one task assigned to a specific user, use the code below:

    code language-none 
    tasks:assignedToID=57cf1b7a000077c9f02f66cb09c8f86c&tasks:assignedToID_Mod=in

    This generates a report where all the projects listed have at least one task assigned to the user whose GUID is 57cf1b7a000077c9f02f66cb09c8f86c.

    note note
    NOTE
    You cannot reference the name of the primary assignee (“Assigned To” field) of a task, according to the API Explorer. You can only reference the ID of the primary assignee.

    For example, to filter for any projects where any of the project issues are assigned to a specific user use the following code for your custom prompt:

    code language-none 
    issues:assignedToID=57cf1b7a000077c9f02f66cb09c8f86c&issues:assignedToID_Mod=in

    This generates a report where all the projects listed have at least one issue assigned to the user whose GUID is 57cf1b7a000077c9f02f66cb09c8f86c.

    note note
    NOTE
    Notice that you must use issues for the collection object name. The API Explorer  does not offer a collection object name for issues at this time.

  17. Done.

  18. (Optional) Continue editing the report.

    Or

    Save + Close to save the report.

recommendation-more-help
5f00cc6b-2202-40d6-bcd0-3ee0c2316b43