Any tutorial teaching how to design crystal report against SQL 2000? 1

[tofuTnT]

Hi,
Is there any tutorial that shows how to design crystal report again SQL 2000 server?
I have been using other applications to export the data from SQL server to Access file, then design my crystal report on the file.
Is there any tutorial that shows how to design crystal report directly against SQL 2000 server?
I am using CR 8.5 and have some experience with SQL.


Thanks a lot

Song

 

[FVTrainer]

What connection method have you been using to connect to Access? (The answer is most likely either a) ODBC; b) oledb; or c) natively, using "Database Files" in the Data Explorer).

Similarly, the most common data connections to SQL Server are via ODBC, OLEDB, and using a native SQL driver. So which way do you want to connect to SQL? [Note: if you connect via the native driver, CR 9 won't support it. If you bring the report into CR 9 or run the report using the CR 9 engine (for example, in Crystal Enterprise 9), CR 9 will convert the driver to ODBC.]

Respond and indicate which method you'd like to use, and I or someone else will point you in the right direction.

 

[tofuTnT]

I believe the method should be ODBC, I can see the database I want to connect to under ODBC category.
Thanks in advance,

Song

 

[FVTrainer]

Really, the steps are no different than reporting off of an Access database using an ODBC connection.

Assuming you have an ODBC connection to your database, here are the steps from the beginning:

1) Click on the New Report icon.
2) Select blank report (you could also use a report expert, but the rest of the instructions assume a blank report)
3) In the Data Explorer, open the ODBC folder
4) Locate the ODBC connection that attaches to your SQL Server.
5) Enter your logon and password and then click on the "Options" button. Select the appropriate database.
6) By default you'll see a Tables object, which you can expand to see individual tables and views (to which you have access). If you want to see stored procedures, you'll need to make sure that the "Stored Procedures" box is checked in the Database tab of the File | Options menu item (or, in the Data Explorer you can select the "options" button and click on the "Stored Procedure" box under the Show section).
7) Double-click on a table to include it in the report.
8) After you click on OK, Crystal will bring up the Visual Linking Expert if your report has more than one table. You will need to decide if the joins are appropriate (or make the appropriate joins).

 

[tofuTnT]

Hi FvTrainer,

thanks for the instruction. The beginning part of process is easy, but it is impossible to build a complex report.
for example, If I want to show the total amount of gifts given by a group of donors that meet some criterias. I can retrive all the gifts to crystal report then find subtotal on for gorup (assume i gorup by donor). I think SQL has function like Sum(gift_Amount) and do the caculation on the server). Is there a way to do that?
there is a new stuff call SQL Expression, does it have anything to do with that? or how can i use the SQL Designer that comes with the CR 8.5.

Thanks a lot!

Song

 

[FVTrainer]

Well, how familiar are you with grouping in Crystal, using the Crystal summary functions, and hiding or suppressing sections?

The best way to get a good response in this forum to a particular report design problem is to provide a sample listing of what you want your data to look like in the report. In the problem you described above, it's not clear what exactly your criteria is or at what level you want to apply it. Do you want to filter based on the donor total or does each gift have to meet the criteria? Provide a listing of how the data will appear, where the totals will be, and what the criteria is and you'll get a good response.

(by the way, avoid basing reports on queries you can design in the CR Sql Designer. They don't migrate well).

 

[tofuTnT]

We are going to do a major update to the software we are using. There are many reports that are in older format and I need to redo all of them. For the old reports. I need to use that software to export data into a MDB file and design reports based on the file. So everytime I refresh the report, I need to do the export and it is pain in the ass. Besides, I want to put the report in a network drive so that everyone can just open the report and refresh it. Otherwise I have to show them how to do export first and it is pain in the ass..

So I am looking for some general ideas of how to design crystal report directly against SQL 2000, not problem concerning a particular report I am working on. (well, I do have question concerning a particular report i am working on now, will post it in a seperate thread).

Thanks,
Song

 

[FVTrainer]

If you're starting from scratch, then my opinion, and I think it is shared by many here, is that using stored procedures or views is the often the best approach to reporting against a SQL Database.

Now for more of my opinion that I won't impute to other folks here: Crystal's strength is in formatting a recordset. Give Crystal a recordset that is as close to how the report should look as possible. In terms of using Crystal as your source for generating queries against SQL, Crystal handles basic joins and record selection (the WHERE clause of a SQL statement)well. But if your data needs to be manipulated (for example, doing a GROUP BY or using subselects), you are better served doing that sort of thing on the database side first, before Crystal gets the records. And most often, a stored procedure or view is the best vehicle for that. Philosophically, it moves much, if not all, of the business logic for a report to a stored procedure, which, depending on your point of view, can make report maintenance easier (or at least serviceable by people who don't need to know a lot about Crystal. As long as the stored procedure continues to return the same columns, just about any SQL person could edit the stored procs.). Many of the posts here could be much more easily addressed if the users were able to use a stored proc or view as their datasource.

That is not to say that Crystal can't handle grouping or other tasks, but simply that it is not as efficient as stored procs and views are at what are really database tasks. And if you're looking to standardize on an approach, then I wuold focus on doing what is most efficient: let your database do what it does best, and let Crystal do what it does best (format the recordset).

 

[tofuTnT]

Wow..Thanks for the advice.
That is really helpful!!

I guess I will go back to my old methold: MDB and CR.

Thanks again.

Song

 

[FVTrainer]

Well, maybe I wasn't that helpful. Pushing data into Access for SQL 2000 and then using Crystal is, at least on the surface, the worst of the three alternatives. (The three being: 1) useing stored procs and views; 2) Using Crystal directly against SQL Server; 3) pumping data to Access and reporting against the Access mdb) Why? Well, first, it's got to be a pretty slow process. And most of the time speed is of high importance in reporting applicatons. Secondly, you're adding a layer of things that can go wrong (i.e., another point of failure). Your apps have to manage connectivity to SQL and the mdb, and crystal has to be able to connect to the mdb. What happens if the mdb becomes corrupt?

Anyway, if you don't have rights to add stored procs or views, or are not skilled in stored procs or views and don't have access to someone who has that skillset, then I can't fault you for using an intermediate db like Access, but I certainly can't recommend that approach. I think if you are willing to put in the time and learn how to use Crystal properly against SQL, you will probably eliminate your need to use Access. Post questions here about specific issues, visit the Crystal Decisions website, study the sample reports that come with Crystal and see if you can't make use of Crystal without using Access.

 

[synapsevampire]

To elaborate on this a bit, there's potential for misunderstandings in FV's statements.

You can pass SQL to the database from within Crystal, including Group BY's, so use Crystal unless you have some sophisticated querying to be done (Crystal has a GUI for joining tables). The speed will not be significantly different than using Views (SP's are precompiled so they will generally be faster).

And creating Views in SQL Server is not too much different than creating queries in Access (there's a GUI to do so).

I agree wholeheartedly with FV's statement that you've selected the slowest path, but more importantly, maintenance is now threefold, and you may not understand that SQL Server allows for much greater flexibility, functionality and speed than Access.

-k