Compute Room Turnover Time without Room Sort

[5lights]

Using CR8.5
Main report shows OR Case info sorted by StartDateTime for all ORs in a given daterange.
I need to compute Room turnover time.
Tried a SubRpt linked by date & room and was able to compute Turnover's with a Start-Previous(End) formula. But could not pass it to the MainRpt tied to the appropriate case. How can I get the right Shared NumberVar? OR, Can I limit my SubRpt to just Current & Previous Case, then just get one SharedNumberVar per record?
Suggestions?

 

[lbass]

Try adding another link on case.

-LB

 

[5lights]

Then I'll just get the one record & unable to use the PREVIOUS function. I need the same room, but two sequential cases to do the computation.

 

[lbass]

Assuming that the case ID is unique to each row in the main report and that the subreport is located in the detail section, then I think you can do the following. In the main report, go to edit->subreport links->and add {table.caseID} as a linking field from your main report. In the bottom left, {?pm-table.caseID} will appear. Then uncheck "Select records based on this field". Leave the links on room and date as is.

In the subreport, sort the records by startdatetime and then create the following formula and place it in the detail section:

whileprintingrecords;
datetimevar startdatetime := datetime(date({table.startdatetime}),time(8,0,0));
shared numbervar dur;

if {table.caseID} = {?Pm-table.caseID} then
if onfirstrecord then
dur := dur + datediff("d",startdatetime,{table.startdatetime}) else
dur := dur + datediff("d",previous({table.startdatetime}),{table.startdatetime});

In the subreport report footer, place this formula:

whileprintingrecords;
shared numbervar dur;

In the subreport report header place this formula:
whileprintingrecords;
shared numbervar dur := 0;

Now you can suppress all sections of the subreport except the subreport footer. If you want to use the variable in a calculation, then insert a detail_b section where you do the calculation, e.g.,

whileprintingrecords;
shared numbervar dur;
numbervar adddur := adddur + dur;

Note that in the detail level formula within the subreport I created a startdatetime variable that I set at 8:00 am. You should add your own starting time so that cases that are the first for a particular room will show the correct value--or maybe you want it to be zero? If so, just change the formula to:

whileprintingrecords;
shared numbervar dur;

if {table.caseID} = {?Pm-table.caseID} then
if onfirstrecord then
dur := 0 else
dur := dur + datediff("d",previous({table.startdatetime}),{table.startdatetime});

-LB

 

[5lights]

WhooooYaaaa!
(Thats Okey' for "Now you're talking")
Looks good. I'll let you know how it turns out.

 

[5lights]

Worked like a charm.
I had to modify some for my data peculiarities
(ie startdatetime only has time, date is 1800,01,01)
so my solution was:

whileprintingrecords;
shared numbervar RmTO;

if {table.CASE_NO} = {?Pm-table.CASE_NO} then
if onfirstrecord then
RmTO := 0 else
RmTO := (datetimetoseconds({table.ENTER_ROOM_TIME})-DateTimeToSeconds(previous({table.LEAVE_ROOM_TIME})))/60;
RmTO;

Thanks for the assist'