Does anyone have any idea on how I can acheive this?

 

FYI I am using Crystal 10 & my formula is listed below (with the list of holidays cut down)...

 

 

// Start Date Time and Date

Local DateTimeVar dt1:= {@DateFSComplete}; //Date Time

Local DateTimeVar d1:= CDate({@DateFSComplete}); //Date

 

// End Date Time & Date

Local DateTimeVar dt2:= {@DateInvoiced}; // Date Time

Local DateTimeVar d2:= CDate({@DateInvoiced}); //Date

 

//Convert Days to Seconds

Local NumberVar ds:= (Date(dt2) - Date(dt1))*86400;

 

//Convert Hours to Seconds

Local NumberVar hs:= (Hour(dt2) - Hour(dt1))*3600;

 

//Convert Minuites to Seconds

Local NumberVar ms:= (minute(dt2) - Minute(dt1))*60;

 

//Seconds

Local NumberVar ss:= Second(dt2) - Second(dt1);

 

//Total Seconds

Local NumberVar ts:= ds+hs+ms+ss;

 

//Extract total days from total  seconds

ds:= Truncate(ts/86400);

 

//Extract total hours from total seconds

hs:= Truncate((Remainder(ts,86400))/3600);

 

//Extract total minuites from total seconds

ms:= Truncate((Remainder(ts,3600))/60);

 

//Extract remaining seconds

ss:= Truncate(Remainder(ts,60));

 

//Remove Weekends

Local NumberVar Sat:= 0;

Local NumberVar Sun:= 0;

Local NumberVar WD:= 0;

Sat := DateDiff ("ww", d1, d2, crSaturday) ;

Sun := DateDiff ("ww", d1, d2, crSunday);

WD := Sat + Sun;

 

// *** Remove Bank Holidays ***

 

// Set Bank Holidays to 0

Local Numbervar bankhols := 0;

 

//BANK HOLIDAYS HERE

 

//Loads of bank holidays removed

 

//2008 BANK HOLIDAYS

 

//New Years Day (01/01/2008)

if date(2008,01,01) in d1 to d2 then  bankhols := bankhols + 1;

//Good Friday (21/04/2008)

if date(2008,03,21) in d1 to d2 then  bankhols := bankhols + 1;

//Easter Monday (24/04/2008)

if date(2008,03,24) in d1 to d2 then  bankhols := bankhols + 1;

//May Bank Holiday (05/05/2008)

if date(2008,05,05) in d1 to d2 then  bankhols := bankhols + 1;

//Spring Bank Holiday (26/05/2008)

if date(2008,05,26) in d1 to d2 then  bankhols := bankhols + 1;

//August Bank Holiday (25/08/2008)

if date(2008,08,25) in d1 to d2 then  bankhols := bankhols + 1;

//Christmas Day (25/12/2008)

if date(2008,12,25) in d1 to d2 then  bankhols := bankhols + 1;

//Boxing Day (26/12/2008)

if date(2008,12,26) in d1 to d2 then  bankhols := bankhols + 1;

 

//Calculate days (days - holidays - Weekends) - Note Global variable used here to alllow other formulas to perform calcaulation on figure in FD (cont.)

//If more than one of these formulas, give each final days values a unique name

Global NumberVar FD;

FD:= (DS - bankhols - WD);

 

StringVar Display:= ToText(FD,0,"") + ":" + ToText(hs,0,"") + ":" + ToText(ms,0,"") + ":" + ToText(ss,0,"");

 

Display

 

My first thought besides Stored Procs (because you localize it there and use a function or another stored proc to get the bank holidays) was a function, but it would probably need to be in the Repository, but I have never used this as I can't seem to figure out how to get access to it. 

 

Last idea, create a subreport that instead of fetching any data, just gets passed the dates and returns the result of the formula, then when ever you need to the calculation, you insert the subreport.  This gives you central location to maintain the formula, and all calculations are the same.  I can endorse this solution as it doesn't hit the database again (why I tend to dislike subreports)...so the dates are the values that 'link' the subreport to the main report, and either the subreport can display the value and/or return it as a shared variable.  If returned as a shared variable, you could access it in the existing formulas and the rest of the report should work as before.

HTH