Crystal Report Problem with sub reports that contain large volumes of data

I have a Crystal Report that has a main report and 8 sub reports.
The main report contains one record per employee, and each of the sub reports displays information about different data pertinent to the employee, e.g. cars, loans, medical benefits, etc. Each sub report may have zero – many records.

The application that runs this report is supplied to a variety of customers. These customers may have MS SQL Server or Oracle databases.

The Crystal Report is invoked via a VB.NET program. The program reads all the data required from the database into a dataset. The data is passed to the report using ReportDocument.SetDataSource for the main report table and ReportDocument.SubReports.Item(“xxx”).SetDataSource for each of the sub reports.

Each of the datatables is described using an XSD, for the main report data and each of the sub report’s data. The Crystal Report uses these XSDs to describe the data and create the report fields.

The main datatable has approximately 25,000 records.
Subreport 1 has approximately 31,000 records.
Subreport 2 has 5,000 records.
Subreport 3 has 2,500 records.
Subreport 4 has 0 records.
Subreport 5 has 30,000 records.
Subreport 6 has 2,500 records.
Subreport 7 has 1,000 records.
Subreport 8 has 1 record.

These numbers may be higher in future. The more data records that the sub reports return makes the report exponentially slower.

When we are dealing with smaller amounts of data :- eg. Maximum of 1,000 records for each, then the report runs through in a few minutes. With the data as described above it was running for 2days and it still didn’t finish! Obviously NOT ideal for the customer.

As a test I have tried to using OLE DB to access the database direct, so that I could use ADD SQL Command to select the data for each subreport, but I could not find any way of reducing the number of records selected. It would dramatically reduce the time of the report if I could pass a “Value” from the main report into the Command SQL in each of the subreports, so that in the WHERE clause it has EMP_ID = {passed in EMP_ID} from the main report. This EMP_ID changes for each of the 25,000 employees in the main report. It seems that you can pass in a fixed parameter and not a dynamically changing one. Don’t know too much about SQL Expressions and whether or not they may be of any use.

Obviously I would like to keep the report as it is with regards to using ADO.NET, just with some magical workaround to speed up the report.

I have spent days googling for possible solutions and none have yet to be suitable, obviously avoiding sub reports is a good answer, and I have avoided them in the other reports and they run very quickly, but unfortunately this report cannot be written without using them as far as I can tell.

Any help on this would be greatly appreciated as I have no more ideas left to try.

Regards,

Wayne.