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

Crystal runs sql command of first sub report in the second sub report ?

Hi,

I have report that contains 3 sub reports.

Each subreport is running his own sql command to retrieve it's data.

When moving from the first subreport to the 2nd subreport, we see on the oracle that the crystal runs the sql command of the 1st subreport, and then the sql command of the 2nd subreport.

The results are fine (he ignores the 1st subreport result in the 2nd subreport), but it delays the showing of the 2nd subreport to show.

Why? The 2nd subreport doesn't need the data of the 1st subreport!

How do I cancel that????

BTW - The 3rd subreport is running only his own sql command (As should be).

Thanks.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Feb 08, 2012 at 07:36 PM

    Hi,

    There is nothing wrong in the way Crystal Reports is executing the SQL queries. CR follows a top-to-bottom, left-to-right processing approach.

    Suppose Subreport 1 is placed in Report Header a and Subreport 2 is placed in Report Header b, Subreport 1's sql query would always be processed before Subreport 2's query. That is because, Report Header a falls before Header b.

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 08, 2012 at 08:44 PM

    Have a look at the Subreport Links also, if the 1st subreport gets a value it will run.

    Don

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 09, 2012 at 09:56 AM

    Hi,

    Thanks, but:

    Sub report 1 is on 'Details a'. Subreport 2 is on 'Detaild b', subreport 3 is on 'Details c'.

    The size of each report is one page,

    Each report is linked only to the main report, which is actually mainly some info such header and parameters showing.

    When I look at page 1 (subreport 1 is running) then it takes about 5 minutes (This is the time of the sql query to run).

    When I move to page 2 (subreport 2), it takes 13 minutes (We saw on the oracle server that 2 sql queries are running:

    The query of the firsr page (subreport 1) which takes now 8 minutes, and right after that the query of the second report is running for 5 minutes.

    After those 13 minutes, I see the results of subreport 2.

    When I move to page 3 (subreport 3) only the query of this subreoport is running for 3 minutes and I see it's results.

    Any idea why?

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello,

      Not without more info about the report and the data. If there is nothing linked from the main report to sub1 then it's going to run and return all data, no filtering. And from the sounds of it to take 5 + minutes per query this is going to affect performance for the whole report.

      You may want to re-evaluate why and what the subreports are used for. If they all use the same data the link them in the main report use groups to sort the data.

      Having subreports in the Details section is asking for performance problems, every record returned will cause the subreport to run. Not knowing what the subs are doing I can't say for sure....

      What you should do also is re-evaluate your Commands for each sub and see if you can do it either in one SQL statement or write a Stored Procedure to do all of the data collection. DB Servers are much more efficient at collecting data than CR will ever be. Pushing just the results to CR makes it simply formatting the results in the Designer and no performance hits....

      Talk to your DBA on how to optimize your Command SQL's and how to get them into one SQL or into a SP that dumps the final results into a final SQL * from TEMP Table into Crystal.

      Don

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.