CR 8.5 Report Takes Too Long Too Run 1

[antonx]

Hi,

I am using a CR 8.5 with an ODBC connection to a DB2 table on the mainframe. The table has about 45,000 records. The problem I have is that the simple report takes very long too run. It takes about 5 minutes to run. It is not a problem now but in 2 years when there will be hundreds of thousands of records, it will be a big problem!

I have a main report with one table. The main report also has a subreport. The problem is in the record selection in my main report. There is an IF statement in the record selection. When I look in the Show SQL query box, the where clause is missing.

This is what I would like the report to accomplish:

If the current day I run the report is the 1st, 2nd or 3rd day of the current month, then I would like to extract all records from the previous month.
For example, if I run this report on 2004/01/03 (YMD), then it must extract all records where {NEW_BUS_DM.TRANSACTION_DT} is between 2003/12/01 and 2003/12/31.
On the other hand, if the current day I run the report is >= the 4th day of the current month, then it must extract all records from the first day of the current month until the last day of last week which is Saturday.
For example, if I run this report on 2004/01/13, then it will extract all records where {NEW_BUS_DM.TRANSACTION_DT} is between 2004/01/01 and 2004/01/10.

This is my record selection in the main report (which takes very long to run):

if Day(CurrentDate) in [1, 2, 3] then
{NEW_BUS_DM.TRANSACTION_DT} >= DateSerial(Year(CurrentDate), Month(CurrentDate) - 1, 1) and
{NEW_BUS_DM.TRANSACTION_DT} <= DateSerial(Year(CurrentDate), Month(CurrentDate), 1 - 1) and
{NEW_BUS_DM.TRANSACTION_TYPE} = &quot;New Issue&quot; and
{NEW_BUS_DM.CHANNEL_NAME} <> &quot;Independent Career&quot; and
{NEW_BUS_DM.CHANNEL_NAME} <> &quot;N/A&quot;
else
{NEW_BUS_DM.TRANSACTION_DT} >= DateSerial(Year(CurrentDate), Month(CurrentDate), 1) and
{NEW_BUS_DM.TRANSACTION_DT} <= maximum(LastFullWeek) and
{NEW_BUS_DM.TRANSACTION_TYPE} = &quot;New Issue&quot; and
{NEW_BUS_DM.CHANNEL_NAME} <> &quot;Independent Career&quot; and
{NEW_BUS_DM.CHANNEL_NAME} <> &quot;N/A&quot;

This is what is in my Show SQL query:

SELECT
NEW_BUS_DM.`TRANSACTION_DT`, NEW_BUS_DM.`TRANSACTION_TYPE`, NEW_BUS_DM.`SALES_COUNT`, NEW_BUS_DM.`CHANNEL_NAME`
FROM
`NEW_BUS_DM` NEW_BUS_DM

How can I improve the run time and include the where conditions of my records selection in my SQL?

Thank you in advance.

 

[synapsevampire]

Your logic is confusing, try running it in January and the formula states that month will be month-1, which is 0, not your assumed 12.

Also, this theory is flawed:

&quot;On the other hand, if the current day I run the report is >= the 4th day of the current month, then it must extract all records from the first day of the current month until the last day of last week which is Saturday.&quot;

If the day is the fifth, which falls on a Friday, you're saying the report runs from the 1st thru the last Saturday of the previous week, which would be before the start date.

The business rules need work, but forgetting about that for now...

As for passing SQL, one method is to create a start date and end date formula and test as you go.

@startdate:
If Day(CurrentDate) < 4 Then
If Month (CurrentDate) = 1 Then
DateTime((Year(CurrentDate)-1),12,1,0,0,0)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)-1,1,0,0,0)

@enddate:
If Day(CurrentDate) < 4 Then
DateTime(year(CurrentDate),month(curreentdate),1,11,59,59)-1

Then the record selection formula contains:

(
{table.date} >= @startdate
and
{table.date} <= @enddate
)

Note that I used datetimes because your field is probably a datetime, not a date, and this seems to help Crystal in many cases.

Or you might even use the following for the last month formula in the record selection formula:

If Day(CurrentDate) < 4 Then
{table.date} = lastfullmonth

But you need to define the biz rules better for the > 3rd scenario, the methodology will be consistent with my 1st example above.

-k

 

[antonx]

Thank you for your response!

About the theory that you mentionned is flawed, I forgot to specify that this report is scheduled to run only on Mondays, which I think the theory is not flawed now.

I am eager to use the @startdate and @enddate fomulas you created. But for the @enddate, if the current day I run the report (which will be Mondays) is >= the 4th day of the current month, then it must extract all records from the first day of the current month until the last day of last week which is Saturday.
For example, if I run this report on 2004/01/13, then it will extract all records where {NEW_BUS_DM.TRANSACTION_DT} is between 2004/01/01 and 2004/01/10.

How can this be done in the @enddate formula?

 

[synapsevampire]

OK, every Monday you want through the last Saturday when > 3rd.

To compute last Saturday use:

currentdate-day(currentdate)

So if it's a Monday, it will subtract 2 days (Saturday).

@enddate:
If Day(CurrentDate) < 4 Then
DateTime(year(CurrentDate),month(currentdate),1,11,59,59)-1
else
DateTime(year(CurrentDate),month(currentdate),day(currentdate),11,59,59)-day(currentdate)

-k