INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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.
Minimise subreport in your main report header and then pass the shared array to your formula.

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
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[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.
BeforeReadingRecords;
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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close