Hey Brian,
It wasn't to hard after a little thinking. The report is what we use to give projected dates that work off of one ending date. This is for each department to have the tasks completed on time. The only actual field on the report that is tied to the the sql database is my ending date. We have preset so many days back from the ending date for each department. These are consecutive dates, so once one department is finished the project is moved to the next department and so on.
Here is what i did. I placed my ending date on the report as the last date in the row. Then i placed five unbound fields on the report representing each departments cut off date for completion. I used the formula editor to create the crystal syntax formula for each field.
Here is the code i used on the formula:
DateVar EstDeliveryDate := {Project.ProjectedDeliveryDate};
DateVar EstCuttOffDate := {Project.ProjectedDeliveryDate} - 10; //this could be any number you choose.
NumberVar ActualDays := DateDiff("d", EstDeliveryDate, EstCuttOffDate) - DateDiff("ww", EstDeliveryDate, EstCuttOffDate, crsaturday) - DateDiff("ww", EstDeliveryDate, EstCuttOffDate, crsunday);
{Project.ProjectedDeliveryDate} - 10 + NumberVar + 10
The last calculation looks odd but it works, I have run the report and checked it against an actual callendar and it comes out right everytime. I imagine there is probably room for improvement.
I placed this formula inside of each of my unbound date fields and just modified the actual number of days for each field going backwards adding so many more days to each number 10 -> 20 -> 26 -> 52 -> 58.
I will check in the morning to make sure of the exact wording, but this is the formula i used.
The only thing that i have not included yet is holidays which, we work all but Christmas so it really doesn't matter.
Hope this helps anyone who might need it.
Edited by JohnS. - 16 May 2007 at 9:11pm