Create diffrent records for each date in a range

[stoneywma05]

I wish to create a report which will creat a record for every day between two dates:

Name Start Date End Date Car
Brown 01-10-2011 05-10-2011 Ford
Brown 06-10-2011 08-10-2011 Nissan
White 05-10-2011 09-10-2011 Kia

==============================
Brown 01-10-2011 Ford
Brown 02-10-2011 Ford
Brown 03-10-2011 Ford
Brown 04-10-2011 Ford
Brown 05-10-2011 Ford
Brown 06-10-2011 Nissan
Brown 07-10-2011 Nissan
Brown 08-10-2011 Nissan
White 05-10-2011 Kia
White 06-10-2011 Kia
White 07-10-2011 Kia
White 08-10-2011 Kia
White 09-10-2011 Kia

 

[Madawc]

Crystal is a reporting tool and does not create database records, though this can be done within an SQL Command included in the Crystal.

You can show details as per your example, though not easily. If you have a table of dates with one entry per day, this would work with a left-outer join, but would be tricky since you want the details repeated for start-date to end-date.

It would also be possible within SQL to merge the two datasets, date and details, to produce one entry per day in the way you show. That depends on being able to develope code in SQL, or in whatever database language you may be linking Crystal to.

The alternative is a 'Mock Crosstab'. This is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. Again, your case is different, you'd need to define a formula field with variables to collect the right value.

This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.

Each running total will count the record if it was within the criteria - in your case, the day.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP