Array help

[ags1127]

Hi,

I need to build a report that will display the number of people for each day in a given date range.

Example Data:

Name In Out
A 1/1/2012 1/2/2012
B 1/1/2012 1/3/2012
C 1/2/2012 1/2/2012
D 1/3/2012 1/10/2012
E 1/5/2012 (null)

The report prompts for a date range and should return the number of people that were in during each day of the date range

For example, if date range provided was 1/2/2012 to 1/5/2012, I would expect the report to return:

Date #
1/2/2012 4
1/3/2012 3
1/4/2012 1
1/5/2012 2


My selection parameters work in pulling all people who were within the date range, however, I am at a loss in getting the report to display for each day. I've tried making an array of dates for the date range given in the parameter, but I must be doing it wrong (would an array be the best to use in this situation?). Any ideas would be greatly appreciated!!

Thank you,
AS

 

[ags1127]

Forgot to mention.. using Crystal Reports 8.5

 

[lbass]

Create a formula {@alldts} like this:

whilereadingrecords;
datevar x := {?startdate};
numbervar diff := {?enddate} - {?startdate};
numbervar i := i + 1;
if i <= diff then
x := Date(DateAdd("d", i,{?startdate}));
x;

Insert a group on this formula. Even though you are not referencing it, you must have a table added to the main report that contains more rows than the maximum days in the date range so that the above formula will execute and create the day groups.

Then insert a subreport that is sorted by indate and outdate and which includes {table.personID}. Insert a distinctcount on {table.personID} at the grand total level. Suppress all sections of the sub except the report footer. Link the sub to the main report by moving the main report formula {@alldts} to the right. Allow this formula to link to some field in the sub. Then in the sub, go to report->selection formula->record and change the formula to read:

{table.indate} <= {?pm-@alldts} and
{table.outdate}>={?pm-Alldts}

Then place the sub in an inserted GH#1_b section, and format GH#1_a to "underlay following sections". Remove the border from the sub.

Suppress the detail and group footer sections in the main report.

-LB

 

[ags1127]

THANK YOU LB!!

This worked exactly as I needed it to.
Now I just need to pretty up the report for distribution

Thanks Again!

 

[lbass]

What was wrong with the display? Did you use the underlay following sections and remove the border?

-LB

 

[ags1127]

Nothing wrong with the display - Underlay following sections and removing the border worked. Just have a few more data elements to the sub-report (didn't just need count of people, but count of people by type) I added headers to the main report and I am making it line up with the grand total summaries in the subreport. Also added logo, etc and viola!

Thanks again! I can stop pulling my hair out now