you could write a stored procedure and have the subreport use that. Otherwise, if you are joining directly to the table and using a record selection formula, CR is generating a sql command that incorporates the parameters...so it isn't reading all the data either.

The 'biggest' gains would probably be achieved by making the whole a stored proc and calculating the values that the subreports return.

Why?

Because every time a subreport is called a new connection to the database is opened and the data is read from the tables.

So the standard example is: if you have a report with 100 rows of details and each detail has a subreport, then the report will connect and read from the database 101 times.

If you got all the information that you needed the first time, then the number reads would be 1...a big time saver.

I do know that this solution is not for everybody based on some shops not allowing stored procedures to be written and/or no not knowing how to write a procedure.

With that in mind, this would be the biggest speedup. Specialized views or optimized procs would be the other way to go.

As you can probably tell, I try to avoid subreports if at all possible, because of their performance hits.

Edited by lockwelle - 18 Apr 2014 at 5:16am

What about using the add command feature to create an SQL statement to filter out unwanted data. It basically is a simple stored procedure that gets executed BEFORE any CR passes, right?

it should be quicker, as long as you replace the subreport with the data from the command...
I like stored procedures because of the versatility, much of which revolves around temp tables and being able to tailor the result set...
There are reports that are simplified if not totally impossible to achieve without a stored proc.

Again, it depends on what data you can extract using the command and how you combine it...and getting rid of the subreports (I guess that is really the key)