Solved

Crystal Reports XI Date Filter

Posted on 2013-12-26
11
454 Views
Last Modified: 2013-12-31
I'm trying to record select based upon Crystal's "LastFullWeek" .
I have the field TimeTicket.TicketDate (as string).

Record Selection Formula is: {TimeTicket.TicketDate} IN LastFullWeek
Formula Error - String Required

See attached screenshots.

My goal is to list all TimeTicket entries for the last full week without prompting for a date range. Any assistance is greatly appreciated.
Doc3.pdf
0
Comment
Question by:Lee Ingalls
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 3

Accepted Solution

by:
13Shadow earned 500 total points
ID: 39740190
Try:
DateValue({TimeTicket.TicketDate}) IN LastFullWeek
0
 
LVL 7

Assisted Solution

by:Lee Ingalls
Lee Ingalls earned 0 total points
ID: 39740197
Problem solved by converting string field to DateTime using SQLExpression.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39740351
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.
0
 
LVL 7

Author Comment

by:Lee Ingalls
ID: 39740386
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.
0
 
LVL 3

Expert Comment

by:13Shadow
ID: 39740493
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39740589
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.
0
 
LVL 3

Expert Comment

by:13Shadow
ID: 39740628
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.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39740685
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39741042
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
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39742034
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.
0
 
LVL 7

Author Closing Comment

by:Lee Ingalls
ID: 39747875
Both solutions worked. 13Shadow gets the points for providing a workable solution before I did.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now