Transitioning from Crystal 11 to SQL Reporting

[dianemarie]

Hello, we have been told we will be switching our reporting from Crystal (BO) to SQL Reporting. I am comfortable in Crystal 11 and Access reporting, and have just started working with SQL Reporting. I'm feeling somewhat uncomfortable and wondered if anyone could suggest a good book to get me jump-started. I'm not a whiz in SQL but I think I know it well enough to get started on some reports. Thank you for any suggestions.

 

[xlbo]

SQL tends not to be the issue in RS (although you need to know more than for BO / CR). You may find it a bit harder as you cannot synchronise data sets like you can in BO so you need to be quite careful about how you return data in the 1st place. I have found myslef using stored procedures far more now and learning more about writing SQL rather than getting a gui to generate it for me

Best book I have found is the HitchHikers guide to SQL Server Reporting Services 2000

Don't know if there is a 2005 version but if not, the basics remain the same - some very good tutorials

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dianemarie]

Thanks Geoff. There are so many books out there and a recommendation from someone who knows what they're talking about takes a lot of the guesswork out of finding a book that is going to be helpful. I'm looking forward to some nice relaxing RS reading over the weekend. You know, in between the heavy drinking and carousing.

 

[xlbo]

No worries - If you have any experience designing Access forms, I find RS very similar in concept to that (don't know if that's the least bit helpful). The HitchHiker's guide is very good - you may want to skip the 1st few chapters and come back to them later unless you are installing RS. Chapter 3 is the best starting place for report builders - chapters 1 & 2 are an overview of RS and details on installation / security etc

Enjoy the carousing !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[anationalacrobat]

I'm from a Crystal 11 background. What's killing me is that everything seems so much more complicated than Crystal. I think I've finally gotten old enough to be a tech fuddy-duddy because I find myself saying "In my day..." heh. For example, I haven't messed with ASP much since the early days. I have a skepticism towards integrated development environments because they always seemed to hurt as much as they helped.

What makes my situation particularly bad is I was hired for my Crystal experience, even though they have a half-built (and by no means correctly) reporting services system they want me to take over. We have a good sysadmin here but he's really not dealt much with reporting services so I'm going to have to tell him what he needs to do to support me while at the same time not having admin access to the servers due to our corporate security policies. Also making things fun, very little documentation on the structure of the database I'm reporting on so I'm having to grope along blindly there as well.

In my last job, I was half the IT department and had full admin access to everything. I was able to get a Crystal XI server system up and running with complicated reports, no problem. Right now I feel like I'm trying to do brain surgery while wearing mittens.

 

[DietSquirt]

brain surgery while wearing mittens"
Exactly!
I'm from a Crystal/Access background also and I'm finding SSRS difficult. I miss the ability to select and modify objects in preview, the data saved with the report so it didn't have to rerun it every time you make a change, and the visibility of parms and group formulas in the formula expert. That's after just a week or so of working with SSRS.
Oh, yes, I also miss report bands and the ability to write Basic snippets without having to know all that fully qualified syntax.

 

[bessebo]

I am also from a Crystal Reports background and I have just started a job using SSRS. One thing that I don't like about SSRS is that with the Expression editor you cannot parse it before you actually run the report. So you don't know if you have a syntax error until you try to run the report. The formula editor in Crystal lets you know if there is an issue before you actually save the formula.

Regards,
Bessebo

 

[xlbo]

Can we stop the RS bashing please. It adds nothing to this thread

SSRS has positives and negatives like any other product. Bear in mind that this is a very green product and will mature over time.

Any further posts which do not add anyting to this thread will be red flagged for deletion by site management

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[anationalacrobat]

Xlbo, I can understand your point of view, complaining doesn't make things any better. But this sort of discussion is exactly what's so needed when evaluating a product!

