Crystal Reports XI Date Filter

I would personally consider BudELee's own solution as the best.  13Shadow's will "work", but  it could be horribly inefficient, depending on how much data is available.  Why?  Because it's a print-time formula that won't be passed to the database server for processing.  Instead, what will happen is that ALL tickets, regardless of date, will be processed and returned by the database server to the client, which will then filter them out after the fact.  Depending on how much overall data there is, other filters and joins in the report, etc..., this could be anywhere from thousands to millions of extra records being unnecessarily returned to the report.  

Even though a SQL Expression will do a similar conversion to the TicketDate field, it will always be processed on the database server.  By using this solution, the report will be far more efficient.

Rhinok: I did notice the performance hit using the DATEVALUE solution, as it went through 244,000 records to return 373. The SQLExpression was near instantaneous.

This question was posted under Crystal Reports software and thus I gave a Crystal Reports solution. Had there been any mentioning of performance it would have been a different solution I would have offered. There are always a lot of ways to accomplish the same result.

This question was posted under Crystal Reports software and thus I gave a Crystal Reports solution. Had there been any mentioning of performance it would have been a different solution I would have offered. There are always a lot of ways to accomplish the same result.

A SQL Expression is a Crystal Reports solution.  It's a type of field available in  Crystal Reports through the Field Explorer when you use Tables and/or Views from SQL Databases (and some PC databases, such as MS Access) as data sources. Unlike a formula, as stated above, it's always processed as part of the SQL Crystal Reports passes off to the database for processing.

Yes, there are often many ways to accomplish tasks within Crystal Reports, but the question was specific to record selection, not just a printed formula on the page.  The OP shouldn't have had to mention performance, because performance should always be considered in regards to the record selection criteria.

That's why I posted the comment I did.  I think it's important to explain why one solution may be better than another, not just provide a "fix" to a problem.  When I teach Crystal Reports classes and we start to get into Record Selection Criteria, I always suggest my students review Database|Show SQL Query in order to ensure their criteria is passed to the database for processing as part of the WHERE clause Crystal Reports generates.  In your example, it would have been missing.

The OP's latest comment validated my post - using the formula you recommended, 244k records had to be processed by the database server, returned across the network and then filtered out by the client simply to see 373 records.  In other words, only .15% of the records actually read and returned from the database were usable and met the conditions of the selection criteria.  That doesn't seem like a good solution to me.

There are definitely cases when using a print time formula might be the only option or when the amount of data is so small there wouldn't be any significant performance gains by not using one.  In such cases, using a print time formula would be fine. Without knowing anything about the underlying database though, I would generally only recommend it as a last resort.

Like you said without knowing the specifics of database, connectivity and data volume I can't say that you will notice degradation with one solution or another. There wasn't enough information to know. But you seem to imply that by using the sql expression only the selected records would be used. I don't believe that is true. It would be true if the sql field being compared was a date type field but the OP mentioned he is converting the string field to date in the sql expression. My solution was a record selection formula not a print time formula like you keep mentioning. The OP changed the sql expression directly.

Like you said without knowing the specifics of database, connectivity and data volume I can't say that you will notice degradation with one solution or another. There wasn't enough information to know. But you seem to imply that by using the sql expression only the selected records would be used. I don't believe that is true. It would be true if the sql field being compared was a date type field but the OP mentioned he is converting the string field to date in the sql expression. My solution was a record selection formula not a print time formula like you keep mentioning. The OP changed the sql expression directly.

1)  I'm calling it a Print Time formula, because that's what it is, whether you use it in a formula field or in the record selection criteria. It's a formula that can't be passed to the database for processing.  Instead, it's processed in the WhilePrintingRecords (Pass 2) phase of the multi-pass report model used by Crystal Reports.   There's no difference between:

{@TicketDate} IN LastFullWeek//in which the TicketDate Conversion was done in the formula

vs.

DateValue({TimeTicket.TicketDate}) IN LastFullWeek

Either way, the conversion won't be passed to the database server for processing because it can't be evaluated until the WhilePrintingRecords pass.

2)  The OP didn't change "the SQL Expression" directly.  By stating such, are implying the OP manually manipulated the SQL that Crystal Reports generates.  That's not possible, except in very old versions of Crystal Reports (8 and lower, I believe).  When you create a report against tables and/or views, Crystal Reports automatically generates the SQL based on a combination of factors, including: the fields you use, the tables you join and the record selection criteria that can be passed to the database server as part of the WHERE clause.

What the OP did do, however, was create a SQL Expression field in which he converted the database field from one datatype to another.  This is the functional equivalent of what you recommended with a formula.  He then used the SQL Expression field in his Record Selection Criteria, probably like the following:

{%TicketDate} IN LastFullWeek

In this case, the converted database field value would ALWAYS be passed off to the database server for processing.  Why?  Because when you create a SQL Expression field, Crystal Reports automatically adds it as a field in the SELECT clause of the SQL it generates.  By doing so, the field is now available to be evaluated at run time (the WhileReadingRecords phase, otherwise known as Pass 1), not print time.

I've attached on old presentation on SQL Expressions I gave at the 2006 BusinessObjects Insight Conference.  The portion on suppressing subreport instances is outdated, since the process is now much more streamlined, but it provides good examples of what SQL Expressions are and scenarios in which they can be used effectively.
The-Power-and-Possibilities-of-S.ppt

Just want to clear up the evaluation time of the formula.  

I just verified that DateValue can be used in formulas with WhileReadingRecords; as the evaluation time.

If it was a printime formula it couldn't be used in the record selection.

The issue is it is a Crystal function so that can't be passed to the database but has to be evaluated by Crystal thus many more records than desired will be brought to the report to be filtered.

I agree that converting the date strings using a Crystal formula will have a performance hit.

mlmcc

The issue is it is a Crystal function so that can't be passed to the database but has to be evaluated by Crystal thus many more records than desired will be brought to the report to be filtered.

That's not entirely accurate.  The issue is the function was used in conjunction with a database field and, therefore, didn't get passed to the database as part of the record selection in Pass 1. The function itself can actually be passed to the database for processing as long as it's used in conjunction with a constant formula, such as using it in conjunction with a special field like DateTime or even against a parameter value.  Both of these following selection criteria get passed to the database, for example:

{Orders.Order Date} <= DateValue(ToText(DataDate))

{Orders.Order Date} > DateValue({?Order Date})

So, it's not the function itself that's the issue, but the manner in which it's used that effectively prevents it from being passed to the database server for processing.

Both solutions worked. 13Shadow gets the points for providing a workable solution before I did.