Crystal Reports XI and SQL Server 2k

Question

Crystal Reports XI and SQL Server 2k

ChrisM@Work

SSC Guru

Points: 186127
More actions
December 16, 2007 at 4:50 am

#93135

Hello
Asked recently to examine a few custom Crystal reports which were performing rather poorly, I found two common reasons. One of them was a query command containing 72 correlated subqueries which took about 25 minutes to run - changing to a join cut this to about ten seconds. Another report returned 4.4 million rows to Crystal for filtering and processing. So the two main reasons for poor performance are poorly written queries, and absence of selection at the server side. I fixed these two reports using stored procs with parameters.
The problem is, I'm no Crystal expert, there are other reports with the same or similar problems, and my learning curve exceeds the client timeframe. I'm looking for a resource which will help, either a book or a forum. Crystal XI & SQL Server2k. Lots of examples of parameter usage. Any ideas?
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Yvan Bouchard SSCrazy Points: 2506 More actions · Answer 1

Hi Chris,

I have been working here with Crystal 10 mostly so far, upgrade is next year, with SQL 2K and after a lot of frustrations, I am now using Crystal only for the presentation part of the report. Crystal is great for that but rather poor on performance when you have multiple tables. What I do now is to make sure I have all the data prepared and ready for Crystal, so I transfered to joins, data manipulation back to the DB layer, using functions, stored procs or views.

Stored procs parameters creates your parameters in Crystal so if you know SQL, better off do your stuff in SQL and then, use Crystal to display it.

Sometimes, you may have to build a temp table in SQL just so you can have your data regrouped properly.

Sorry, I cannot refer any litterature, a lot of it is done on your own. There are some Crystal Reporting advanced courses, but it does not fix the performance problems.

Transfer the heavy work to the SQL Server and you will be ok.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Yvan Bouchard (12/17/2007)
Transfer the heavy work to the SQL Server and you will be ok.

Hi Yvan

Many thanks for the quick reply. I've figured out that it's the SQL side where the real work needs to be done, but the area where I'm having trouble is parameters. To reduce the result set returned to Crystal, I need to use parameters - but if I use a sproc as the data source, sure I can pass parameters into it e.g. company number - but what I can't figure out is how to make such a parameter list-based! If I use a subreport i.e. add one of my dodgy financial reports to an empty report, which is just a vehicle for collecting parameters - then I can make it work exactly how I want: company number and fiscal year are picked from lists (from views on the server), and the remaining three parameters are picked up on the same parameter sheet. But - how this sucks bigtime - Lawson Business Intelligence seems unable to drive a published linked subreport.

In plain English, I want to collect 5 parameters on one sheet as soon as the report is selected. The first two parameters are company number (from a picklist, displaying the company name, and driven by a view), and fiscal year (from a picklist). These first two parameters are then used with the sproc to fetch data from the server, and the remaining 3 parameters are used to further control filtration / display of the result set. And it looks like I need to do this in one report, no subreports.

This seems to me to be a fairly typical setup and I'd be very surprised if Crystal can't do it, but I don't have sufficient experience with it's somewhat counter-intuitive workings. If you have any hints, then I'd be very grateful!

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Yvan Bouchard SSCrazy Points: 2506 More actions · Answer 3

The problem here is that Crystal will prompt you for the parameters as soon as you run the report, so you can't pass the value to the stored proc. I do not know if it can be done. What I would try is to NOT have any parameters on the stored proc, meaning, the proc will return all the data ready to be displayed, but not filtered. Then, you use the Crystal side to get the view driven parameter list to filter the data. Try it, you might be surprised of the performance difference, since the bottleneck is the multiple joins.

