Question about Report Record Selection Date 1

[jehanzebn]

Morning all (happy new year! ))

I have found one problem with my report which calculates number of returns and credits.

The report is set to 4 date criterias.
1: Daily
2: Last 5 Days
3: Last 4 Weeks
4: Year to date

Now to get the report work faster I added a formula into the report record selection

{order_header.date_entered}=Yeartodate

Since it has been set to yeartodate at record selection, when I ran the report today, it produced no results for Last 5 Days and Last4Weeks as both do not set to Yeartodate (2008) criteria.

I then removed the Yeartodate formula from the Record Selection. Since I did that, the report is back to sluggish speed.

I think when I remove the date criteria from the record selection the report gets all the records regardless of the date before it processes the date criterias set into the report. In other words, it brings all the records from the table and then filters them onto the report. Hence slow speed.

Is there anyway to avoid the slow speed and get the right data?

In current situation
1: Daily set to 02/01/2009
2: Last 5 days set to 26/12/2008 to 01/01/2009
3: Last 4 Weeks set to 01/12/2008 to 26/12/2008
4: Year to date set to 01/01/2009 to 02/01/2009

Any ideas how to get this sorted?

Many thanks

Kind Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

Can I setup 4 different criteria within Record Selection formula?

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

Perhaps I might be able to use

{Order_Header.Date_Entered} >= Today-365?

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

I would set up a string parameter {?Interval} that identified the date interval with the options you identified above. Then change your record selection formula to:

(
select {?Interval}
case "Daily" : {table.date} in currentdate-day(currentdate)+1 to dateserial(year(currentdate), month(currentdate)+1,1)-1
case "Last 5 Days" : {table.date} in currentdate-7 to currentdate-1
case "Last 4 Weeks" : {table.date} in minimum(lastfullweek)-21 to maximum(lastfullweek)
case "Year to Date" : {table.date} in date(year(currentdate),1,1) to dateserial(year(currentdate), month(currentdate)+1,1)-1
)

I'm not sure what you intended by "Daily"--whether you literally meant you wanted only the one date or all dates during the currentmonth. If you meant just the one date, then you would use:

{table.date} = dateserial(year(currentdate), month(currentdate)+1,1)-1

And Last 5 Days means last 5 working days? I think using the last 7 days will work except in the case of holidays.

-LB

 

[jehanzebn]

Happy new year IIbass and thanks for your kind help with this query.

Yes. Daily means Currentdate. Daily, Last 5 Days, Last 4 Weeks and Year to date - all are setup as Working days by the use of formula.

One question when you said, you would setup a String parameter and use that parameter to filter the report.however the report is setup in such a way that there is no need for any user input. The report will Show all the Date sequences in one single report.

For example, When I run the report it only asks for Reason Code. When range of reason code is entered (lets say 1 - 999), it produces the results according to those reason code and dates are shown

Daily - Last 5 Days - Last 4 Weeks - YTD

So giving a parameter will ask the user to enter the "Daily", "L5D", "L4D" and "YTD" which users don't want to do.

However saying that, I will try and use the concept which you have mentioned above.

Regards

Jehanzeb



Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

You might want to set up a record selection formula like this:

(
{table.date} in {table.date} in date(year(currentdate),1,1) to dateserial(year(currentdate), month(currentdate)+1,1)-1 or
{table.date} in minimum(lastfullweek)-21 to maximum(lastfullweek)
)

This will give you the earliest necessary dates if the currentdate is early in the year, or, if later in the year, will bring in all ytd dates.

-LB

 

[jehanzebn]

Thanks IIbas,

I have just entered the information as you suggested and now running the report.

Will post once the records are perfect )

Many thanks once again

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database