Compare SQL Server Reporting Services to Crystal Reports
Which of Crystal Reports and SSRS (SQL Server Reporting Services) is better to use?
Answer
On the one-hand, Crystal Reports is a steaming pile of expensive and overhyped donkey poo, and on the other hand SSRS actually fulfils all the promises that CR marketing makes - and it's free.
My contempt for CR stems from many years of being obliged to use the horrible thing. There's really no point in detailing the utter odiousness of CR when I can give you references like Clubbing the Crystal Dodo or Crystal Reports Sucks Donkey Dork (not as funny but rather more literate and substantiated with technical details).
Free?! Yup. You don't even have to buy MS SQL Server to get it - you can install SQL Express with Advanced Services. This is available as a download that includes SQL Server Reporting Services. While SQL Express is limited in the number of concurrent users it can support, the following observations are salient:
The licence for SSRS obtained as part of SQL Express only requires that it be deployed as part of SQL Express. There is nothing forbidding connection to other data sources or requiring that a report obtain data from SQL Server.
The abovementioned version of SSRS has no intrinsic restrictions on user connections. All limitations are imposed on the SQL Express database engine.
SSRS uses ADO.NET, which includes, out of the box, drivers for Oracle, Jet (Access), OLEDB and ODBC
Thus you can connect the free version of SSRS to any back-end to which you can connect ADO.NET, which includes (for example) MySQL. I am told by Rory in a comment below that this is "not supported". That's true but I can't find anything in the licence that forbids it and while the drivers are not supplied by SSExpress they certainly are supplied by most versions of Visual Studio and you can ship them in your setup kit. This may not be an expressly supported configuration but so what? Even if you did have a full MSSQL licence it would be asking a bit much to expect Microsoft to help you talk to some third party database (not to mention a bit weird).
I use SSRS extensively at work both for inward facing reports and for outward facing reports embedded in ASP.NET applications that provide bureau services to large numbers of paying customers. In our case it happens that the backing store is a licensed copy of Microsoft SQL Server 2008, but this is incidental to the technical merits of our reporting solution.
There is a long list of capabilities that Crystal Reports claims to support but which either don't work or which require a staggeringly expensive licence if you want more than five users. You can't even trust CR to do SQL correctly. SELECT COUNT(*) FROM SOMETABLE WHERE 1=0 should produce a result of zero but it it produces one. The built-in query engine is defective, and a team that screws up something a bunch of amateurs can do for free (eg MySQL) has no hope of getting anything you'd describe as performance out of their code.
And they don't. The evil thing leaks memory like a bucket with no bottom, and if you use SQL profiling tools you will find it is spectacularly inefficient.
As for the alleged support, I can personally attest that dialog resize bugs have gone uncorrected for decades after they were first publicly documented. If you get out your credit card and pay the extortionate ransoms demanded (I too would want handsome pay to support such a horror) you will find yourself talking to someone who claims his name is David, but inexplicably pronounces it "Dah-feet", and who doesn't even understand your question, much less have an answer.
The SSRS support situation is fairly similar, but it actually works so you don't really need much.
SSRS, on the other hand, does everything that CR claims to. It is not without bugs, but they are delightfully few, and they seldom survive more than one release cycle.
The SSRS designer UI is hosted within the Visual Studio IDE. It is attractively presented in typical Microsoft style, but more than this it is quite well thought out, incorporating several simple but fundamental departures from traditional report designers. For example, to present tabular data you define a table rather than fiddling about with individual text boxes. As a result you don't have to screw around trying to line them up, and putting borders on them is a trivial stylesheet exercise.
SSRS actually does all the things CR claims to, it's inexpensive, there is extensive reliable technical documentation, it's designed to be extended (also documented) and you can connect it to anything for which you can get an ODBC driver. This is a no brainer.
Some shortcomings of SSRS
- It is not obvious how to bind fields in page headers and footers.
- It is not possible (so far as I know) to position relative to the bottom of a page. This is a genuine problem for certain types of report, and one for which I can think of no workaround.
- There's no support for expando horizontal rollups in cross-tabulations.
- There's no direct support for report headers and footers. Use Rectangle objects at top and bottom of the report layout, with pagebreaking properties set appropriately. Or use subreports. The people who complain about this obviously haven't tried very hard.
- Lack of support for overlapping group intervals (the CR grouping system can do this) UPDATE SSRS 2008 R2 now supports this. It's buried in the grouping edit dialog. Look up "group variables" and read this.
It actually looks like overlapping groups can be done with SSRS2005 too, although I never knew that. I wonder did anyone ever crack the bottom-relative positioning issue?