How to eliminate duplicate records in a Workfront report | Community
Skip to main content
B
BrianCo2
May 26, 2016
Question

How to eliminate duplicate records in a Workfront report

  • May 26, 2016
  • 7 replies
  • 1931 views
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
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

7 replies

E
EricLu2
Level 10
May 26, 2016
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
B
BrianCo2Author
May 26, 2016
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
D
DavidCo3
Level 10
May 27, 2016
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
Community Advisor
Doug_Den_Hoed_AtAppStoreCommunity Advisor
Community Advisor
July 1, 2016
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
D
DavidCo3
Level 10
July 4, 2016
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
Community Advisor
Doug_Den_Hoed_AtAppStoreCommunity Advisor
Community Advisor
July 4, 2016
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
    D
    DavidCo3
    Level 10
    July 4, 2016
    Brilliant - thanks Doug!
    Terms & ConditionsAccessibility statement

    Loading footer...