How to eliminate duplicate records in a Workfront report

[BrianCo2]

Hi, I have a report I am creating to display the number of resources assigned by specific project. Ultimately, I will use a chart to allow a quick view of this information. Currently, the report is returing a record per task assignment on a given project. As I'm only interested in seeing Project Name and Assigned Resource Name this effectively results in duplicate records in my report. What I really want to see is the unique combination of Project Name and Assigned Resource Name . This will allow me to easily construct the bar chart noted above. I'm looking for functionality within Workfront reporting that effectively emulate the SQL DISTINCT attribute which would allows a query to only return the result set that contains unique value combinations for all fields in the result set. In this case, this would simply be Project Name and Assigned Resource Name. As a note, I did originally start off with a Task Report and tried to use Assignments, but quickly discovered that field is just a formatted version of the individual Assignment records. So, I am currently basing my report on the Assignment object. Has anyone had any success doing this? If so, could you share the secret? Thanks! Brian

[EricLu2]

Hi: I haven’t figured out how to simulate the DISTINCT function. I usually group the report by the assignment owner name. That creates a summary by owner name. It isn’t exactly what you’re looking for, but I can make it work. I have to dump it out to Excel and play with it in some cases to get exactly what I need. There might be some text mode magic someone can work. I’m not that good with Text Mode. Let’s see. Eric

[BrianCo2]

Hey Eric, Unfortunately, I'm trying to include this in a dashboard, so hoping to be able to view the chart without manipulation. I wonder if this might be something I could with Text Mode within a filter. Thanks! Brian

[DavidCo3]

Hi Brian, I had a look around via the API explorer and can see that there is an object called ProjectUserRole (PTEAM) which has the following fields. I don't believe it is accessible within the UI and the API won't let me query it either unless I am doing something wrong. Someone else may have an idea if this is accessible. Customer ID customerID Project ID projectID Role ID roleID User ID userID

[Doug_Den_Hoed__AtAppStore]

Hi Brian, For your SQL DISTINCT challenge, consider adding a custom form to all your users with a calculated numeric field called "Report Count", with a formula of "1". Create an assignment matrix report, with Project Name, Assigned To Name, and the Assigned To's Report Count to the view, and specify "Min" as an aggregator (ensuring 1 comes back). Group veritcally by Project, and Horizontally by Assigned To Name to collapse to a grid of Projects vs Assignees, with a 1 where they intersect. Turn on totals, and the DISTINCT value you seek will be at the far right of each row. Chart it as a stacked bar to see it graphically. Regards, Doug

[DavidCo3]

Hi Doug, I've tried doing that but cannot group by Project in the Assignment Matrix report. In the groupings, I can only select from the following objects: Assignment Assigned To Assigned by Job Role Team Any ideas? Regards, David

[Doug_Den_Hoed__AtAppStore]

Hi David, Sometimes, when an option isn't available in the builder (as "Project" is not, on the Grouping of the matrix Assignment Report we are discussing), it might mean that the user interface isn't quite built out as extensively as we'd like, but we can still get what we need by using text mode. Here's what I just did: created a new Assignment report Grouped by something that IS available (Assigned To Name) Switched the grouping to textmode Manually replaced "assignedTo" with "project" throughout the grouping (like this) group.0.linkedname= project group.0.namekey=view.relatedcolumn group.0.namekeyargkey.0= project group.0.namekeyargkey.1=name group.0.valuefield= project :name group.0.valueformat=string textmode=true Confirmed that the Grouping worked Switched to Matrix mode Ignored the fact that my first grouping (on the rows) looked blank in the builder Recognized the fact that my first grouping still rendered in the preview as Project: Name Added my second grouping (on the columns) using the builder Ran the report Rejoiced: the matrix rendered with Project Name on the horizontal Hopefully this tip will get you going again. Good luck! Regards, Doug

[DavidCo3]

Brilliant - thanks Doug!