Solved

Crystal Reports 11 - Date Range Filter

Posted on 2013-01-03
5
1,993 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks all!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 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

16 Experts available now in Live!

Get 1:1 Help Now