Learn how to a build a cloud-first strategyRegister Now


Pass Query Parameter to Subquery

Posted on 2006-05-04
Medium Priority
Last Modified: 2007-12-19
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?

Question by:mixxie
  • 2
  • 2
LVL 101

Assisted Solution

mlmcc earned 750 total points
ID: 16611711
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.


Accepted Solution

PATKIRSCH earned 750 total points
ID: 16612496
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

Expert Comment

ID: 16790421
woks fo me - thanks, mlmcc :)
LVL 101

Expert Comment

ID: 16795185
Glad i could help


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month21 days, 2 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question