Skip to Content
author's profile photo Former Member
Former Member

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

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Nov 30, 2017 at 08:54 AM

    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


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 30, 2017 at 02:34 PM

    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


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 04, 2017 at 11:33 PM

    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.


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.