I have a CR8.0 report that has a main and subreport. Both reports are based off of the same SQL Server 7.0 stored procedure. I am also using VB7.0 as the front-end application bit. The report has 3 selections for the user: run for a given stock number (this is an inventory management system); run for a given rollup description (group of like stock numbers); or run for all items. The first 2 selections run OK, but when I select all items, the report runs for over 3 hours. I pass 4 parameters to the stored procedure for both the main and subreport. Anybody have any tips on how I can make the report run faster? I'd be happy if it just took 30 minutes to run.
The subreport calls the stored proc for every result of the main report. This is never ever fast.
You wrote that the main report and the subreport are using the same procedure. Do you really need the subreport? Is there no other way to show the data?
Unfortunately, I need to have the data. It summarizes all of the rollups for different warehouse locations. I tried removing the subreport and adding another database to the main report, but you can't link a stored procedure to anything else.
The subreport sumarizes the same data, as there already is in the main report, why do'nt you use formulas, to calculate the needed results?
And it is not only that the report needs 4 hours, you also generate a lot of traffic on your database server.