Crystal Reports Bank Holiday Calculation

Posted on 2010-01-07
Medium Priority
Last Modified: 2012-06-27

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!!

Question by:player8
  • 2
  • 2
LVL 13

Accepted Solution

PCIIain earned 160 total points
ID: 26199259
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.)
LVL 35

Expert Comment

ID: 26206494
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.

LVL 13

Expert Comment

ID: 26208096
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 <= calendar.date and end_date >= calendar.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....

Author Comment

ID: 26282996
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!!!


Author Closing Comment

ID: 31673965
Gr8 Help! Thanks!

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question