How to Report on the most recent project status?

[Darrion Burges1]

Hello experts, 

We were recently given a requirement to capture a list of project statuses. However, the client only wants the most recent status for each project. 

I know that count distinct would bring this down to only one, but how can I configure this to show only the most recent status? is there a way to change which status it shows? 

Would I need to do this in a script or can it be done with out of the box reporting? 

Thank you for your help and expertise. 

[Adam Stout · Accepted solution]

There are several questions similar to this if you do a little searching.  The solution I use is to add a new field to pm_project called Latest Status (or something similar) that is a reference to the status report table.  Then add a BR on insert or update of status (when project or date changes) to validate if the new status report is the latest or not.  If it is, set that to the "Latest Status" field on pm_project.  With this field, reports are VERY easy and you can identify projects missing reports or with reports move than x old.

[Darrion Burges1]

This is a good long-term solution. 

However, my team does not have edit rights in the pm_project table, only reporting and PA. Pushing a new column through for a single report would be cumbersome in my organization. 

Is there an easy way to narrow it down to the latest project status if we are looking at the project status table? 

I will bring your solution to leadership if there is no other way, but it would just delay delivery of the initial product. 

Thanks as always for your time and expertise Adam!