Technical Questions: How do you do that...

How do you do that...

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11400
Printed Date: 04 May 2024 at 11:57pm

Topic: How do you do that...

Posted By: ChicagoPete
Subject: How do you do that...
Date Posted: 12 Oct 2010 at 9:57am

Hi Gang,

Rather new to CR...

In CR X, I am using a SQL query (in DATABASE EXPERT>COMMAND) that uses the following to set the date parameter for the report:

**** snip****

DECLARE @BegDate datetime;
DECLARE @EndDate datetime;
SET @BegDate = '20090101'
SET @EndDate = '20091231'

***end snip****

later in the query I evaluate against the record.timestamp to gather the proper records within the date range.

****

SELECT blah, blah, blah FROM abc WHERE more stuff here and c.create_timestamp >= @BegDate
and c.create_timestamp < @EndDate + 1)

****

I don't like this setup (I inherited) and would like to know how to remove the hard-coded date range from the query and have the report user get a prompt from CR for a date range AND how to display the date range in the report page header.

Thx

-ChicagoPete

Replies:

Posted By: rvink
Date Posted: 12 Oct 2010 at 1:55pm

Go to Database Expert and edit your command. On the right is a section titled "Parameter List". Click "New" to create your parameters BegDate and EndDate. Make sure the value type is set to Date. Then write your query like this:

SELECT blah, blah, blah FROM abc WHERE more stuff here and c.create_timestamp >= {?BegDate}
and c.create_timestamp < ({?EndDate} + 1)

Unless there is a need to write your query manually (union or aggregate query) it is better not to use a command. Drag the required tables across and link them together in the Links tab. In your report you can then create a parameter called "Date" and make it a range parameter. In your select expert you can then write this:

{c.create_timestamp} = {?Date}

The range parameter gives you the flexibility to say if the range includes or excludes the end values, or if the range is open ended. Crystal dynamically changes the resulting query to match. Manually writen commands can't do that - if you want an open ended range and leave the enddate blank, you'll get an error. It's also easier for the user since they can enter the start and end in one place, they don't need to enter two separate parameters.

Unfortunately you pretty much have to re-write the report from scratch you can't map fields between commands and tables.

Posted By: ChicagoPete
Date Posted: 13 Oct 2010 at 2:13am

Thanks for the quick reply, after some tweaking of your suggestion I got it to do what I needed!