I am trying to compare the difference in two date-time fields suing the PREVIOUS function in Crystal. My problem is that Crystal won’t let me summarize my result in any way, whether it’s a Count, Average, or anything else. I get the error: “This field cannot be summarized”.
My two date-time fields are:

-Patient_Datetime_IN_Room.
-Patient _DateTime_Out_of_Room
I am calculating the room turnover (Room T/O), that is, the minutes between when, say, PT (patient) A was wheeled out of surgery, and the NEXT PT was wheeled in to the same room to undergo surgery.
Here’s what my Detail section on my report looks like
PT # PT Room Patient_Datetime_IN_Room      Patient_DateTime_Out_of_Room   room T/O
1     10          8-5-13 07:15:00               8-5-13 9:00:00               none (1st case of day)
2     10          8-5-13 09:15:00               8-5-13 10:00:00               15

I am trying to average the turnover at a monthly level (across all room), which means I need a fomula to calculate:
Jan T/O Avge: 25 Min
Feb. T/O Avge: 20 min
I also need to group by room at a later time. (i.e., Avge turnover for room 10 for Jan: 15 min, etc)
So, room T/O is PT 2’s time In minus PT 1’s time out of room. (9:15 minus 9:00=15 min)
My formula to for the detail section to calculate room turnover is:
//Formula: @turnover
(if datediff ("n", previous({EpicCases.Pat_DateTime_ OutofRoom}),{EpicCases.Pat_DateTime_InRoom})>=60
or datediff ("n", previous({EpicCases.Pat_DateTime_ OutofRoom}),{EpicCases.Pat_DateTime_InRoom})<0
then tonumber({@fnull})
else
if(time({EpicCases.Pat_DateTime_InRoom})in time(6,30,00) to time(17,00,00))
then
datediff ("n", previous({EpicCases.Pat_DateTime_ OutofRoom}),{EpicCases.Pat_DateTime_InRoom}))

First, I am filtering out any turnover time between cases greater than 1 hour. Also, using an empty formula (@fnull), I’m excluding data entry errors where the wrong time was entered which leads to a minus turnover. (Rare, but it happens).
Next, I use the PREVIOUS function to calculate the minutes elapsed between time in to room for the most recent patient vs. time out of the room for the patient immediately before him/her, for the same room.
As I mentioned, when I try to do any summary calculation, I get the error: “This field cannot be summarized”.
At first I thought that perhaps my @turnover formula was not a number datatype. However, it is a number. I even tried creating a new formula called @makeTurnoveraNumber, and used the tonumber convert function on my @turnover formula, like this:
//formula: @ makeTurnoveraNumber
Tonumber({@turnover})
It did not work. Evidently, using the PREVIOUS function eliminates any possibility of summarizing anything in which it’s used, is that right?
I’m running Crystal Reports 10.0.0.53 (Crystal 10).
If so, what can I do to summarize the @turnover at both the group, and report level?
Any help would be appreciated. Thank you in advance.


Edited by kitster100 - 13 Sep 2013 at 4:45am

you can't summarize on any value that is not 'directly' available in the data. By directly, I mean that all that has to be done is reading the value. Any calculation or logic applied to the value negates it being 'directly' available (exception, field1 * field2 is used....this is still directly available)

To accomplish aggregates you might be able use running totals (never been my forte...DBlank's) or shared variables can be used.

to calculate an average you would need 2 variables(at least) and 3 formulas, nothing too complex. This example uses the assumption that the rooms are being grouped on:

reset formula - usually in a group header:
shared numbervar avgTot:=0;
shared numbervar avgCnt:=0;
""//hide the 0

increment - usually in details:
shared numbervar avgTot;
shared numbervar avgCnt;

if (your logic here) then (
avgTot:=avgTot +{table.field};
avgCnt:=avgCnt + 1; //I can never remember if the ; is needed or if it throws an error
);
""//hide the formula from displaying on the report

display of average - usually group footer:
shared numbervar avgTot;
shared numbervar avgCnt;
avgTot/avgCnt

I don't tend to like Previous() because it doesn't care about group boundaries and acts oddly at the beginning and ending of the dataset. I am not sure that min/max will be easy to use...but I've been wrong before.

If you want a 'total' average for all rooms, you can easily extend the method by: increment more variables in the detail section or increment more variables in the group footer section...it's up to you.

Again Running Totals should be able to do the same thing...just for some reason they confuse me.

HTH

No it hasn't changed, and I doubt that it will. Given that Crystal Reports already reads the data 2 or 3 times, to allow it to summarize would mean another read, and probably more memory usage and a slower display...so I wouldn't expect it to change.