If you have a lot of data, this solution might not be the perfect one but it can help. Maybe you can pre-filter, knowing that, for example, you might not need the past 5 years of data or something like that.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Thanks again...but...one of the reports returns 4.4 million rows and since this is financials, the NOLOCK hint is "probably best not used" (see today's editorials). This one report can and does cause problems.

I've got it working without picklists by passing all of the parameters into the command object whether or not they are used - and this version publishes to Lawson.

I've got it working with picklists by collecting company name and fiscal year in a main report with the financial report embedded in it as a subreport. As a bare Crystal report it's perfect (it takes 3 seconds to run and uses 1 parameter sheet instead of 25+ minutes with 2 parameter sheets), but I can't get it to work once it's published. I'm finding some hints on the LawsonGuru website that it's possible to get subreports working but "Lawson doesn't like them and it's not recommended".

More experiments...

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Key DBA SSCertifiable Points: 6029 More actions · Answer 5

Chris,

We have a SQL Server 2000, Crystal Reports XI and Lawson 9, with Lawson Business Intelligence 9 environment (LBI).

The Crystal Report and SQL Server issue is difficult enough, but adding in the Lawson application structure adds a TON of complexity.

For a Crystal Reports book, I would recommend "Crystal Reports XI: The Complete Reference" by George Peck. ISBN-10: 007226246X ISBN-13: 978-0072262469

or

http://www.amazon.com/Crystal-Reports-XI-Complete-Reference/dp/007226246X/ref=pd_bbs_1?ie=UTF8&s=books&qid=1197905238&sr=8-1

There are not many Crystal Reports books out there. The book will only get you so far, but it is a start.

I have developed a Crystal Report that hits a couple of GL tables where the report parameters of a start data and end date are passed to a Stored Procedure.

If you would like copies of the report and SP, please send me a private message. There is too much Lawson-related detail to cover in a forum thread.

Happy T-SQLing

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Damon Wilson (12/17/2007)
Chris,
We have a SQL Server 2000, Crystal Reports XI and Lawson 9, with Lawson Business Intelligence 9 environment (LBI).
The Crystal Report and SQL Server issue is difficult enough, but adding in the Lawson application structure adds a TON of complexity.
For a Crystal Reports book, I would recommend "Crystal Reports XI: The Complete Reference" by George Peck. ISBN-10: 007226246X ISBN-13: 978-0072262469
or
http://www.amazon.com/Crystal-Reports-XI-Complete-Reference/dp/007226246X/ref=pd_bbs_1?ie=UTF8&s=books&qid=1197905238&sr=8-1
There are not many Crystal Reports books out there. The book will only get you so far, but it is a start.
I have developed a Crystal Report that hits a couple of GL tables where the report parameters of a start data and end date are passed to a Stored Procedure.
If you would like copies of the report and SP, please send me a private message. There is too much Lawson-related detail to cover in a forum thread.
Happy T-SQLing

Christmas comes early this year! :o)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Hi Damon, I'm picking up some useful information here, might be worth a look for you too.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Key DBA SSCertifiable Points: 6029 More actions · Answer 8

Chris,

Thanks for the link, I have added the site to my Favorites. I will be sure to get it to our Lawson Admin as well.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Hi Damon

Here's the current scenario: "Summary Profit & Loss - Consolidated" is a linked report; the main report picks up CompanyGroup and Fiscal Year from static lists. It also picks up, on the same parameter sheet, Month/Quarter (choice), then either the month, from a static list, or the quarter, from a static list. That's all it's for. The subreport is then run. CompanyGroup and Fiscal Year are passed to a sproc to restrict the result set, and the remaining three parameters collected from the main report are then used to filter/group/display the retrieved data. This all works perfectly once published to LBI.

What I'd like to do now is make the CompanyGroup and Fiscal Year work dynamically from views on the reporting db (on the same server as PROD). It works seamlessly and perfectly in Crystal but I can't get it to work in LBI. The controls which Lawson exposes when I attempt to do this suggest that it should be possible.

I'll let you know!

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dbajunior SSCrazy Points: 2307 More actions · Answer 10

Chris - I feel your pain.

Here is a link for you. I am NOT spamming for this guy. He has helped me a lot and some of the stuff (under /bookmarks.html) is free. Check it out. He has a periodic newsletter as well.

http://www.kenhamady.com/

FYI:

We use CR 10 with SQL 2005. Most of our reports use SQL procs. Some use tables with data generated in a prev SSIS pkg. And many use parms that actually reside in the SQL procs.

I did write a VB.Net console application (which accepts report params) that is executed via Windows Scheduled Tasks (creates Crystal report, emails it) with a report id tied to a SQL table, and a param. Example : exec c:\Crystal\CrystalConsole.exe "22" "JDOE"

The SQL table includes fields like: report_id (22 in this case), crystal report file name (xxxxxxxx.rpt), report file path (where is it located?), server name, db name, username, pw, email addresses (mail_to, mail_cc, mail_bcc), export file type (PDF), smtp server name, email subject text, email body text, etc.

This setup works quite well for scheduling/running reports at night.

I wish you the best. Crystal is the reason I'm bald now!:doze:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

James A. Lawrence (12/19/2007)
I wish you the best. Crystal is the reason I'm bald now!:doze:

Haha I know what you mean! Most of the problems are now solved by using subreports, collecting parameters in the main report to pass to the sproc called by the subreport. I'm still having some problems where parameters are chained - oh well.

I'll check out your link though..."every little helps!"

Many thanks, James

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Key DBA SSCertifiable Points: 6029 More actions · Answer 12