Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: How do you do that... Post Reply Post New Topic
Author Message
ChicagoPete
Newbie
Newbie


Joined: 12 Oct 2010
Online Status: Offline
Posts: 2
Quote ChicagoPete Replybullet Topic: How do you do that...
    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
 
 
IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet 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.

IP IP Logged
ChicagoPete
Newbie
Newbie


Joined: 12 Oct 2010
Online Status: Offline
Posts: 2
Quote ChicagoPete Replybullet 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!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.