Advanced Reporting with Collections
Reporting in Adobe Workfront can often seem daunting. As a System Administrator, I was constantly bombarded with reporting requests from Project Managers and Resource Managers. In fact, over the course of my career, I’ve built hundreds of reports and learned some very valuable reporting fundamentals in the process.
One such fundamental is leveraging collections for advanced reporting. When crafting the “View” of a report in Workfront, regardless of object, you can use native and custom object fields, referenced objects and their native and custom fields, as well as collection objects and their fields.
So what are collections, you might ask? This blog will provide you everything you need to know to take advantage of this reporting technique.
Start with the Basics: Breaking Down Workfront Object Structure
First, let’s review an object’s structure in Workfront. An object is a type of record within Workfront’s database that consists of data. Examples of objects are Portfolios, Programs, Projects, Tasks, Issues, and Users.
Let’s use the Project object for our example. Projects can be broken down into fields, references, and collections. Fields refer to the native and custom fields associated with an object, in this case, a Project. Name, description, and reference number are all examples of Project fields. References refer to objects that connect to the Project via a one-to-one mapping. For example, a Project can be associated with a single Portfolio and Program. The Portfolio and Program objects are references to the Project object. Within a Project report, you can access referenced objects’ native and custom fields.
So, What’s a Collection and Why Should You Care?
Lastly, there are collections. Collection objects are associated to a primary object via a one-to-many relationship. For example, a Project can have hundreds of Tasks. Therefore, Tasks are associated to a Project as a collection. To see a comprehensive breakdown of objects and their fields, references, and collections, visit Adobe’s Workfront API Explorer here.
In the June 2023 Wizards of Workfront newsletter, I provided two custom syntax collections formulas that can be used in a Task View.
Let’s break the custom syntax apart and identify all the required elements of a collection.
The Core Elements of a Collection
To insert a collection column in a View, you’ll first need to navigate to the view you want to edit (you can also create a new View). In our example, we will modify an existing Task view.
- Select the edit pencil next to the View’s name
- Next, select “+ Add Column”
- Next, select “Switch to Text Mode”, which can be found in the upper right corner
Collections use the following fields: displayname, listdelimiter, listmethod, type, valuefield or valueexpression, valueformat, and textmode.
- The displayname field is the name of the column in the report
- The listdelimiter field is the specified delimiter that will separate all of the returned data
- Examples of listdelimiters include <p> and ,
- The listmethod field is the collection object associated to the primary object of the report/view
- The listmethod syntax is listmethod=nested(collection object).lists
- The type field is specified as iterate for collections
- The valuefield or valueexpression fields specify the data to be returned in the column;
- The valuefield field is the collection object’s native or custom field name or the collection object’s referenced native or custom field name
- The valueexpression field is a custom expression that leverages the collection object’s native fields, custom fields, referenced native fields, and/or referenced custom fields
- Valueexpressions use calculated data expressions in the formulas, which can be found here
- The valueformat field is the format of the valuefield/valueexpression’s output
- Examples include HTML, int, doubleAsString
- A comprehensive list of valueformats can be found here
- The textmode field isn’t necessary but it is always best practice to add the field and value at the end of your custom syntax
- You set the value of textmode equal to true (textmode=true)
Collection Reporting in Action
Now that we’ve broken down the fundamentals of collections, let’s review an example.
The name of our new column will be “Assignee Hours Remaining” because we’ve set our displayname equal to Assignee Hours Remaining.
The listdelimiter is equal to <p>, which classifies each value output as a unique paragraph. This will display each value from the valueexpression as a separate paragraph block, which makes reading the data easier.
The listmethod is set to nested(assignments).lists. The Assignments collection references all assignments for each Task and allows you to reference assignment users, job roles, planned hours, etc.
For all collections, we set the type equal to iterate so that the data displays a list of values associated to the Task object.
The valueexpression uses the COCNAT() function and within the CONCAT() function, we leverage the Assignments’ {assignedTo}.{name}, {workrequired}, and {actualWorkCompleted} fields.
- {assignedTo}.{name} is {reference object}.{reference object field name}
- {workRequired} and {actualWorkCompleted} are native fields to the Assignment object
- We divide the workRequired and actualWorkCompleted fields by 60 because Workfront displays the results as minutes rather than hours.
The valueformat is set to HTML, which is the most dynamic valueformat you can use. Other examples are int, doubleAsString, etc.
Conclusion
Voila! You now possess the skills to create unique and advanced custom columns within Views and Reports in Workfront.
Interested in more tips and tricks? Make sure you’re subscribed to the Wizards of Workfront Newsletter and connect with LeapPoint on LinkedIn.
Andy Koprowski, Integrations Architect
