How to get count of holidays from table instead of array

[ry1633]

I am new-ish to Crystal Reports so this might be somewhat rudimentary of a question. I need to get a count of holiday dates between a start date and the current date, and use in a business working days calculation. All of the dates for the holidays are in a table in my database.

I've found Ken's formula (

http://kenhamady.com/formulas/form01.shtml)

and it works good for me with some tweaking. But I'm not sure how to handle the last portion. Instead of using an array with the holidays in it like his example, I would be getting the holidays from date values in a separate table. So I need to get a range of holidays from my table values that's between a given start date and the current date. I'm using Crystal 2013 if that helps. Thanks for any pointers.

 

[IanWaterman]

Quickest and easiest will be to use a subreport which queries your holiday table and builds a shared datevar array.
Minimise subreport in your main report header and then pass the shared array to your formula.

Ian

 

[ry1633]

I just need to pull in holidays from the table and some how get that back into the formula of the main calculation. I just made a really simple subreport that has only one formula in it:

whileprintingrecords;
shared DateVar Array Holidays;
redim preserve Holidays[recordnumber];
Holidays[recordnumber] := {myTable.Date_field};
"";

What I don't understand is how to get that shared variable back into the main formula and also where to place / hide the subreport within the main report. Using Ken's example as a starting point.... I have commented the areas I have questions..

//Main formula
WhileReadingRecords;
Local DateVar Start := {MyTable.StartDate}; // place your Starting Date here
Local DateVar End := CurrentDate; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays; // New Shared DateVar here instead?

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) +
(if DayOfWeek(End) = 7 then -1 else 0);

// *** This is the section I have questions about, will this pick up the new Shared DateVar from the subreport instead?
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

// ** If using the SubReport can I omit this entire section?
//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0

 

[IanWaterman]

Assuming your DateVAR array is being populated, I am not familiar with the syntax you have used.

Test result in Subreport with a new formula which displays Array.

I think something like

whileprintingrecords;
shared DateVar Array Holidays;
Join(Holidays, ",")

should work

If all OK then your notes to above formula are correct, making sure you declare array as
shared DateVar Array Holidays;

Ian

 

[ry1633]

Using Ken's formula (

http://kenhamady.com/formulas/form01.shtml)

as a jumping-off point here's what I ended up doing and I think it works OK. If there's an even better way I'd be open to it. Like I said before I'm new to Crystal.

1. Commented out all the values in the old array so there’s nothing in there.
2. Created a subreport that returns a shared DateVar Array Holidays (same name as old Array so I wouldn’t have to change too much).

whileprintingrecords;
shared DateVar Array Holidays;
redim preserve Holidays[recordnumber];
Holidays[recordnumber] := Date({X_HOLIDAYS.HOLIDAY_DATE});
"";

3. Suppressed all sections of the subreport.
4. Change the declaration to shared DateVar Array Holidays in the main report.
5. Insert the subreport into the page header of the main report and re-sized it smaller. Since the subreport itself had all sections suppressed it doesn't show in the main report