Journal Entry Report - Task Status and Assignment Status

[J_Mas]

I'm building a Journal Entry report with the following purpose: Whenever a specific Status is applied to a Task (let's call it "PM Complete"), I want to see all the Assignee's of the Task as well as their Assignment Statuses AT THE TIME that the Task's Status was changed.

So far I've built a report that pulls in any Task where the Status was changed to "PM Complete." (utilizing the "Old Text Value" field). It also lists all Assignees and their Assignment Statuses (utilizing the "Task >> Assignments And Status" field).

The problem with what I've built is that while the Task's Status shows me the Status at a particular point in time, the Assignment Status is showing me the CURRENT Assignment Statuses, and not what they were at that same point in time.

For example, in my screenshots below, when the Task's Status was changed to "PM Complete" the Assignment Statuses were "Done", "Requested", and "Requested", and would initially show those Statuses if I ran the report at that time. However, if someone (e.g. Bob Sacamano) went into his Assignment AFTER the Task's status was changed, and clicked "Done with My Part", my report would now show the Assignment Statuses as "Done", "Done", and "Requested", which is the CURRENT Assignment Statuses and not what they were at the time of the Task's Status change.

Is it possible to get the Assignment Statuses at the time of the Task's Status change to "PM Complete"? I'm hoping that I won't need to build a separate assignment report to join to my Journal Report in Excel.

Report Example: BeforeReport Example: After

[skyehansen · Accepted solution]

I don't think this type of historical reporting is available in current workfront reporting. Have you considered external reporting sources such as Power BI? This ask seems like something our analytics team would fulfill.

[J_Mas]

Thanks @skyehansen , I think you're correct that this isn't currently possible in a Journal Report (comparing the historical value(s) of more than one field).

I did consider doing this reporting outside of Workfront, however my users needed this report added to a dashboard in Workfront.

My solution, at least for the time being, is that I created two new task-level fields. The first field will "hard-code" a true/false value if the task status is ever changed to "PM Complete". The second field "hard-codes" the date/time of that change.

Then on the journal report, I'm able to compare that hard-coded task-level date/time to the date/time of the assignment-level status. This worked for me in our Sandbox so I'll be rolling it out in our Prod environment soon.