Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Populating the Dates within a Date Range Post Reply Post New Topic
Author Message
aimee
Newbie
Newbie
Avatar

Joined: 16 Mar 2009
Location: United States
Online Status: Offline
Posts: 9
Quote aimee Replybullet Topic: Populating the Dates within a Date Range
    Posted: 16 Mar 2009 at 7:50am
Tongue  I have a report that needs to takes two database fields (startdate) and (enddate).  And then the report needs to show all the dates with that date range, ie   startdate = 11/23/2008 and enddate 11/30/2008.  The report would show 11/23, 11/24/ 11/25, 11/26, 11/27, 11/28, 11/29, 11/30 and 11/31.  I have downloaded crystal report books, forumlas and googled my mind out.  Still haven't come across anything that would help.   
PLEASE HELP!!!

aaa
aaa
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 16 Mar 2009 at 12:35pm
The only way I know of to do this is to have a separate table that contains all of the dates.  Create a left-outer join from that table to the table that contains your data.  Set your filter to work based on this Dates table with the dates between the start date and the end date.
 
Because it's a left-outer join, you should get all of the dates in the Dates table, regardless of whether there is corresponding data.
 
-Dell
IP IP Logged
aimee
Newbie
Newbie
Avatar

Joined: 16 Mar 2009
Location: United States
Online Status: Offline
Posts: 9
Quote aimee Replybullet Posted: 16 Mar 2009 at 4:36pm
this is great,  thanks so much,
if you can please help on this next part, I did bring in a excel spreadsheet that had all the dates. and that worked ok, just not sure how to do the next part which would be
the report is an attendance report and the table it is querying has a clientid and a startdate field and enddate field.  I need the report to take in a dynamic parameter begin and end date that the user inputs, and then match the clientid startdate through the end date.  I created a report exactly like this with another table, however that table had individual dates for each clientid.   This table uses a date range.  how would I use that table of dates to match the date range of the clientid. 

Example, the user wants to see who had was in attendance of 11/1/2008  through 11/30/2008.  Now the clientid has a start date of 11/15/2008 and enddate 11/18/2008.  The report would show
11/15
11/16
11/17
11/18

Another client would have a startdate of 10/1/2008 through enddate of 11/3/2008.
The report would show
11/1
11/2
11/3


Another client would have a startdate of 11/29/2008 through 12/2/2008
The report would show
11/29
11/30
11/31

The paramater dates would drive the report...can't get a design around this....


aaa
aaa
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 17 Mar 2009 at 8:23am

Hmmm... That is a bit more difficult. 

Try this:
 
- Don't link the spreadsheet with the dates.
- Set the filter so that the client start date is between the start and end date parameters.
- Manually edit the selection filter to create a link between the client data and the dates.  This will look something like this:
 
({excel.date_field} >= {client.start_date} and 
 {excel.date_field} <= {client.end_date})
 
I haven't tested this to verify it will work, but I think it will.
 
-Dell
IP IP Logged
aimee
Newbie
Newbie
Avatar

Joined: 16 Mar 2009
Location: United States
Online Status: Offline
Posts: 9
Quote aimee Replybullet Posted: 17 Mar 2009 at 9:32am
Hey just got back in and read your post, I'll try it now, this is great!!!
aaa
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.031 seconds.