Bank holidays not working

[jehanzebn]

Dear all, I have been using Ken hamady's formula to calculate my working days. These days are working fine.

I then wanted to add bank holiday filteration as well however instead of using Array within report and keep updating it, I decided to use it directly frm my database field (Bank_holiday).

I was not sure how to link my bank holidays table with my report so I created a sub report added a Shared var formula and linked it with the main report.

The problem I am facing now is that when I use the shared array (subreport) my main report does not filter bank holidays. However if I use Ken's Bank holiday array with a formula within my report it works fine.

Any ideas why is it doing this?

Here is the setup I have done for the report.

Created Sub report with the name Bank Holidays
Inserted the link of sub report on Main Report header and suppressed it.
Within Sub-report I imported the table field bank_holidays
I then created a formula Bank_Holiday in the subreport with the following code:

whileprintingrecords;
shared datevar array Holidays;
numbervar i := i + 1;
numbervar j := count({bank_hol.holiday_date});

if i <= j then (
redim preserve Holidays[j+1];
Holidays[i] := {bank_hol.holiday_date}
);

This shared Datevar is shared with my main report NumofDays formula.

Here is the number of days formula

WhileprintingRecords;

Local DateVar Start := {order_header.date_entered};   // Starting Date
Local DateVar End := {order_progress.date_created};  // Ending Date
Local NumberVar Status:={order_progress.order_status}; //Order Status
Local NumberVar Weeks; // Number of weeks
Local NumberVar Days; // Number of days
Local Numbervar Hol:=0; //Number of holidays
Shared DateVar Array Holidays; // Bank holidays dates array
 
Weeks:=(Truncate(End - dayofweek(End) + 1 - (Start - dayofweek(Start) + 1))/7)*5;

Days:=DayOfWeek(end) - Dayofweek(Start) + 1
+ (if dayofweek(start) = 1 then -1 else 0) //adjust for starting on sunday:
+(if dayofweek(end) = 7 then -1 else 0); //adjust for ending on a saturday:
 
//Check for bank holidays
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek (Holidays[i]) in 2 to 6 and
Holidays[i] in Start to End then Hol:=Hol+1 );
If Status =77 then
Weeks + Days - Hol;

Crystal Reports 2008

I suspect that it is because of YYYY/MM/DD date format which is used in Crystal and in my database table the date format is DD/MM/YYYY. this is just what I think I am not sure though.

Could someone please help me with this issue.

Many thanks

Regards

Jehanzeb

 

[jehanzebn]

Ok I found the issue why it was not working.

I cannot use a formula from a sub report in the record selection of the main report as the main report will process before the sub report, therefore it filters the data of the main report before the shared array is passed back to the main report.

I am using the formula which is placed in the report with manual date entries, I have entered bank holidays up to year 2020 which is enough (I think) for a while.

This has sorted my problem of taking bank holidays out.

 

[Utreg]

I've got the same issue but can't use the (manual add) method from jehanzebn (I use the report in a Contact Center and Holidays are pre-defined in a seperate database).

I've managed to read the holidays from the database by the following formula:

@Holidaydays:

Global DateVar Array Holiday := [Date (2003,12,25)];
local Numbervar i;
local Numbervar lenght;
lenght = Maximum({HolidayHours.ID});
Redim holiday[lenght];
For i := 1 To lenght Do
( holiday = cdate({HolidayHours.HolidayDate});
);


Now I want to use this array to test if the date was a Holiday. The formula for that looks like this:

local testdate as datetime
IF testdate in Holiday THEN
//..calculate things..
END IF

Anyone any thoughts how to do this?
(Is my declaration of a global variable fine?
If not how should I define my variables?)

Thanks,
@win