title: Crystal Reports: Need advice on how to avoid use of subreports
body: <p>I'm trying to write a report to hit a very large database, which makes the use of subreports... unpleasant to say the least. How can I avoid them in this situation:</p>
  
  <p>I'm starting from a table of patient visits. Each visit can have multiple vital readings (Say in a 'Vitals' table that is many-to-one with the visit table) of which I want the min and max of each type. So to accomplish this, I group by the patient visits and then use formulas to calculate the min/maxes and display them in the group footer. So far so good. However, there is another table, Orders, that contains all the orders for that visit as well, of which I also want to select some min/max values to display for the encounter.</p>
  
  <p>To skirt using a subreport, I added some more formulas to the suppressed details that also find the min/max of these order values and display in the group footer. The problem is that Crystal only returns patient visits that have orders (despite being outer-joined), and what I need is a list of ALL visits that may or may not have any orders whatsoever. To complicate matters further, there is yet another Orders table I need to do this with as well (one for medication and one for procedures). Any ideas?</p>
  
  <pre><code>(visitID, patName, visitDate)
  ------------------------------------------------------------------
  1, Patient A, July-24-2009
  2, Patient B, July-25-2009
  
  (visitID, line, medName, dosage)
  ------------------------------------------------------------------
  1, 1, Aspirin, 200mg
  1, 2, Aspirin, 500mg
  2, 1, Codeine, 100mg
  2, 2, Codeine, 200mg
  2, 3, ValerianCap, 700mg
  
  (visitID, line, procName, procType, cost)
  ------------------------------------------------------------------
  1, 1, Xray-wrist, xray, $500
  1, 2, Xray-elbow, xray, $300
  
  </code></pre>
  
  <pre><code>+-----------------------------------------------
  | Patient A
  |   Med Orders:
  |     Max Aspirin Dosage: 500mg
  |
  |     Proc. Orders:
  |     Max cost Xray: $500
  |
  +-----------------------------------------------
  | Patient B
  |   Med Orders:
  |   ...
  +-----------------------------------------------</code></pre>