Selecting only those records where a record appears in a subreport

[richardtm]

Using Crystal 8.5. I have a report grouped by date. The details section includes patient name, medical record number, subreport 1 (which contains only one field), and subreport 2 (which also contains only one field).

I'd like the report to only generate data on those records where there is data entered in either or both of the subreports.

Eg)If there are 5 total records for a date period, and only one with data in subreport1 and two with data in subreport2, the report should generate only those 3 records.

Thanks for any assistance!

 

[lbass]

How are the subreports linked to the main report? Let's assume they are linked on medical record number. I think you would have to add the subreport table (let's call it Table2 to your main report, with a left join from the main report table (Table1) to Table2. Repeat for the second subreport (Table3). Then you could use a record selection formula of:

not isnull({table2.medicalrecordno}) or
not isnull({table3.medicalrecordno})

-LB

 

[synapsevampire]

If the subs are only returning one row each, you are probably better served to use a subquery to return that data either with a View within the database, or perhaps using a SQL Expresssion within Crystal.

As for your current design, as LB points out, your approach may be backwards, you might find it easier to create a Union of the tables in the subreports for use as the main report, and then use the current data in the main report as a subreport, but again, try to avoid subreports altogether.

-k