Crystal Reports Bank Holiday Calculation

Posted on 2010-01-07
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
    LVL 13

    Accepted Solution

    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 34

    Expert Comment

    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

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

    Author Comment

    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

    Gr8 Help! Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video discusses moving either the default database or any database to a new volume.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now