Crystal Report 11 Data Source

We always used stored procs for one main reason, the end user generally knows they wanted columns A,B,C,D, and E on their report but we find they are always calling up and modifying the where clause or join conditions of the query. As long as the return signature of the proc is the same we can modify the proc without having to touch the crystal report.

And what about speed issue.If Crystal report has data soure stored procedure will be loaded fast or Report having datasource Dataset will be loded fast?

I'm going to guess the stored proc was faster. Once its run the first time, sql server has the execution plan figured out. If you do it as embded sql in the report, sql server compiles the execution plan each time the report is run.  This will probably only be noticeable on complicated reports.  For simple reports the user is waiting longer for the business objects web interface to load than it takes for the query to run ;)  Perhaps someone else can clarify better on the speed issue, but that is my understanding of it.

How are you going to run the reports?

If you are using an application and need to change the data source from say a DEV database to Test then PROD many find it easier to use a dataset from the application rather than having to sync the 3 databases and change the report to use them.

The most frequently asked question here is how to change the data source from code.  The answer is in general less than satisfying.  For some reason what works for one in the .Net environment doesn't work for another.

WHen using a dataset, you don't need to change the source for the report but just the source for the dataset.

Here is a sample
http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

mlmcc

true, we develop all our reports using ODBC names for the connection as well.  We have seperate test and production environments.  We make sure the same ODBC name is on each system.  The database server details of each ODBC are obviously different for each envirnment. This way we develop our locally using an ODBC name (e.g. "CONN1"), when we publish it to the test envirment there exists a CONN1 ODBC name that points to the testing database, and when we move to production there is a CONN1 ODBC the points to the production database.

That is a good way to get around it.  We did essentially the same thing.  Since our DEV server was totally independent from the PROD system we named the servers the same and had the same directory structure for where the reports were and the database server.

mlmcc