Formula help and Indicators not showing - MS Project Server 2007

D

Dylan

Question 1:
I'm trying to create an indicator on Project Center that shows whether each
project is over/under budget. I created an Enterprise Project field in
Project Server and used the following formula...

Switch([Cost Variance]=[Cost],"No Baseline",abs([Cost Variance]/[Baseline
Cost])<=0.1,"Green",abs([Cost Variance]/[Baseline Cost])>0.1 And abs([Cost
Variance]/[Baseline Cost])<=0.2,"Yellow",abs([Cost Variance]/[Baseline
Cost])>0.2,"Red")

This works for everything except the "No Baseline" in MS Project
Professional 2007. How can I get the "No Baseline" to work? The following
formula does present "No Baseline" correctly...

IIf([Cost Variance]=[Cost],"No Baseline",abs([Cost Variance]/[Baseline Cost]))

Question 2:
The indicators in Project Center are not showing correctly. The words
"Green", "Yellow", "Red" are shown instead. I've tried reopening the projects
and resetting the baselines and republishing them to no avail. How can I get
them to present correctly?

Your assistance is greatly appreciated.
 
D

Dylan

I've been able to get the indicators working now (Question 2).
Apparently, I had to check the "Data" radio button in "Values to Display". I
then reopened each project and hit F9 to recalculate them and saved/published
them.

This is exactly the opposite of how it should work and of how it DOES work
with my custom enterprise schedule field. Anyone know why there could be a
discrepancy here?

I still haven't figured out Question 1. Please help if you can.
 
D

Dylan

Thank you for your response.

If I understand it correctly, Cost Variance is the difference between the
Baseline Cost and Cost fields. I found that it does work as the IIF statement
shows. For some reason it doesn't work in the SWITCH formula.

I did try other fields to check for baseline, including Baseline Start and
Baseline Finish, but those didn't work here either...

Any other possibilities for this you can think of?

Paul Conroy said:
You're forumla is not checking for a baseline

change the expression [cost variane]=[cost],"no baseline" to [baseline
cost]=0,"No Baseline"
--
Please rate this post if it has helped

http://www.fundraiseonline.co.nz/TheProjectServerGuru/

http://theprojectserverguru.spaces.live.com


Dylan said:
Question 1:
I'm trying to create an indicator on Project Center that shows whether each
project is over/under budget. I created an Enterprise Project field in
Project Server and used the following formula...

Switch([Cost Variance]=[Cost],"No Baseline",abs([Cost Variance]/[Baseline
Cost])<=0.1,"Green",abs([Cost Variance]/[Baseline Cost])>0.1 And abs([Cost
Variance]/[Baseline Cost])<=0.2,"Yellow",abs([Cost Variance]/[Baseline
Cost])>0.2,"Red")

This works for everything except the "No Baseline" in MS Project
Professional 2007. How can I get the "No Baseline" to work? The following
formula does present "No Baseline" correctly...

IIf([Cost Variance]=[Cost],"No Baseline",abs([Cost Variance]/[Baseline Cost]))

Question 2:
The indicators in Project Center are not showing correctly. The words
"Green", "Yellow", "Red" are shown instead. I've tried reopening the projects
and resetting the baselines and republishing them to no avail. How can I get
them to present correctly?

Your assistance is greatly appreciated.
 
D

Dylan

The formula I'm using for Schedule status is here...

Switch([% Complete]=100,"Complete",(([% Complete]<100) And
([Finish]<Date())),"Overdue",(([Baseline Start]=ProjDateValue("NA")) Or
([Baseline Finish]=ProjDateValue("NA"))),"No Baseline",[Finish
Variance]<=(ProjDateDiff([Project Start],[Project
Finish])*0.1),"Green",[Finish Variance]>(ProjDateDiff([Project
Start],[Project Finish])*0.1) And [Finish Variance]<=(ProjDateDiff([Project
Start],[Project Finish])*0.2),"Yellow",[Finish
Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.2),"Red")

It seems to work fine for "No Baseline"

-Dylan

Dylan said:
Thank you for your response.

If I understand it correctly, Cost Variance is the difference between the
Baseline Cost and Cost fields. I found that it does work as the IIF statement
shows. For some reason it doesn't work in the SWITCH formula.

I did try other fields to check for baseline, including Baseline Start and
Baseline Finish, but those didn't work here either...

Any other possibilities for this you can think of?

Paul Conroy said:
You're forumla is not checking for a baseline

change the expression [cost variane]=[cost],"no baseline" to [baseline
cost]=0,"No Baseline"
--
Please rate this post if it has helped

http://www.fundraiseonline.co.nz/TheProjectServerGuru/

http://theprojectserverguru.spaces.live.com


Dylan said:
Question 1:
I'm trying to create an indicator on Project Center that shows whether each
project is over/under budget. I created an Enterprise Project field in
Project Server and used the following formula...

Switch([Cost Variance]=[Cost],"No Baseline",abs([Cost Variance]/[Baseline
Cost])<=0.1,"Green",abs([Cost Variance]/[Baseline Cost])>0.1 And abs([Cost
Variance]/[Baseline Cost])<=0.2,"Yellow",abs([Cost Variance]/[Baseline
Cost])>0.2,"Red")

This works for everything except the "No Baseline" in MS Project
Professional 2007. How can I get the "No Baseline" to work? The following
formula does present "No Baseline" correctly...

IIf([Cost Variance]=[Cost],"No Baseline",abs([Cost Variance]/[Baseline Cost]))

Question 2:
The indicators in Project Center are not showing correctly. The words
"Green", "Yellow", "Red" are shown instead. I've tried reopening the projects
and resetting the baselines and republishing them to no avail. How can I get
them to present correctly?

Your assistance is greatly appreciated.
 
D

Dylan

I finally figured it out. The following formula works perfectly.

Switch([Baseline Cost]=0,"No Baseline",[Baseline Cost]>0 And abs([Cost
Variance]/[Baseline Cost])<=0.1,"Green",[Baseline Cost]>0 And abs([Cost
Variance]/[Baseline Cost])>0.1 And abs([Cost Variance]/[Baseline
Cost])<=0.2,"Yellow",[Baseline Cost]>0 And abs([Cost Variance]/[Baseline
Cost])>0.2,"Red")

Also, the indicators are showing now too. I had to set the criteria for the
Project Summary and wait until it processed. Apparently, there is some delay
in processing custom enterprise field changes on Project Server 2007.

Hope this post is helpful for someone.
 
F

Frank Trivoluzzi

I tried your formula, but in project center the data field is empty, is there an email i can reach you on for advice?

Thanks,
Frank
Question 1:
I'm trying to create an indicator on Project Center that shows whether each
project is over/under budget. I created an Enterprise Project field in
Project Server and used the following formula...

Switch([Cost Variance]=[Cost],"No Baseline",abs([Cost Variance]/[Baseline
Cost])<=0.1,"Green",abs([Cost Variance]/[Baseline Cost])>0.1 And abs([Cost
Variance]/[Baseline Cost])<=0.2,"Yellow",abs([Cost Variance]/[Baseline
Cost])>0.2,"Red")

This works for everything except the "No Baseline" in MS Project
Professional 2007. How can I get the "No Baseline" to work? The following
formula does present "No Baseline" correctly...

IIf([Cost Variance]=[Cost],"No Baseline",abs([Cost Variance]/[Baseline Cost]))

Question 2:
The indicators in Project Center are not showing correctly. The words
"Green", "Yellow", "Red" are shown instead. I've tried reopening the projects
and resetting the baselines and republishing them to no avail. How can I get
them to present correctly?

Your assistance is greatly appreciated.
On Tuesday, July 29, 2008 5:44 PM Dyla wrote:
I've been able to get the indicators working now (Question 2).
Apparently, I had to check the "Data" radio button in "Values to Display". I
then reopened each project and hit F9 to recalculate them and saved/published
them.

This is exactly the opposite of how it should work and of how it DOES work
with my custom enterprise schedule field. Anyone know why there could be a
discrepancy here?

I still haven't figured out Question 1. Please help if you can.
On Wednesday, July 30, 2008 1:57 AM pconroyATtheprojectserverguruDOTcoDOTnz wrote:
You're forumla is not checking for a baseline

change the expression [cost variane]=[cost],"no baseline" to [baseline
cost]=0,"No Baseline"
--
Please rate this post if it has helped

http://www.fundraiseonline.co.nz/TheProjectServerGuru/

http://theprojectserverguru.spaces.live.com


"Dylan" wrote:
Switch([% Complete]=100,"Complete",(([% Complete]<100) And
([Finish]<Date())),"Overdue",(([Baseline Start]=ProjDateValue("NA")) Or
([Baseline Finish]=ProjDateValue("NA"))),"No Baseline",[Finish
Variance]<=(ProjDateDiff([Project Start],[Project
Finish])*0.1),"Green",[Finish Variance]>(ProjDateDiff([Project
Start],[Project Finish])*0.1) And [Finish Variance]<=(ProjDateDiff([Project
Start],[Project Finish])*0.2),"Yellow",[Finish
Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.2),"Red")

It seems to work fine for "No Baseline"

-Dylan

"Dylan" wrote:
On Friday, August 01, 2008 7:22 PM Dyla wrote:
I finally figured it out. The following formula works perfectly.

Switch([Baseline Cost]=0,"No Baseline",[Baseline Cost]>0 And abs([Cost
Variance]/[Baseline Cost])<=0.1,"Green",[Baseline Cost]>0 And abs([Cost
Variance]/[Baseline Cost])>0.1 And abs([Cost Variance]/[Baseline
Cost])<=0.2,"Yellow",[Baseline Cost]>0 And abs([Cost Variance]/[Baseline
Cost])>0.2,"Red")

Also, the indicators are showing now too. I had to set the criteria for the
Project Summary and wait until it processed. Apparently, there is some delay
in processing custom enterprise field changes on Project Server 2007.

Hope this post is helpful for someone.

"Dylan" wrote:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top