Confused about business date calculations

[Venimeux]

I used the information I found here and on Ken Hamandy's site to create a report. The function of the report is to find all orders that filled on three consecutive business dates. I created a Parameter called {?Start Date} and three formulas--{@Holidays}, {@DatePlusOne} and {@DatePlusTwo}. The DatePlusOne and DatePlusTwo are exactly the same as Ken Hanamy's site, with the changes to the input and the days added. Holidays simply shows some business holidays.

Here is my selection statement:

{Trans.AC9} = <account number that we're searching for>
and
{Trans.BuySell} in ["B", "S"] //indicates buy or sell
and
(
{Trans.EffDate} = {?Start Date}
or
{Trans.EffDate} = {@DatePlusOne}
or
{Trans.EffDate} = {@DatePlusTwo}
)

If I run the report with this, it runs forever while records run across the bottom. I let it run for 15 hours before I stopped it. If I change the selection to show {?Start Date}+1 and {?Start Date}+2 it finishes in seconds.

What the heck am I doing wrong!?

 

[rosemaryl]

Dear Venimeux,

It is very bad for report performance to use formulas in the selection criteria.

What is happening is that Crystal is effectively returning every database record and doing a check to see if the data "matches".

What is the content of the @DatePlus1 and @Dateplus2 formulas? Maybe they can be changed to SQL expressions to improve performance.

Please advise and provide database, Crystal Version, and expected results.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Venimeux]

The following is my @DatePlusOne:
====================
//Adding Business Days:
WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:={@Start Date}
NumberVar Add:= 1;
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
==================

I'm using CR 9.0 on Win2k. The end goal of the project is to print out all orders that filled on an inputted date, as well as 2 business dates forward. So if the input is 3/17/04, they also want 3/18 and 3/19.

I apologize but I don't know where else to put the formulas or how to use them in the selection for the report.

 

[rosemaryl]

Dear Venimeux,

So, the upshot of what you are trying to do is to return those orders with eff date of the date passed in the parameter and those that have an effective date 2 Business Dates into the future. So, if the effective date was on Friday, you want Monday and Tues dates also returned and if the date is on a Friday and Monday and Tuesday are holidays, you want those orders with an effective date of the passed date and then Wednesday and Thursday of the following week.

The way you currently have it written is that for each row in your database Crystal has to perform the calculation in both those formulas and effectively say: "yes, yes, no, no... and so on. You can check this by doing a Database/Show SQL Query and you will see that the criteria for @dateplus1 and @dateplus2 is not passed.

Do you have your holidays stored in the database? That would make it easier, because you could check for that right in the selection criteria.

If you are using CR9 then you have the option of that using a SQL Command to which you can pass a parameter.

Regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Venimeux]

Ken's holiday array was placed into the header of the report with the correct holidays noted.

============
//Holiday Array Formula for the report Header:
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25), // you can put in as many lines for holidays as you want.
Date (2003,12,31)
];
0

=============

I am using CR8.5 but I have to be able to export this to CR7.0 for users. We still need to upgrade them...

I know we have a holiday table, but I'm unfamiliar with where it's at. Good idea!

Is there a way that I can have CR perform the calculation to determine the three dates I need, then pass it to the selection criteria to use? That's what I want it to do anyway. Having to perform the calculations every time it reads a row is pretty time intensive.

Thanks very much for any help you provide!
Ven

 

[rosemaryl]

Dear Venimeux,

I understood that you were using Ken's formula and had an array for the holidays, but selecting data for the report on that is not efficient, as you have found.

If you have a table that indicates which dates are holidays, it also probably indicates which dates are business dates (not weekends) then you should be able to write selection criteria that takes advantage of it to pass the dates to the database that you want returned.

I suggest a meeting with your dba so that he/she can explain the data structure of the table.

Having CR do any calculations that are then used in the selection formula is contraindicated for efficient report processing.

You cannot use a sql expression, because parameters cannot be passed to expressions.

Using your existing holiday table is the best bet.

regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.