Reference collections in a report

Building a report in Adobe 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

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

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.

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:

• Text Mode overview
• Overview of common uses for Text Mode.
• Text mode syntax overview

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

   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

   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 Filter of a report
• Reference a collection in the custom prompt 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.

• Add a collection column in a report View
• Understand the lines of a collection View in Text Mode
• Limitations of a collection 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:

   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 namewith 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 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:

      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:

      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:

      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:

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

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:

   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:

    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:

    issues:name=Marketing
    issues:name_Mod=cicontains
    

    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

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.

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. Click 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:

    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. 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.

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

15. 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.

16. Replace 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:

    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

    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:

    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

    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. Click Done.

18. (Optional) Continue editing the report.

    Or

    Click Save + Close to save the report.

Business.Adobe.com resources