Crystal Reports Bank Holiday Calculation


I have a crystal report formula for the lead-time calculation as follows:
Leadtime = dateout - datein  bank holiday, Saturday and Sunday.
Currently I use a custom function to check whether the particular date is bank holiday or not.
In the custom function, Ive hard coded all the bank holidays since 2008.

This year onwards, I do not want to manually update the bank holidays every year.
Instead I created a DB Table and check whether the particular date is bank holiday or not.
The Table Structure - Bank_holidays is as follows:

Date      Day      UK      Germany      France
1.1.10      Fr.      Yes      Yes      Yes
4.2.10      Fr.      Yes      Yes       
4.5.10      Mo.      Yes      Yes      Yes
5.1.10      Sa.             Yes      Yes
5.3.10      Mo.      Yes              
.....      .....      .......      .......      ...........

When I try to do this, when there are consecutive holidays then Im not able to subtract it. I.e. the field {Bank_holidays.Date} holds only one value during the formula evaluation. But what I want to do is: Consider one date value and compare it with all the value in the Date field of the - Bank_holidays table.
Is there any way in crystal reports that I access the whole DB table field and save it as an array?

Thanks a lot!!

Who is Participating?
Create a subreport in the report header of the main report. Use your bank holiday table.

Within this subreport create the following formula

shared dateVar array bankhols;
redim preserve bankhols [recordnumber ];
bankhols[RecordNumber] :=date({datefield});

Paint this formula in the details section

Suppress all the sections of the sub-report (do NOT suppress the subreport in the main report)

In the formula of the main report, use
shared dateVar array bankhols;
to gert an array of the dates.

(You may have to faff with multiple shared variables to get the dates for each country.)
PCIIain has given you the basics.  A few notes:

 It's often recommended (by people here) that shared variables be declared in the main report before being used in a subreport.  With that in mind, you may want to create a formula in the main report that declares your shared array(s) and put that in the main report header, then create a second report header section below that and put the subreport there.

 As PCIIain suggested, if you have different holidays for different countries, you'll probably want one shared array for each country.  There are other ways that you could handle it, but that's probably the simplest.

 Be aware that there are limits on where/how you can use shared variables, so having the holiday dates in shared variables may impose some limits on where you can check those dates.  But if you want to save a table in an array, a subreport and shared variables are pretty much your only option.  Hopefully the shared variables won't be a problem.  I just wanted to mention it.

As a second thought.

Sometimes it's easier to have a table which contains every date for a large period, (a calendar table) and just join directly to this. That gives you a record in the report for each day.

So assume the following.

task, start_date, end_date, value

join this to calendar (date, UK_working_day, FR_Working_day ...) ((working day as opposed to holiday, that way weekends can be included as false values))

select tasktable.*, calendar.*
from tasktable inner join calendar on start_date <= and end_date >=

This gives a row for each day. The issue with this is that the value field is repeated once for each day, so you have to be very careful with sums and other totals....
player8Author Commented:
Thank you very much for your valuable inputs.

I'm using the Bankholiday table to provide the Bankholidays since 2006. And I've used your above formula for the Bankholiday calculation and it works!!!

I'm not with my full testing yet, but I hope it works! In case I have some problems, I post you again.

I have no words to explain how intelligent you guys are!!! Thank you soo much!!!

player8Author Commented:
Gr8 Help! Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.