How to link subreports in Crystal Reports?

Question

I am using Crystal Reports 2008. I want to create a report where I will get the value from one SQL and that value field I have to pass on another SQL and will get the result. Is that possible to do that?

Answer 1

Normally (but not always), you can combine the two SQL statements into one - avoiding the complexities of sub-reports.

For (over simple) example:

SELECT CustomerNum    -- Returns number 919
  FROM RecentOrder
 WHERE OrderNum = 23455;

SELECT FirstName, LastName, EmailAddress
  FROM Customer
 WHERE CustomerNum = 919

These can be combined in several possible ways:

SELECT FirstName, LastName, EmailAddress
  FROM Customer
 WHERE CustomerNum = (SELECT CustomerNum
                        FROM RecentOrder
                       WHERE OrderNum = 23455
                     )

SELECT FirstName, LastName, EmailAddress
  FROM Customer AS C JOIN RecentOrder AS O
       ON C.CustomerNum = O.CustomerNum
 WHERE O.OrderNum = 23455

Depending on your DBMS, you may be able to use a WITH clause up front, too:

WITH name1 AS ...query1...:
SELECT ...
  FROM ...list including name1...
 WHERE ...

It is worth spending some effort combining the queries that make you think you need a sub-report. If you still need a sub-report, then you need more specialist help than I can offer.

Answer 2

First, I'd recommend Jonathan Leffler's answer. It is much better if you can get the data formatted the way you need it using a query than trying to use a subreport placed in the detail or group sections. Putting a subreport in the details section is essentially like running a report for every record that the main report pulls.

With that said and to answer your question, when you add a subreport you can right click on the subreport and choose "Change Subreport Links". Here you can link the main report data to the subreport data.

EDIT in response to your comment

1) There are two options that you can use here. One would be to create a parameter in the subreport, add your logic in the Select Expert of the subreport so that it uses the new parameter and then link the column in the main report to the parameter in the subreport using the Subreport Links screen.

2) Set up a shared variable in the main report and in the subreport that you can then use in the Select Expert formula. For more information on this see the following question. http://stackoverflow.com/questions/1268348/retrieve-record-count-of-subreport-in-crystal-reports/1268827#1268827

Hope this helps. Don't forget to upvote all answers that helped and mark a correct answer for your questions if it is correct.