Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

Setting up a date range parameter and displaying it in the report

In a recent report, I needed to return data based on a date range selected.  That's easy enought to put in... you just create a new Parameter field, set the type to "Date" and make sure the "Allow range vales" option i set to true.  With the parameter field created, you need to add a record select formula that reads like this...

(TABLENAME.DATEFIELD) = {?DateRange Parameter)

...and all the records returned will be in that date range.

I also needed to display that range value in the report, which took a little digging to figure out.  All you need to do is to create a Formula field with the following code:


DateTimeVar date1;
DateTimeVar date2;

DateRange Parameter});
DateRange Parameter});

totext(Date(date1)) + " and " + totext(Date(date2))

 Then all you need to do is slap that formula field somewhere on the report, and it will display the date range selected in the parameter.


Thanks for reading!  I hope you found this helpful!


What's This?
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.

Related Content
   Using an Array - SQL vs Crystal.
In data, an Array is used to store multiple values in a single variable.  I often use Arrays to qual
Posted on Jul 17, 2014 by George Jensen to George Jensen's Blog
   Working with the Picture Object in Crystal
We have had several inquires lately on how to dynamically change images in a report based on report
Posted on Aug 17, 2012 by George Jensen to George Jensen's Blog
   CFX Reports Are Updated
I am very happy to back after 19 months of Military leave.  One of my first tasks upon returning to
Posted on Jul 27, 2012 by George Jensen to George Jensen's Blog
   Supressing the blank page after "New Page After" Group Footer setting
I've worked with Crystal for some time, but there's always been one little thing that sort of bot
Posted on Jun 08, 2011 by Jason Buss to George Jensen's Blog
   Setting custom date ranges in Crystal
Recently, I had to develop a report which was to return data within a particular date range. Specificall
Posted on May 27, 2011 by Jason Buss to George Jensen's Blog


Scott said:

I know that this is a year old, but I found it very useful. I do have an update for the Formula, though:



DateTimeVar date1;

DateTimeVar date2;

date1:=Minimum({?DateRange Parameter});

date2:=Maximum({?DateRange Parameter});

IF totext(Date(date1)) = "" AND totext(Date(date2)) <> ""


   " Before " + totext(Date(date2))

ELSE IF totext(Date(date1)) <> "" AND totext(Date(date2)) = ""


   " After " + totext(Date(date1))

ELSE IF totext(Date(date1)) <> "" AND totext(Date(date2)) <> ""


   " Between " + totext(Date(date1)) + " AND " + totext(Date(date2))


This way, if your user selects no upper/lower bounds, the description will be accurate. Thanks again for your post. It was very useful!


June 28, 2012 3:28 PM

Robb said:

Yes, it was VERY helpful. It was exactly what I was looking for!

Thanks so much for posting it.

September 14, 2012 8:59 AM

'Jeda said:

very useful...thks

November 19, 2012 9:49 AM

mueli said:

Still helping here...

Nice post. Congrats.

February 26, 2013 12:05 PM

Mathieu said:

Thanks Scott!

March 14, 2013 1:33 PM

Greg said:

Thanks for Posting!  This was very helpful since Crystal Reports doesn't have anything in the help document about this scenario.

March 18, 2013 5:36 PM

Dan Shaw said:

Excellent!  Exactly what I needed.

Thanks so much.

April 17, 2013 2:11 PM

Setting Report date parameters | User25 said:

Pingback from  Setting Report date parameters | User25

September 10, 2013 7:25 AM

Setting Report date parameters | User32 said:

Pingback from  Setting Report date parameters | User32

September 10, 2013 7:33 AM

Lloyd said:

thanks for info.

October 11, 2013 8:28 AM

Scott said:

Very helpful. thanks

January 28, 2014 10:51 PM

Carol Francum said:

Am looking for a way to create OPTIONAL date parameters.  The user would like to refine the start and end dates to also (optionally) refer to the accounting period.

Ideas?  Don't want to trick it with a default of 1900-01-01 or some such.

September 9, 2014 2:35 PM

beetle said:

Thanks, this is just what I was looking for.

November 28, 2014 2:49 PM

Leave a Comment

All contents Copyright © 2015 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register