Report showing last status update for each project

[David Sloan]

I am trying to generate a report that shows any projects that do not have a status report from the last 7 days. I have created a database view called u_project_status_date with two view tables pm_project (prefix proj) and project_status (prefix rep). I marked the second table as a left join with the where clause proj_sys_id=rep_project, but this gives me all of the status reports for each project, and I want only the most recent status report for each project (or Status Date empty if applicable). How do I make my right table return only the most recent date for each project?

[amaradiswamy]

Hi,

You can apply the filter on database view also. If this is not helping, then what is your criteria to pull the most recent status reports ie., one day, one week, one month etc.,

[David Sloan]

I don't understand your answer. Perhaps my question was misunderstood. The only criterion for pulling the most recent status report is that it is a status report associated with the given project and is more recent than any other status reports for the given project. So if a project has received three status reports, I want to know the date of the most recent of the three. If that date is greater than a week ago or if there is no status report for a project, then the project should show up in the report. If that date is less than a week ago, the project should not show up in the report.

[David Sloan]

Perhaps if I can create a view of status reports in the last 7 days, I could use that as the second table in my left join and then display in my report only the results where there was no result in the right table, but I cannot even get that view to work. In other words, now I am simply trying to create a view that shows all status reports in the last 7 days. Here is what I am trying:

This is my only view table in this database view. I expected the database view to show all results where as_on is in the last 7 days, but it instead returns no results. If I could get this view to work, then I could create a second view that joins pm_project with this view. Then my report could filter out all results that have a status report, thus returning projects that don't have a status report in the last 7 days. But how do I get this simpler view to work?

[Adam Stout]

If you are looking for projects without a recent status report, just use a Related List condition.  This is one of the use cases, I use to demonstrate the power of them.  There is no need for a view here.

Create a report on "Project" since that is what we are trying to find.  Then use a Related List Condition to find those with no status report in the last 7 days (adjust as needed).