Best method for using Crystal Reports with SQL Server?

Posted on 2005-05-16
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

    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

    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.
    LVL 5

    Assisted Solution

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now