Crystal Reports: How to restrict records in main report if a subreport contains a specific value

[former_member289643]

My main report groups records by invoice number. Each invoice number has multiple records, each of which has a unique billing id, and which also has a billing status. (The two options for billing statuses are Pending and Paid.)

I have sorted the records so the highest billing id (i.e. the most recent) is at top, and then I put that in my group header, so that only the most recent record appears. (I have suppressed the details section so the only thing visible in the report are the group headers.)

I then created a subreport (using the same data source) that only includes records that have a billing status of Paid, and I joined the subreport to the main report via the invoice number. (The subreport is in the group header of the main report.)

I want to set it so that the only invoice numbers that appear in the main report are those that have Paid in their subreport.

Any advice?

[DellSC]

Or you just link the table with the invoices to your main report and in the Select Expert, set it to select only the Paid invoices. Your existing main report should still work. However, it will only show customers that have paid invoices. There is a way to do this where you'll get all customers, but only paid invoices using SQL. If you'll post the SQL from your main report and the subreport along with the formulas from the Select Expert, I should be able to write the query that you need, and explain how it works.

-Dell

[Former Member]

Then you will have to reverse report.

Subreport becomes main report and Main becomes sub.

However, that will mean Main report executes multiple times ie once for each Invoice in SR

Ian

[Former Member]

Personally I would remove subreport and use its SQL to create a command.

You can then join main report query to Command using an Inner join on invoice number.

You might have to change Subreport sql slightly to bring back a unique list of Invoices

Ian

[former_member289643]

Thanks Ian...I agree that modifying my SQL makes most sense. But my SQL skills are pretty primitive, and so I'd prefer to modify the report instead.