• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2239
  • Last Modified:

Crystal Reports 11 - Date Range Filter

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
robthomas09
Asked:
robthomas09
3 Solutions
 
LinInDenverCommented:
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
 
vastoCommented:
Change your Command to include :

convert(datetime,<datefield>,112) as ConvertedDate
This will provide you with a field with date format
0
 
mlmccCommented:
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
 
vastoCommented:
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
 
robthomas09Author Commented:
Thanks all!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now