Wanting to bypass holidays in and weekends in formula.

[ttheobal]

My environment:
I am using Crystal Reports XI, connecting to Oracle 9i. We do not have Crystal reports Server or repository.

My Goal:
to write a report that calculates whether a call to the help desk was received Weekday Weekend or on a Holiday (Christmas, Canada Day, Labour Day etc.)

The Holidays must not be hard coded in the report and they don't exist in the database.

What I've done so far:

I've created a spreadsheet, and added it as a second datasource without linking to the rest of the database tables. The spreadsheet has one column called "Stat Holidays" that contains the holidays in MM/DD/YYYY format for 2006/2007

I've added an if statement to determine whether it's a weekday or not.

If Dayofweek({INCIDENT.DATE_LOGGED}) in [1,7]
Then " Weekend"
else "Business Day"

Now what I want to do is load my spreadsheet into an array and scan through the array for a stat holiday. this isn't working when I do it like this.

While CStr({Sheet1_.Stat Holidays}) <>"" and i<200 Do
(
StatDays :=CDate({Sheet1_.Stat Holidays});
i :=i+1;
);
Arraysize :=i;
for i:=1 to Arraysize do
(
If CDate({Sheet1_.Stat Holidays}) = CDate({INCIDENT.DATE_LOGGED}) then StatFound=1;
);

So now I want to try a custom Function, but I don't know how to make the function return a value I can't find any examples of the structure of the function. I stumbled onto this topic

http://www.tek-tips.com/viewthread.cfm?qid=1169574

It's helpful, only thing is this example has the dates of the holidays hard coded in the report, which is one of our constraints.

Any assistance is appreciated.

thanks.

 

[Madawc]

It seems to me that the constraint is impossible. Public holidays are arbitrary and different in different countries. It has to be hard-coded or else in the database.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ttheobal]

It appears that I've come to the same conclusion as you.
Thanks for your reply.