Pass Query Parameter to Subquery

Hello, experts!  I have a question regarding Crystal Reports XI.  I have a report that pulls data from 3 different tables.  One table returns 'header' type of data (one row), while the other two tables return 'detail' data (multiple rows).  

What I've done is created a report using

SELECT * FROM my_header_table WHERE order = {?order} (for example)

and I have two subreports:

SELECT * FROM my_detail_table1 (for example)
SELECT * FROM my_detail_table2 (for example)

These are linked to the main report through subreport links. This takes an exceedingly long time to run.  If I change my subreports to:

SELECT * FROM my_detail_table1 WHERE order = {?order}  

(with no subreport links) then the report runs much more quickly.  The problem is that the user must then enter the same number 3 times.  Can I pass the parameter from the header table to the queries in the subreports?

Who is Participating?
PATKIRSCHConnect With a Mentor Commented:
first = concur with mlmcc = for a shor term/mmediate solution, use the <change subreport links> dialog box to create a link between the main report and each subreport (reach this dialog box by <right clicking> on each subreport, and then choosing this option... It looks like for your situation, you will also check the box regarding selecting records within the subreport based upon the link... (note: this will not speed up the report because the subreport runs its query multiple times...)

second = also concur with mlmcc - you might want to see if you can modify your sql query to eliminate the need for the subreports entirely... this can likely be done via a Union Query (based upon the look of your information above) and or other linking... did you want to expalin a bit further what information you are working with and/or why you need the 2 subreports (e.g. could "groups" based upon formulas work as well for you, etc.... so, please provide a bit more details so that we can help you better evaluate your needs...

best wishes, :) Pat K
mlmccConnect With a Mentor Commented:
Any reason you can't join the tables and avoid the need for a subreport?

You should be able to link the main report parameter to the subreport parameter on the subreport link page.

woks fo me - thanks, mlmcc :)
Glad i could help

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.