Date Ranges - here's a beauty :-)

[CInman]

I want to ignore the database upon which I'm reporting, for now, and quite simply want to ask for a date range. Then, output (as Group Headings) each and EVERY date in that date range. That way, I can output subtotals as group footers, even if no records in my source database contain any data for a given date.

 

[rosemaryl]

The only way I have been able to accomplish this is to create a stored procedure, using derived tables, to generate the dates in the range I want and then use that as a main report.

I then insert a subreport and link on dates. When there is no data for the date nothing is returned and I place a formula thta shows 0.

Hope this helps. Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[chelseatech]

We did some articles on "Missing Data" in some past issues of crystal clear. It might be easier to use those techniques which used Previous and Next functions.

Alternatively, if your date range is always of the same size (eg. 28-31 days in a month), you could use a bunch of variables, but that would be a lot of work, and restrict your range of replies. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[CInman]

Thanks for the feedback ... I get the feeling its going to be a tough one to crack. How do I get the back copies of Crystal Clear that are relevant? Is that a subscription publication or a web one?

 

[HowardHammerman]

Here is another way:

Create a formula field with the following expression:


//@date_range
datevar lowdate := minimum({?parameter});
datevar hidate := maximum({?parameter});
numbervar days := hidate-lowdate;
numbervar kount := 0;
datevar array alldates ;
for kount := 0 to (days - 1) step 1 do
(addates[kount] := lowdate + kount);


The good news is you now have an array with one element per day. The bad news is that you cannot output an array in Crystal. So .....

Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[chelseatech]

The back issues of Crystal Clear are at

http://www.chelseatech.co.nz/pubs.htm

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm