Creating a report with specific records based on one field and a date field

Need to create a summary report that is the rollup of specific detail records (see attachment).
At the detail level we need to somehow tag the first patient visit with the selected service and sum the charges and costs from that visit and subsequent visits only. We cannot include any charges and cost for discharge prior to the selected record. Is this possible?

Doable… but some questions to determine the best approach:

  1. How are you identifying the selected service to group on? Is the user prompted or is it based on a data element? Does it change?
  2. All services after the first for the selected services should be included? In your example, the ED visit is included in the PC service summary even though it’s not a PC encounter?


  1. based on a prompt so it could change
  2. yes! :slight_smile:

You’ll need a bunch of variables. Here’s the gist:

  1. User response to your prompt
  2. Min date in patient id (you want a unique id, not name) where service = user response
  3. If date >= min date from above then 1 else 0
  4. count/sum visit id/charge/cost where = 1
1 Like

Thanks! We will have multiple patients along with their visit IDs. Will this work for all of them? Will we have to reset between patients or is that accounted for?

One more question. It looks like the change in patient is in there but how do we write the
“min date in patient id”. I am new at this. How would this be written in a variable please?

If the unique Patient ID (NOT Visit ID) is in the data block: Min( [Admit Date] ) in( [Patient ID] )

If it’s not in the block, use ForEach instead of In: Min( [Admit Date] ) ForEach( [Patient ID] )

In/ForEach provides the calculation context that tells the system what dimensions to include when calculating (you can exclude dimensions using ForAll).