Solved

Crystal Reports 11 - Date Range Filter

Posted on 2013-01-03
5
2,011 Views
Last Modified: 2013-01-03
Hello experts,

I have a crystal report that is built on a Command that has a date field in the form of yyyymmdd.  I have desire to filter my data based on a user-entered date range, so to do that, I had to first have a formula that converts that field to a number:

@{Due Date}:
CDate (ToNumber (Left ({Command.apptDate},4 )),ToNumber (Mid ({Command.apptDate},5 ,2 )),ToNumber (Right ({Command.apptDate},2 )))

{?Due Date Param}
I built a parameter as a Date datatype, allowing range values, and assigned that parameter to the

Select Filter:
{@Due Date} = {?Due Date 2}

I now have a need to have the select the date equal to the range, but PLUS 30 days from the Due Date Variable above -- @{Due Date}

So, in theory something like:

Select Filter:
{@Due Date} = {?Due Date 2} + 30

So that if the data set looks like:

20130125
20121225
20121125
20121025
20120925
20120825
20120725

And you entered a range of

2012-11-25 to 2012-12-25

it would return dates in this range, up to 30 days in the future, or 2013-01-25:

20130125
20121225
20121125

I appreciate the time and help!

Thanks
0
Comment
Question by:robthomas09
5 Comments
 
LVL 14

Accepted Solution

by:
LinInDenver earned 400 total points
ID: 38740551
In your report filter, you would do:

{@Due Date} in minimum({?Due Date 2}) to maximum({?Due Date 2}) + 30

or {?Due Date Param} instead of {?Due Date 2} - whichever one you are really using to get the date range from the user.
0
 
LVL 18

Expert Comment

by:vasto
ID: 38740900
Change your Command to include :

convert(datetime,<datefield>,112) as ConvertedDate
This will provide you with a field with date format
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 50 total points
ID: 38740906
Better would be to convert the parameters to the yyyymmdd format the dates are in since then the filter cabn be passed to the database.

{YourDateField} in Val(CStr(minimum({?Due Date 2}),"yyyymmdd")) to  Val(CStr(maximum({?Due Date 2}),"yyyymmdd"))

mlmcc
0
 
LVL 18

Assisted Solution

by:vasto
vasto earned 50 total points
ID: 38740970
If you convert the current data field to a real date you will be able to filter the data inside the command. There will be 3 advantages :
 1. Filter by date is faster then filter by string ( varchar, char ...)
 2. You will be able to filter the data on the server instead of downloading everything locally and filtering it inside Crystal reports. This will make your report much faster too.
 3. It is simpler,  the command will look like :

SELECT ....  convert(datetime,<datefield>,112) as ConvertedDate
FROM ...
WHERE convert(datetime,<datefield>,112) BETWEEN {?Date1} AND {?Date2}

Where {?Date1} AND {?Date2} are the parameters inserted by the user.

In 2008 you can use also date (instead of datetime) and your report will be even faster
0
 

Author Closing Comment

by:robthomas09
ID: 38741533
Thanks all!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

19 Experts available now in Live!

Get 1:1 Help Now