Follow
Referencing Collections in a Report

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 "Creating a Report."

Understanding 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 "Creating a Report."

For more information about building a report using the text mode interface, see "Understanding Text Mode."

Finding Collection Objects and Their Fields in the API Explorer

Not all collections can be reported on. For example, you can reference a list of tasks in a project report, but you cannot reference a list of issues in a project report. 

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. Navigate to the API Explorer.
  2. Find the object of your report. 
  3. Select the collections tab.
    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 navigate to the object of your collection.
    This opens the fields tab for the object of your collection.
    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. 

Referencing 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 collection from a project report, to show task information at the project level. 

Referencing 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 information in a project report, by building a collection column for tasks in the view of the report. You can display information about the tasks, like task names, dates, primary assignees, percent complete, etc in the collection view. The view will display the task information in a list format, with every line of the list representing information about a task. The list of tasks or task fields will appear on the same line as the project the tasks belong to.  
project_with_task_collection_simple_shot.png

Adding a Collection Column in a Report View

To add a collection column in a report view:

  1. Navigate to the Reporting area in the Global Navigation Bar.
  2. Select the Reports tab.
  3. Click New Report.
  4. Select the object of your report. 
  5. 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 "Finding Collection Objects and Their Fields in the API Explorer."
    Make a note of what the name of the object for the collection is. 
  6. Using the API Explorer, navigate 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 "Finding Collection Objects and Their Fields in the API Explorer."
    Make a note of what the name of the field you want to display in the collection is. 
  7. Navigate back to your report, and in the Columns (View) tab, click Add Column.
  8. Click Switch to Text Mode.
  9. Mouse over the dialog box, and click Click to edit text.
  10. 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:
    valueformat=HTML
    textmode=true
    type=iterate
    listdelimiter=<br>
    displayname=Column Name
    listmethod=nested(collection object name).lists
    valuefield=collection object field
  11. Replace Column Name with the name of your column in the displayname line.
  12. Replace collection object name with the name of your collection object in the listmethod line, as it appears in the API Explorer.
  13. 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
    NOTE 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 "Understanding Calculated Custom 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.
    Use the following code to build your column:
    valueformat=HTML
    textmode=true
    type=iterate
    listdelimiter=<br>
    displayname=Tasks Names
    listmethod=nested(tasks).lists
    valuefield=name
    The following column displays in the project report, listing all tasks in each project:
    project_report_with_task_collection.png

    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:
    valueformat=HTML
    textmode=true
    type=iterate
    listdelimiter=<br>
    displayname=Tasks Names - Primary Assignee
    listmethod=nested(tasks).lists
    valueexpression=CONCAT({name},' - ',{assignedTo}.{name})
    The following column displays in the project report, listing all tasks in each project alongside their primary assignees:
    project_report_with_task_and_assignee_collection_view.png

  14. Click Save.
  15. (Optional) Continue editing the report.
    Or
  16. Click Save + Close to save the report.

Understanding 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 The valueformat for a collection list should always be HTML
Other formats can be used as well. For more information about conditional formatting in a view, see "Understanding Conditional Formatting in Text Mode."
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=<br>

This is the delimiter which will be used to separate the values in your list.
We recommend to use <br> 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 will add 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. 
Replace 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 "Understanding Text Mode." 

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. 

Referencing 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 information in a project report by using a reference to the attributes of tasks on the project in the filter statement.  

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

  1. Navigate to the Reporting area in the Global Navigation Bar.
  2. Select the Reports tab.
  3. Click New Report.
  4. Select the object of your report. 
  5. 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 "Finding Collection Objects and Their Fields in the API Explorer."
    Make a note of what the name of the object for the collection is. 
  6. Using the API Explorer, navigate 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 "Finding Collection Objects and Their Fields in the API Explorer."
    Make a note of the field you want to display in the collection. 
  7. Navigate back to your report, and in the Filter tab, click Switch to Text Mode.
  8. In the Set Filter Rules for your Report area, paste the following code:
    collection object name:collection object field=collection object value
    collection object name:collection object field_Mod=value of the modifier
  9. 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.
  10. Replace collection object field with the name of the field of your collection object in, as it appears in the API Explorer
  11. Replace collection object value with the value of the collection object as it appears in Workfront.
  12. 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:
    tasks:name=Marketing
    tasks:name_Mod=cicontains
    This report will only display projects which have at least one task that has the word "marketing" in their name.
    marketing_only_tasks_in_project_report.png
  13. Click Done.
  14. (Optional) Continue editing the report.
    Or
  15. Click Save + Close to save the report.

Referencing 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 "Referencing Collections in Filter of a Report." 

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

  1. Navigate to the Reporting area in the Global Navigation Bar.
  2. Select the Reports tab.
  3. Click New Report.
  4. Select the object of your report. 
  5. Build a filter with a collection reference as described in "Referencing Collections in Filter of a Report." 
  6. Click Report Settings.
  7. Click Report Prompts.
  8. Click Add Prompt.
  9. Click Custom Prompt.
  10. Specify the name of the prompt in the Field name field.
  11. Specify a Dropdown Item Label.
  12. Specify the following in the Condition field:
    collection object name:collection object field=collection object value&collection object name:collection object field_Mod=value of the modifier
  13. (Optional) Specify if this choice is displayed by default in the prompt. 
  14. 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.
  15. Replace collection object field with the name of the field of your collection object, as it appears in the API Explorer
  16. Replace 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 with marketing.
  17. Replace value of the modifier with a valid modifier. 
    For a list of modifiers, see "Filter and Condition Modifiers."
    For example, you want to build a project report with a custom prompt where you want to display only projects that have at least one task assigned to Jennifer Campbell.
    Your code for the custom prompt is:
    tasks:assignedToID=57cf1b7a000077c9f02f66cb09c8f86c&tasks:assignedToID_Mod=in
    This will generate a report where all the projects listed have at least one task assigned to Jennifer Campbell (Jennifer's GUID is 57cf1b7a000077c9f02f66cb09c8f86c).
    project_report_with_collection_tasks.png
    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.
  18. Click Done.
  19. (Optional) Continue editing the report.
    Or
  20. Click Save + Close to save the report.