INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# How to get count of holidays from table instead of array

## How to get count of holidays from table instead of array

(OP)

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.

### RE: How to get count of holidays from table instead of array

Quickest and easiest will be to use a subreport which queries your holiday table and builds a shared datevar array.

Ian

### RE: How to get count of holidays from table instead of array

(OP)
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
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[i] ) in 2 to 6 and
Holidays[i] 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.
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0

### RE: How to get count of holidays from table instead of array

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

### RE: How to get count of holidays from table instead of array

(OP)
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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!