Report performance with a subreport.

[SuperTime]

Crystal 9
Sql Server 2000

I have report which looks like:

Employee Name: ABC
Date Time In Time Out Code TotalTime
3/20/2006 8:00AM 5:00PM On Clock 9:00
8:00AM 1:00PM Travel 5:00
1:00PM 1:30PM Job 0:30
1:30PM 5:00PM Travel 3:30
3/21/2006 8:00AM 5:00PM On Clock 9:00
8:00AM 1:00PM Travel 5:00
1:00PM 1:30PM Job 0:30
1:30PM 5:00PM Travel 3:30

Employee Name: xyz
Date Time In Time Out Code TotalTime
3/20/2006 8:00AM 5:00PM On Clock 9:00
8:00AM 1:00PM Travel 5:00
1:00PM 1:30PM Job 0:30
1:30PM 5:00PM Travel 3:30
3/21/2006 8:00AM 5:00PM On Clock 9:00
8:00AM 1:00PM Travel 5:00
1:00PM 1:30PM Job 0:30
1:30PM 5:00PM Travel 3:30


Now I have a main report and a sub report
The main report has the Date and Employee Name which comes from tblTimeCards

Then in subreport I have all the other information like time in, time out etc... I pass the Employee ID an Date from the main report to the subreport to get the timeblocks information

The subreport uses table tblCompTimeBlocks so based on the employee and the timecard date it pulls all the timeblock information

The issue is I have 1400 records in tblTimeCards on the main report and 2500 records in tblCompTimeBlocks on the subreport.
And the report runs very slow.

Is there any solution for this?

Please advice
Thanks.

 

[synapsevampire]

Of course there is, and try to avoid subreports in groups or the detail sections.

You might create a View which does the grouping and returns the data, or you might paste the SQL into an Add Command to provide the data, the point is to create a single recordset.

As it stands, for every employee and for every date, a new query is fired against the database, which is why it's so slow.

If you're not familiar with SQL, then ask your dba to help you build out a single query to return your data.

Or from within Crystal add in the second table to the main report and join it by the employee, then group by the employee and the date (formula for the date so that you only have the date, SQL Server doesn't have a DATE type, only DATETIME). Then nuke that evuuuuuuhl subreport...

Now you've returned all of the rows at once and the speed should be reasonable.

-k