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
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
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
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