It appears that BO intends to let Crystal wither on the vine. This means that a migration to RS for many companies will be inevitable. But the question is "When do we want to make the jump?" The only way to find out the worth of a product is to tear into things and see what it can do. Companies can run for several years with a product even if it is abandonware, only taking the upgrade plunge when the new solution has sufficiently matured.

As an example, the company I am with is wedded to a third-party customer management system. Management loves the complexity of the system, gathering and massaging data in ways the previous system could not. The built-in reporting features are somewhat lacking, thus the need to look into third party solutions. What makes this so challenging is the CMS allows users to create "groups" of customers using a fairly ingenious query editor that is far more friendly than MS Query or the Crystal query builder. These groups are then used when running canned reports within the system. So from a data-gathering perspective, the CMS really delivers. But displaying the data, ugh! It falls apart, their report builder is horribly outdated. And so users will ask for dynamic reports in RS or Crystal based on the groups as created in the system. The groups information is stored in the database but not in a format that can be easily inserted into a SQL query.

It's a maddening process to try and divine the best combination of reporting needs for our situation. Reports heavily dependent upon using different groups as inputs will have to remain in the built-in reporting engine. Reports that will be for fixed groups can simply have those elements hardcoded into the report or prompted for upon execution. But sometimes we'll need to dump the data automatically into Excel, sometimes Excel can't handle it and something like Crystal or RS needed, and if RS can't yet duplicate a feature Crystal provides, then maybe it's too early to make the jump.

As someone more familiar with Crystal than RS, it's very informative to hear what a Crystal guy's gripes are because they are likely to be the same as mine. There's a wonderful book out there with a name like "Microsoft Servers for the Linux Administrator." As you can imagine from the title, it explains things for the Linux guy in terms he will be familiar with, more useful than a generalist book intended for the student entirely unfamiliar with the subject.

 

[DietSquirt]

I agree with anationalacrobat that information exchange of this sort is very useful in decision making. We are now working on a new Data Warehouse and trying to decide if SSRS is the tool to use for reports. I like it a lot but there are a few things that make me think maybe we should wait for a later version.
But Geoff is correct, this isn't the right thread. Maybe we should start one that accumulates the difficult spots we've hit with SSRS, and also the neat things it does that are an improvement over Crystal.

 

[anationalacrobat]

Well, the thread title seems to cover it nicely, "Transitioning from Crystal 11 to SQL Reporting." It seems like a good forum to put it in. I can see the Crystal forum guys getting understandably miffed if a topic like this was there!

 

[Peager]

We are a corporate shop that has worked with Crystal since version 7 all the way up through XI. We moved to MS SQL 2005 about a year and a half ago so I took about 11 'butt weeks' of classes on it and related technologies. Of the 12 of us in the reporting services class, 10 of us were there to 'get away from Crystal'. It's a shame that Business Objects has let Crystal wither on the vine but it's not surprising for a company that considers any firm not on the Fortune 500 list to be a 'small'company.

Once I started digging into MSRS I have been pleasantly surprised. There are a number of things that I prefer to Crystal. The ability to have multiple data regions is my favorite. The rendering of the reports in different formats for output is hands down superior to Crystal. You can actually distribute an Excel spreadsheet from MSRS and not be ashamed of it. It handles line work better from a design viewpoint. I don't have to blow everything up to 400% to get the line work exactly right like I do with Crystal.

The problems I am struggling with I expect will go away as the product matures. I can't seem to get text to rotate to the left. I haven't found a way to handle odd/even page formats in the body of a report. I can't lock an element to a point on the page when there are 'can grow' sections above it.

The best 'getting started' book I've found is an Osborne book, "Microsoft SQL Serve 2005 Reporting Services" by Brian Larson, ISBN 0-07-226239-7. It's really set up as a tutorial but covers everything in pretty good depth.

Hope you find this helpful.

 

[bessebo]

Hi,
The last time I responded I got shot down by xlbo for being critical of RS by making a comparison to Crystal. Isn't this the place to be comparing the two reporting packages? Also, by making a comparison someone else may have a different way to do what I am trying to do and could possibly enlighten me and give me a solution. I have been using SSRS now for about a month and I do like the ability to have multiple data sets within a report and the interactivity with all of the Microsoft languages is very cool. I exclusively use Visual Sudio to develop reports and will be learning C# in the future and will already have had exposure to the development environment. I love how everything integrates. I do wish you could render an SSRS report in Word format but I don't want to be toooooo critical.
We also realized that the charting capabilities was limited so we purchased licenses for Dundas charting. I have not used it yet but co-workers who have already been utilizing the Dundas charting really think it is worthwhile. I am a big fan of SSRS as it is now but I'm sure it will become much more mature in the ensuing years.
As a suggestion for a course, I actually took a course at New Horizons Learning Center in Nashua, NH and they actually used the video course from APPDEV. It is definitely worth checking this out. I learned a lot. Good luck.

Bessebo

 

[DietSquirt]

I used the AppDev course here at work and got a lot out of it. How much does a Dundas license cost? I would appreciate a list of the charting shortcomings as that's another thing I'm struggling with. As you say, sometimes it's difficult to know if you're doing something wrong or if the tool just doesn't do it.

 

[dianemarie]

What's this AppDev course you speak of? I've used the Microsoft Virtual Labs, which I found helpful coming from the perspective of being brandy new. Then I took the lab printouts and applied them to my real data. It was a good springboard. Also, could one of you explain why you would use multiple datasets for a report? Not quite sure I'm getting how that would be used. I'm a graphical Crystal developer, used to thinking of simply adding tables or views if I need more information on a report.

 

[DietSquirt]

http://www.AppDev.com

1/800/578/2062 Our department head came up with these, and I like the SSRS one.

They come with a book and a DVD, (they have entirely different material and don't follow each other.) I found the course very helpful, with the exception that the books you buy NEVER tell you what it can't do. Those things are few, but sometimes you beat your brains out trying to make the darned thing work before you realize it doesn't work.

I used the microsoft SSRS tutorials too and they were good. Is that what you mean by Virtual Labs?

Multiple data sets can be used like this:
A dataset may be a select distinct query that provides a parameter list for the user to select from. You can have one of those for each parm in your report.

The main dataset would return the records for your report.
Actually I guess you can have several datasets and put more than one dataset report on the report canvas. (So two reports on one report? I haven't got the terminology down yet.

Or you might have a separate dataset for a graph that goes with the report.

 

[xlbo]

To the previous posters - especially bessebo who seems to think I was targetting them:

The 3 posts prior to my post (not just yours bessebo)were not discussions on the differences between SSRS and Crystal - they were spleen vents. That is why I interjected. I have no issues with discussing the differences between the 2 products but I do have an issue (as do site management) with posts that simply slag off a product

Constructive comparisons are fine. Venting your spleen because you are frustrated is not



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bessebo]

Hi,
I want to also reiterate what DietSquirt said about using multiple datasets. I particularly use them for querying for a parameter list. This has been very useful for me.

Regards,
Bessebo

 

[dianemarie]

What an interesting concept (using a dataset for a list of parameters). I'll have to think about this, get my brain around it. Most of our reports use a lot of parameters. The virtual labs are out on TechNet.

From all the previous posts, it appears it is a fairly big learning curve going from Crystal to SSRS, which is fine. IT people are used to being on a continual learning curve of one kind or another. Our company has one full-time developer, and I'm her part-time backup (I wear several hats). Management would like all 600+ Crystal reports converted by the end of the year. What do you think? Doable?

 

[anationalacrobat]

Management would like all 600+ Crystal reports converted by the end of the year. What do you think? Doable?

Contemplate the cherry blossom and compose your death poem now.

Actually, it should come down to how well you know the data structure you're reporting on, how versed you are in Crystal, and how quickly you can pick up the nuances of RS. So this could be realistic or ridiculous, no way to tell.