[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Crystal Reports 11 - Date Range Filter

Posted on 2013-01-03
5
Medium Priority
?
2,171 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
[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
5 Comments
 
LVL 14

Accepted Solution

by:
LinInDenver earned 1600 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 101

Assisted Solution

by:mlmcc
mlmcc earned 200 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 200 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

656 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