Running Total, formula to use in

[Jonathan212]

Good day everyone:

I am using a running total to summarize amount of time an operating room was used during a case. I use this to determine block utilization. That is, if a surgeon was given an operating room for 8 hours on a given day and only used 4 hours, then his/her utilization was 50%.

For the running total field, I am using a formula “roomtime” and summarizing. The formula is working fine and is:
({@PtoutofRoom} - {@roomopen})/60/60

I am evaluating for each record and resetting on change of surgeon name.

My group sort is by surgeon name.

In a few instances I am getting duplicative roomtime values because there are a few cases each month when more than one procedure is done during a case.

Might someone recommend a formula that I might be able to use in the “evaluate for each record” section of the running total to ignore duplicative, repeating instances of roomtime on a particular case.

We use a field “casenumber” (which is a unique number for each case). Ideally I’d like the formula to evaluate that if there is a 2nd duplicative (or even 3rd duplicative) case number, do not count those hours in the derivation of sum for roomtime.

I am using CR10. Thanks in advance for any suggestions. Jonathan

 

[IanWaterman]

You may have to use a variable rather than Running Total. You can have much greater control over how variable based RTs evaluate than you can get with standard RT functionality.

Ian

 

[lbass]

Sounds like you should be able to evaluate on change of field: case number. Be sure to sort by that field within the group. Or you could have an inner group on case number and then evaluate on change of group.

-LB

 

[Jonathan212]

Thanks much Ian and LB. I did not try the variable solution as yet but the sort on change of case number should do the trick.

Also, I was able to eliminate most of the duplicates by doing an isnull expression on a "deletedon" field, which is resident in most of our tables. Apparently, when the user deletes an item and replaces it with another entry, our OR system maintains the history of what was entered for audit purposes. So, if i do an isnull({pcmcase.deletedon}), for example, along with other isnull formulas, it generally cleans up all of the repeating data.

My thanks for your help.