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:
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:
-
Go to the API Explorer.
-
Find the object of your report.
-
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. -
Expand the object of your collection by clicking it.
-
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.
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:
-
Click the Main menu , then click Reports.
-
Click New Report.
-
Select the object of your report.
-
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. -
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.
-
Navigate back to your report, and in the Columns (View) tab, click Add Column.
-
Click Switch to Text Mode.
-
Mouse over the dialog box, and click Click to edit text.
-
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
-
Replace Column Name with the name of your column in the
displayname
line. -
Replace collection object name with the name of your collection object in the
listmethod
line, as it appears in the API Explorer. -
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})
-
-
The following column displays in the project report, listing all tasks in each project alongside their primary assignees:
-
Click Save.
-
(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:
valueformat=HTML
valueformat
for a collection list should be HTML.textmode=true
type=iterate
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:
‍
(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 Namelistmethod=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 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:
-
Click the Main menu , then click Reports.
-
Click New Report.
-
Select the object of your report.
-
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.
-
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.
-
Navigate back to your report, and in the Filters tab, click Switch to Text Mode.
-
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
-
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.
-
Replace collection object field with the name of the field of your collection object in, as it appears in the API Explorer.
-
Replace collection object value with the value of the collection object as it appears in Workfront.
-
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.
-
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 ofoptask
which is how issues appear in the API Explorer. -
Click Done.
-
(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.
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:
-
Click the Main menu , then click Reports.
-
Click New Report.
-
Select the object of your report.
-
Build a filter with a collection reference as described in the section Reference a collection in the Filter of a report in this article.
-
Click Report Settings.
-
Click Report Prompts.
-
Click Add Prompt.
-
Click Custom Prompt.
-
Specify the name of the prompt in the Field****name field.
-
Specify a Dropdown Item Label.
-
Specify the following in the Condition field:
code language-none collection object name:collection object field_Mod=value of the modifier
-
(Optional) Specify if this choice is displayed by default in the prompt.
-
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.
-
Replace collection object field with the name of the field of your collection object, as it appears in the API Explorer.
-
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.
-
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:
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. -
Click Done.
-
(Optional) Continue editing the report.
Or
Click Save + Close to save the report.