Best method for using Crystal Reports with SQL Server?

Posted on 2005-05-16
Medium Priority
Last Modified: 2011-09-20
I wanted to know the best method to have Crystal Reports pull data from SQL Server without causing great strain on an existing network?

I saw a method of which a stored procedure was accessed through the connection and was considering doing that again.  I'm much more familiar with Access Reports and am wondering why can't I just make something similiar to the Access queries in my SQL Server machine?  I guess a View is similiar to Access' queries in that its a view of the data and that may be quite faster.

I am currently under the impression that the only way I'm going to get a SQL Server query to show up through an ODBC connection is having it as a stored procedure and that I really couldn't run queries except through Query Analyzer.  Since this part of the question relates to SQL Server - I am asking it here even though it involves Crystal Reports also.  Please correct my faulty reasoning!  Thank you.

Question by:stephenlecomptejr
LVL 11

Assisted Solution

andrewbleakley earned 640 total points
ID: 14014840
I use views from sql server to generate crystal reports. I can save a lot of time that way and I havn't found any significant network strain yet.
make sure whatever method you use you only return the exact fields and rows you need so that unwanted data isn't being sent.
You are right views are the same as queries in access.
your last paragraph is a bit confusing, can you try it again I am sure a solution can be found for you.
LVL 13

Accepted Solution

TommyTupa earned 680 total points
ID: 14014995
Let me start off by saying that I can't really speak for the network side of things, but I'll throw in my two cents because views and ODBC will not be your least intensive data retrieval methods.  

From the SQL Server and CPU side of things, your least intensive methods will be stored procedures accessed via ADO.

Views are good but slower than stored procedures as the execution plan will need to be generated each time the view is accessed.  Stored Procedures do not need execution plans regenerated and allow for formulas and other logic thus assuing processing is being done on the server where it belongs.  In about two weeks of practice you'll find Stored Procedures easier to write than MS Access views, and much more powerful.  

Micrososft Data Links (ADO) as opposed to ODBC also has benefits such as improved connection pooling. To create a Microsoft Data Link, first create a text file and then rename to mylinkname.udl.  Double-click on the link, select SQL Server as your connection type and continue.  These are more transporable and offer other benefits.

Assisted Solution

SaxonWica earned 680 total points
ID: 14015313
You can try creating stored procedure with parameters. And you can also try using simple selection formula.

From my past experiences, using view and selection formula on crystal repot will do the following : The crystal report will pull all the data generated by view command, then only get some of the intended data. You can notice this by reading progress on the bottom right of your Crystal Report designer. This happens a lot when you're using a bit complex selection formula. But when crystal report recognizes your selection formula as a simple one, it will include it on the sql syntax that crystal report made as "WHERE" command. (You can view the commands from Database menu -> Show SQL Query).

This won't happen on stored procedure with parameters, and of  course, this will reduce the strain on your network dramatically.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question