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

[Former Member]

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.

[Former Member]

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?

[former_member11696]

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

[former_member11696]

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

Don

[abhilash_kumar]

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