Ask a new question

Summary Status in MS Project and PWA Home page

I added a field 'Text30' to each project, 27 in all, in PWA 2013 (MS Project Server 2013), labeled it 'Health Status'.  The formula interrogates each row for an appropriate status - see formula below.  I added Graphical Indicators - colored circles, flags, etc. for visualization.

The formula works well on the non-summary rows.  See attachment.  I use the 'inherit criteria' checkboxes on  the Graphical Indictors dialog as I cannot seem to get a formula that will work as I 'want it to work'.  See question below.

Question:

How can I change the formula so that for any project its Summary & Project rows behave as follows?

  • If any non-summary row for a Summary or Project row is 'late', then that Summary and/or Project row is 'late'?

Currently, the formula below finds that the Summary and Project rows are 'In Progress' because those individual rows pass the formula criteria for 'In Progress', yet the non-summary rows (indented under a Summary / Project row) are be 'late'.

Formula:

IIf([Duration]=0,"Milestone", 

IIf([% Complete]=100,"Complete", 

IIf([Priority]=900,"On Hold",IIf(((([Current Date]+6)>=[Scheduled Finish]) And ([% Complete]<50)),"Late", 

IIf((([Current Date]>[Scheduled Finish]) And ([% Complete]<>100)),"Late", 

IIf(((([Current Date]+6)>=[Scheduled Finish]) And ([% Complete]<=75)),"At Risk","In Progress"))))))

Setup - Graphical Indicators

I am going for the quick answer here, and say that you can't use a formula which has to get data from one row to use in another row. MSP is not an EXCEL spreadhseet (thankfully), except you can use VBA to do it.

I don't want to spoil your formula writing exercise, but are you need/want to do this? MSP already has easy ways to measure task status, such a sthe status field, slipping tasks filter etc, built in. Your multiple nested IIFs are bound to become a sticky problem, and you seem to be re-inventing the definition of "progress".

Trevor Rabey
PERFECT PROJECT PLANNING

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated March 19, 2024 Views 327 Applies to: