Subtract ProjectStartDate from current date/time
Hello all, I’m trying to create a simple SSRS report in Visual Studio 2008 that will show a stoplight on whether a schedule is ahead or behind where it is scheduled to be "today" based on % work complete. I currently use a custom field in Project Pro 2010 that calculates a stoplight for each individual task based on the duration and it works perfectly but want to move it into SSRS with the % work. I'm running into a problem with a formula where I need to subtract the ProjectStartDate.Value from now() and I'm getting a "Operator '-' is not defined for types 'Date' and 'Object'." Anyone have any suggestions on how I can alter this formula to get it to work? Formula that works as a custom field in Project Pro to calculate stoplight based on duration: IIf([Active] = 0, -1, IIf([Scheduled Duration] = 0, (IIf([% Complete] = 100, 2, IIf([Scheduled Finish] < Now() + 7 And [% Complete] < 100 And [% Complete] >= 80, 3, IIf([Scheduled Finish] < Now() + 7 And [% Complete] < 80, 4, 1)))), IIf(([% Complete] = 100 Or [% Complete] > 100 * (Abs((Now() - [Scheduled Start]) / ([Scheduled Finish] - [Scheduled Start])))), 2, (IIf([Scheduled Finish] > Now(), IIf([Scheduled Start] > Now(), 1, 3), 4))))) Formula in SSRS to calculate stoplight based on % work completed that does not work: =IIf(Fields!ProjectPercentWorkCompleted.Value=0,(IIf(Fields!ProjectPercentWorkCompleted.Value=100,2,IIf(Fields!ProjectFinishDate.Value<dateadd("d",7,now()) And Fields!ProjectPercentWorkCompleted.Value<100 And Fields!ProjectPercentWorkCompleted.Value>=80,3,IIf(Fields!ProjectFinishDate.Value<dateadd("d",7,now()) And Fields!ProjectPercentWorkCompleted.Value<80,4,1)))),IIf((Fields!ProjectPercentWorkCompleted.Value=100 Or Fields!ProjectPercentWorkCompleted.Value>100*(Abs((dateadd("d",0,now()-Fields!ProjectStartDate.Value)/(Fields!ProjectFinishDate.Value-Fields!ProjectStartDate.Value)))),2,(IIf(Fields!ProjectFinishDate.Value>dateadd("d",0,now(),IIf(Fields!ProjectStartDate.Value>dateadd("d",0,now(),1,3),4)))) I believe the part of the formula I've bolded is where the problem is but I'm not sure how to correct it. Thank you for any suggestions! Daren Johnson
October 18th, 2011 9:51am

You can wrap your date fields and the now() function in CDate() to make sure all objects are of type Date. Hope that helps! Cheers!Ryan
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2011 10:01am

Thank you for the note Ryan, I replaced the bold section above with: *(Abs(CDate((dateadd("d",0,now())-CDate(Fields!ProjectStartDate.Value))/(CDate(Fields!ProjectFinishDate.Value)-CDate(Fields!ProjectStartDate.Value)) ...and now I'm getting an error that says: Operator '/' is not defined for types 'System.TimeSpan' and 'System.TimeSpan' Full formula is now: =IIf(Fields!ProjectPercentWorkCompleted.Value=0,(IIf(Fields!ProjectPercentWorkCompleted.Value=100,2,IIf(Fields!ProjectFinishDate.Value<dateadd("d",7,now()) And Fields!ProjectPercentWorkCompleted.Value<100 And Fields!ProjectPercentWorkCompleted.Value>=80,3,IIf(Fields!ProjectFinishDate.Value<dateadd("d",7,now()) And Fields!ProjectPercentWorkCompleted.Value<80,4,1)))),IIf((Fields!ProjectPercentWorkCompleted.Value=100 Or Fields!ProjectPercentWorkCompleted.Value>100*(Abs(CDate((dateadd("d",0,now())-CDate(Fields!ProjectStartDate.Value))/(CDate(Fields!ProjectFinishDate.Value)-CDate(Fields!ProjectStartDate.Value))))),2,(IIf(Fields!ProjectFinishDate.Value>dateadd("d",0,now(),IIf(Fields!ProjectStartDate.Value>dateadd("d",0,now(),1,3),4)))) Daren Johnson
October 18th, 2011 10:21am

Yeah, I can repro that. Looks like you may need to use a custom assembly that can take the .ToString() vaue of the timespan, convert it to a number, divide, and then turn that back into a timespan through the timespan.Parse() method. For more on custom assemblies, check here: http://support.microsoft.com/kb/920769 Cheers!Ryan
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2011 10:37am

Ok, well... I officially just got lost. :( May have to look for another way to show project actual vs. planned % work complete. If there are other ways to do this in SSRS, I'd be more than willing to give them a try. Thanks! Daren Johnson
October 18th, 2011 10:52am

I believe I've figured out how to pull the calculation differently. Since the calculation is already made in a custom column in Project Pro, I can just pull in the value from that column into SRSS and assign a stoplight to the value. Much easier. :) Daren Johnson
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2011 1:48pm

Hi Daren, I've been searching forums and I believe what you wrote here is closest to what I'm trying to achieve. If you have time and could elaborate a little more, I'm trying to take the "RAG / Stoplight" formula and get it to pull the duration from a custom column within project so I can edit this value within the column and get the calculation rather than editing the formula for example... IIf([% Complete]<100 And (([Text1]="Hi" And DateDiff("d",date(),[Scheduled Finish])<=5) Or (DateDiff("d",[Scheduled Finish],date())>14)),"r",IIf([% Complete]<100 And ((DateDiff("d",[Scheduled Finish],date())<=14 And DateDiff("d",[Scheduled Finish],date())>5) Or ([Text1]="Med" And DateDiff("d",date(),[Scheduled Finish])<3)),"a","g")) I want to be able to edit the formula above to pull the duration from a custom column. Any help or thoughts would be greatly appreciated.
May 9th, 2012 12:21pm

Hey Dan, I'm not quite sure if I understand what you are trying to do. Are you referring to the "duration" as what is being calculated from the "DateDiff"? If so, couldn't you change the [Scheduled Finish] to a [Custom Date]? Daren Johnson
Free Windows Admin Tool Kit Click here and download it now
May 9th, 2012 3:20pm

Hi Daren, Thank you very much for your response! What I am trying to do is take these parts of the formula... IIf([% Complete]<100 And (([Text1]="Hi" And DateDiff("d",date(),[Scheduled Finish])<=**5)** (DateDiff("d",[Scheduled Finish],date())>**14))** Namely the 5 and 14 for date duration in all the lines of the formula where this occurs, and get the formula to pull these values/numbers from a custom column that I have created in my project in Project 2010, I am able to do this in excel but cat find a clear way to do it within project. So that I can change this value in my custom column rather than editing the formula itself to show the change and pull the calculation that way. Please let me know if I'm in the right direction or how I would go about accomplishing this. Thank you again for all your input. Original Formula IIf([% Complete]<100 And (([Text1]="Hi" And DateDiff("d",date(),[Scheduled Finish])<=5) Or (DateDiff("d",[Scheduled Finish],date())>14)),"r",IIf([% Complete]<100 And ((DateDiff("d",[Scheduled Finish],date())<=14 And DateDiff("d",[Scheduled Finish],date())>5) Or ([Text1]="Med" And DateDiff("d",date(),[Scheduled Finish])<3)),"a","g"))
May 11th, 2012 11:03am

Dan, If your variable could be set at the project level you could create a project custom number fields named "Days1" and "Days2". You could then insert those fields into your formula: IIf([% Complete]<100 And (([Text1]="Hi" And DateDiff("d",date(),[Scheduled Finish])<=**[Days1])** (DateDiff("d",[Scheduled Finish],date())>**[Days2]))** Would that get you what you're looking for?Daren Johnson
Free Windows Admin Tool Kit Click here and download it now
May 14th, 2012 12:02pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics