Create 2 queries:

·         Query1 = 2 coulmns, 1 filter

o    Projects.Projects.ProjectID (or project name if they are always unique)

o    Maximum(Presentation.project history.Updated on) – this grabs the last “updated on” record for each project. Should only have one record display per project

·         Query 1 filter will be:

o    [Presentation.project history.updated on] < _first_of_month(current_date) – defines that you only want records that occurred previous to the first day of the current month.  Make sure to either drag over a new instance of “updated_on” from the "insertable objects window" project history because if you use the field in the “data items” view, it will be filtering it twice.

·         Query2 = 3 columns, no filter

o    Presentation.project history.ProjectID

o    Presentation.project history. Updated On

o    Presentation.project history. Health

·         query3 Joins them together

o    Join on ProjectID “and” Updated On

·         query3

·         add the following columns from query 2

o    ProjectID

o    UpdatedOn

o    Health

Add Health from query 3 to the report.

When I added this to the report, I created a “master – detail” relationship between this query and the “master” query so we would be using the same project to determine the health.