Detecting when a project's enterprise outline code changes

[RugbyCoach]

I am trying to write a report that filters only for projects where a
specific enterprise outline code has been changed in the last 7 days.
As a bit of background, the enterprise code contains Project Phase.
The intent of the report is to identify projects that have changed
phase in the last week so that they can be discussed at the weekly IT
manager's meeting.

Anyway, the basic query is inserted below. I have looked at the tables
and can't find a column that I can use to identify changes to the
enterprise code assignment by date. There are a couple of promising
fields on msp_web_assignements called assn_start_date and
assn_finish_date, but they don't seem to work the way that I hoped.

Does anyone have any suggestions for what I can do to get what I need?

Thanks,
Simon

select
wp.proj_id
, wp.proj_name
, convert(varchar,moc.oc_name) AS ProjectPhase
from
msp_web_projects wp
join msp_web_assignments mwa on mwa.wproj_id = wp.wproj_id
join dbo.MSP_WEB_WORKGROUP_FIELDS f on mwa.wassn_id = f.wassn_id
join dbo.MSP_WEB_WORKGROUP_FIELDS_INFO fi on fi.field_id = f.field_id
join MSP_OUTLINE_CODES MOC ON f.INT_VAL = MOC.CODE_UID
join msp_projects mp on moc.proj_id = mp.proj_id
where fi.custfield_name = 'Project Phase'
and mp.proj_type = 2
order by ProjectPhase

 

[Reid McTaggart]

One approach would be to write a VBA macro for PPro. Add a "Phase Change"
button to the toolbar that invokes this new macro. The macro would handle
changing the value in the outline code and would also put the current date
into a custom Date field, say, the "Phase Change Date" field. Then your
report can query the date field. Require users to use the button when they
want to change the phase of a project.

An automated approach would involve coding every task to associate it with a
phase, and then using other custom fields to determine the project's phase as
well as the finish date of the previous phase. This approach requires a
pretty good knowledge of logic, formulas, and rollups, but would result in an
automatically calculated project level field with the date of the phase
change, which you then could query for your report.

 

[RugbyCoach]

Reid,
thanks for the suggestions. I take it that this means that there is no
timestamp that holds the information I am looking for.

Unfortunately, in our process many of the project phase transition
changes will be done from PWA rather than PPro, and the project
managers will resist the second option if it means more work for them
when creating their plans (and I don't think I have enough knowledge to
pull it off . Another approach that I am considering is saving a
snapshot of the results of the query into a table on a weekly or daily
basis and then writing the report off that.

Thanks in advance for any further suggestions.

Simon