subreport speed

[whit1950]

I have a report with a main report and subreport. Both main report and sub-report use the same data table as their primary table. The sub report is linked to the main report via a field in this common table that is also an indexed field. The report prints one page per record from this common primary table. In both the main and subreport this common table is a master table with a detail table's records being printed as detail lines in both the main and subreport

When I run the report, it takes a long time to develop. Once the report appears on screen, each time I scroll to a subsequent page, Crystal displays a message that it is generateing the subreprot and then seems to go through and re-read all the records of the detail table - which is in the tens of thousands, instead of just the detail records that should be linked to the master record for the current page.

1. Why would Crystal seem to read all detail records when generating the subreport instead of just the matching ones?
2. Is there a way to printdetail lines from the same record twice on the same page without using subreports?
3. Is there any other way to speed up such a reprot?

thank you [sig][/sig]

 

[kenhamady]

Whit,

A linked subreport, no matter how efficiently linked, will still generate a SQL call to the database EVERy time it is invoked, which means one SQL statement and call per record if you put it on the detail section. If it is reading every record you should look at the SQL statement that the subreport is generating and see if it is optimized.

But better yet, lose the subreport if you can. I am not sure how you want the records to print twice, but you could Split the detail section into A and B and put the fields in twice. This will duplicate everything, but will not allow you to group one set of details separate from another set of details.

Describe the output that you want in a bit more detail and I will see if there is a way to get the data to duplicate.

[sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Taining by Ken Hamady</a><br>[/sig]

 

[MalcolmW]

Using a stored procedure is a good way to both improve efficiency in multiple sql calls caused by sub reports, and to get the data to do weird things so that you don't even have to use subreports.
If all you need is a detail line printed twice, provide some more details, cause there are a few ways to accomplish that trick. [sig][/sig]

 

[geoffreyg]

I am having the same problem with my report. I am not sure how I can restructure the report to accomodate a second detail section. In my detail section I have two sub reports.

To better understand what I am doing, here is what report &quot;looks&quot; like.

Each detail line is a tenant in a building, Joe Smith for example. In the database, Joe is in the tenant table. This table is linked to another table called charges. This table has several records for Joe. It contains all of the items we bill Joe for on a monthly basis (future and past). For example we bill Joe for Rent, Water, Electrical (3 records). If there is a change in the amount for these items a new record is added to the charge table. Each record has a begin date and an end date so that we can keep track of the history.

On the report Joe Smith's name appears on the detail line. In the first subreport, there is a listing of the &quot;current&quot; (or active billing). In this subreport there would be one line each for rent, water, and electricity. The second subreport contains the next (future) change in amounts. So if Joe's rent is going up, there would be a list of the future rent increases in this report.

Both of these reports is set to &quot;grow&quot;. They may grow at uneven ratios. One subreport can be long the other empty or vica-versa, both empty, both long, etc. This is the reason I chose subreports as my solution.

The issue is that there are 10,000 tenants in the database with each having a minimum of 3 records in the charge table. Some tenants have more than 10 records in the charge table. As a result, this report takes so long since it scans the entire charge table containing 40,000+ records for each detail line.

I hope somone can help find a good solution!

Thanks!

 

[kenhamady]

geoffrey,

It doesn't appear that you need subreports for this. Create a formula field called &quot;Charge Type&quot; that can distinguish between a current and forward charge. Somethng like:

If {field} = &quot;C&quot;
then &quot;Current&quot;
else &quot;Forward&quot;

Now Group by Person, and then Group within that by the formula field &quot;Charge Type&quot;.

This way you only need to read the records once, and they are organized the way you want. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal