Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Reports XI Date Filter

Posted on 2013-12-26
11
Medium Priority
?
476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 3

Accepted Solution

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

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 9

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
 
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 101

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 9

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

722 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