Grouping by a Date Range Parameter

[gwaynep99]

This seems so simple, but it is creating a lot of headaches. I am using CR XI and I am trying to utilize a date range parameter to group info on the report.

The intent of the report is to create a blank time sheet with the employee's pre-filled out on the report by date. For example if I have 3 employees and I enter into the parameter 8/19/2010 to 8/20/2010; I want 2 time sheets 1 for 8/19 with the 3 employees listed on the sheet and 1 for 8/20 withth 3 employees listed on the sheet.


Employee.tbl
------------
John Doe
Mark Smith
Larry Adams

Parameter Range
----------------
8/19/2010 Start
8/20/2010 End


Results
------------------
(Page 1) 8/19 Report
John Doe
Mark Smith
Larry Adams

(Page 2) 8/20 Report
John Doe
Mark Smith
Larry Adams

Any help would be appreciated!

 

[lbass]

Do you have a separate date field or only the date range start and end parameters? If you have a date field, then you can just group on that by date and then use the date range only in the selection formula.

-LB

 

[gwaynep99]

No I do not have a date field in the existing database where the info is being retrieved. I have tried to add another database, however the databases are hosted offsite on secure servers. While I can simulate this situation on my pc, once the report is uploaded to the server to run it can not find the file since it is not on the secure server. I have tried to upload the new database to the server, but I can not add the database to the report from the secure server location. Is it possible to utilize an array or to hard code groups into the report. Since I am trying to run time sheet records they are only needed for a maximum of a 7 day period. The groups could be hard-coded for Monday-Sunday?

 

[lbass]

Start a new report by adding some table that has a few records in it (at least as many as the number of days in the range. The content of the table is irrevelant. You might or might not need to add any field to the report header (suppress it) to activate the formula. Create this formula and insert a group on it:

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

Then you must add a subreport to the group footer that uses the table that contains the employee names. You might need to go to database->select distinct records in the subreport. Do not link the sub to the main report.

-LB