Previous Business Day with Holidays

[doublej0]

I am creating a report which runs daily based on a previous business days date. Currently I am using the following formula:

{TRANSACTION_HISTORY.POSTING_DATE} = DateTime (Year(CurrentDate), Month(CurrentDate), (Day(CurrentDate)-1), 00, 00, 00)

There are two things I would like to accomplish. First, I would like to make the report run faster. I have read that if I can put it in SQL it would run quicker.

Second and more importantly, I need an accurate caculation of the previous business day. Currently this is just the previous day. It also needs to take into account holidays. I have read several posts, but have not been able to find anything like what I need. I tried to use a posted formula to calcualte the number of business days between two dates, but to no avail.

Any help would be greatly apprecaited.

Regards,

John.

 

[doublej0]

Also, I cannot setup a table to include holidays or weekend days because I do not have access to modify the database. It needs to be strictly in CR.
Thank you,
John.

 

[smcnulty2000]

You may be taking the long way to get there. If you want to do what you are asking there are formulas for figuring out if a given day is a weekend day. Also you can build an array with known holidays in it. You'd have to maintain the holiday list year-in and out, of course.

However: If you happen to know for sure that no one posts on a day which is a weekend or holiday (which sounds like what you are dealing with) then you might consider bringing in multiple days worth of data (maybe the past week), grouping by day, sorting that group in descending order, which would put the most recent day of business in the front of the report. Then you could suppress the days that were not in that front group, or at the very least cause a page break when the 'day' changed.

That way, you don't have to care what days are holidays, you just care about which days have data.

I think if you want to know a great deal about holidays and time, you might try

http://www.kenhamady.com/formula.html

As for the speed issue, perhaps you could let us know more about the report and maybe someone here can suggest some tuning options. Let us know what you are trying to accomplish, what your output is going to look like and what kind of data you are working with (tables, fields, joins you've already set up, indexes you know about).

Yes, rewriting the selection criteria for SQL usually makes things run faster but that requires that you have access to your database such that you can write the formula directly on the server in PL/SQL or T-SQL. Writing it in the SQL inside your Crystal won't really help (probably).

Let us know your Crystal version, please. And what database system you are using, too, if you can.

Scott.

 

[kenhamady]

See the following link for one option:

http://www.kenhamady.com/form17.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[smcnulty2000]

Excellent followup, Ken.

Scott.

 

[doublej0]

Thank you SMC & Ken for the help.

Ken, this is exactly what I was looking for!
Like SMC said, GREAT FOLLOWUP!

 

[doublej0]

Okay. I am new to Crystal. I am an Access guy, but am finding Crystal much faster when reporting on thousands of records. At any rate, I am having difficulty getting the formula to work correctly. I am using Crystal 10 and pulling the date from a DB2 database (I believe). Please advise below.

This is how it is input:

Under Formatting Formulas->Report Header->Background Color(I'm pretty sure this is not right):

BeforeReadingRecords;
DateVar Array Holidays := [
Date (2005,09,05),
Date (2005,11,24),
Date (2005,12,26),
Date (2006,01,02)
];
0[/color red]

Under Selection Formulas->Record Selection:

WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:=CurrentDate; //Put your field name in here
NumberVar Add:= -3; // Put in the number of days to subtract (a negative number)
NumberVar Added := 0;

WHILE Added > Add
Do (target := target -1;
if dayofweek (target) in 2 to 6 and not (target in holidays)
then Added:=Added-1
else Added:=Added);
Target;

{TRANSACTION_HISTORY.TRADE_CLASS} = "S" and
{TRANSACTION_HISTORY.TERR1} in ["i01", "i02", "i03", "i04", "i05", "i06", "i07", "i08", "i09", "i10", "i11", "i12", "i13", "i14", "i15", "i16", "OS1", "w01", "w02", "w03", "w04", "w05", "w06", "w07", "w08", "w09", "w10", "w11", "w12", "w13", "w14", "w15", "w16", "w17", "w18", "w19", "w20", "w21", "w22", "w23", "w24"] and
{FUNDS.FUND_GROUP} in ["aa", "cc", "eq", "fi", "jpm", "te"] and
{TRANSACTION_HISTORY.POSTING_DATE} in Target[/color blue]

Any help would be great!!

Thank you guys/gals.

 

[kenhamady]

You should be creating 2 formula Fields in the field explorer. One for your holidays and the other for your Business Days formula. Then place these formulas in the appropriate sections of the report layout.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/