I have a crystal report which calculates the hours worked between two datetimes.
It works fine in Crystal, but we use a standalone program called Reporter (about which I know nothing !) to run reports and the report does NOT work from there. It gives me the following error;
_____________
There was a problem opening that report. Please contact your network
administrator. Error = 'Error in formula <TT_HourlyTurnTime>.
'
'
The remaining text does not appear to be part of the formula.'.
_____________
The really annoying thing is that it appears to work on everyone else's machine, but I cannot release it until I know what the problem is, just in case.
HELP !
The formula concerned is;
_________________
_________________
The @TT_SetupHolidayList is in the report header, but is suppressed. Frankly, I'd prefer to read the holiday info from the database as a totally seperate query to be run each time the report runs, but I couldn't work out how and didn't need to waste any more time on this !
_________________
_________________
It works fine in Crystal, but we use a standalone program called Reporter (about which I know nothing !) to run reports and the report does NOT work from there. It gives me the following error;
_____________
There was a problem opening that report. Please contact your network
administrator. Error = 'Error in formula <TT_HourlyTurnTime>.
'
'
The remaining text does not appear to be part of the formula.'.
_____________
The really annoying thing is that it appears to work on everyone else's machine, but I cannot release it until I know what the problem is, just in case.
HELP !
The formula concerned is;
_________________
Code:
EvaluateAfter ({@TT_SetupHolidayList});
EvaluateAfter ({@TT_StartDate});
EvaluateAfter ({@TT_EndDate});
// Function: TT_HourlyTurnTime
// Written by: Simon Holzman
// Date: 2 January 2003
// Amendments:
// 01/02/03 Sample amendment description
// This is the Hourly Turn Time Calculation.
// It calculates the number of working hours between two datetime values, excluding weekends, holidays and non-working hours.
// For the moment, it assumes that the working hours are always 8am to 6pm, Monday to Friday.
// If it is necessary to change these hours, they can either be changed explicitly here, or a function can be created to calculate them
// even making them different for different periods or days, if needed.
timeVar XTT_WorkDayStartTime := Time (08, 00, 00);
timeVar XTT_WorkDayEndTime := Time (18, 00, 00);
// The original version is in the Crystal Report "HourlyTurnTime".
// It is also used by the following Crystal Reports;
// dynreport:\Default\1\SampleReportName.rpt
// ALWAYS update this version and copy it to the other Crystal Reports listed so that it remains consistent.
// Include the paths and names of any reports that use it so that they can be maintained easily.
// All of the variables declared elsewhere but used by this function start with TT_
// All of the variables which are only used within this function start with XTT_
// The @TT_StartDate must be set to the Start DateTime of the Turn Time period to be calculated.
// The @TT_EndDate must be set to the End DateTime of the Turn Time period to be calculated.
// It also NEEDS the @TT_SetupHolidayList function which MUST be in the report header, although it can be suppressed so that it
// is not visible.
// The result of this function is a number giving the total working hours (including decimals where 0.5 = 30 minutes)
// Calculate the Hourly Turn Time version of the Start DateTime setting the start time to XTT_StartTime if it is on
// a weekend or a holiday.
global dateVar array TT_HolidayList;
datevar XTT_CheckDate := date({@TT_StartDate});
timevar XTT_CheckTime := time({@TT_StartDate});
datevar XTT_EndDate := date({@TT_EndDate});
timevar XTT_EndTime := time({@TT_EndDate});
numbervar XTT_WorkingHours := 0;
// If the Start Time is not within normal working hours, adjust it to the start of the next working period.
if XTT_CheckTime < XTT_WorkDayStartTime then
XTT_CheckTime := XTT_WorkDayStartTime;
if XTT_CheckTime > XTT_WorkDayEndTime then
(
XTT_CheckDate := XTT_CheckDate + 1;
XTT_CheckTime := XTT_WorkDayStartTime
);
// If the End Time is not within normal working hours, adjust it to the start of the next working period.
if XTT_EndTime < XTT_WorkDayStartTime then
XTT_EndTime := XTT_WorkDayStartTime;
if XTT_EndTime > XTT_WorkDayEndTime then
(
XTT_EndDate := XTT_EndDate + 1;
XTT_EndTime := XTT_WorkDayStartTime
);
// Check each day between the two dates and accumulate the hours worked.
while XTT_CheckDate < XTT_EndDate do
(
if not(DayOfWeek(XTT_CheckDate, crMonday) > 5) and not(XTT_CheckDate in TT_HolidayList) then
XTT_WorkingHours := XTT_WorkingHours
+ (datediff('n', cdatetime(CurrentDate, XTT_CheckTime), cdatetime(CurrentDate, XTT_WorkDayEndTime)) / 60);
XTT_CheckDate := XTT_CheckDate + 1;
XTT_CheckTime := XTT_WorkDayStartTime
);
if ((DayOfWeek(XTT_CheckDate, crMonday) < 6) and not (XTT_CheckDate in TT_HolidayList)) then
XTT_WorkingHours := XTT_WorkingHours
+ (datediff('n', cdatetime(CurrentDate, XTT_CheckTime), cdatetime(CurrentDate, XTT_EndTime)) / 60);
// Output from the Formula ... Working Hours
XTT_WorkingHours;The @TT_SetupHolidayList is in the report header, but is suppressed. Frankly, I'd prefer to read the holiday info from the database as a totally seperate query to be run each time the report runs, but I couldn't work out how and didn't need to waste any more time on this !
_________________
Code:
// @TT_SetupHolidayList function
// This is used for the Hourly Turn Time Calculation in the @TT_HourlyTurnTime Function. See that for more information.
// This function is used to store the Holiday Dates and MUST be updated each year.
// It MUST be in the report header, although it can be suppressed so that it is not visible.
BeforeReadingRecords;
global dateVar array TT_HolidayList := [
Date(2000,1,1),
Date(2000,11,23),
Date(2000,11,24),
Date(2000,12,25),
Date(2001,1,1),
Date(2001,1,15),
Date(2001,1,19),
Date(2001,5,28),
Date(2001,7,4),
Date(2001,9,3),
Date(2001,11,22),
Date(2001,11,23),
Date(2001,12,25),
Date(2002,1,1),
Date(2002,1,21),
Date(2002,2,18),
Date(2002,5,27),
Date(2002,7,4),
Date(2002,9,2),
Date(2002,11,28),
Date(2002,11,29),
Date(2002,12,25),
Date(2003,1,1),
Date(2003,1,20),
Date(2003,2,17),
Date(2003,5,26),
Date(2003,7,4),
Date(2003,9,1),
Date(2003,11,27),
Date(2003,11,28),
Date(2003,12,25)
];
count(TT_HolidayList)
![[gorgeous]](/data/assets/smilies/gorgeous.gif "[gorgeous] [gorgeous]")