Compare SQL Server Reporting Services to Crystal Reports

Question

Which of Crystal Reports and SSRS (SQL Server Reporting Services) is better to use?

Solution 1

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?

Solution 2

I've been using Crystal report till version 10 and was always doing stuff i wanted successfully along with ASP.NET applications. Its output on web is really good like WYSIWYG and exports to Excel and PDF are also accurate. Printing is also marvellously correct.

Recently, i've been working on SSRS 2005 for around an year and have been living to witness so many lackings that must have been provided out-of-the-box too. SSRS web output varies greatly with different browsers and diff resolutions and would easily make a developer sick. Moreover, the scrolling issues with report viewer would make an end-user mad quite early as it is based on HTML using an IFRAME. (Note: Crystal 13 uses an IFRAME in the web-viewer which suffers from sporadic text-wrapping and overlapping issues). The exports are not good at all. You cannot align images left or center in cells and cannot specify background colors for images. You cannot center align complete report body. For possibility, i've played with the rendered HTML for hours and figured out exact replacements to make that works, but these simple fixes were not known to SSRS developers i guess because probably, they never used SSRS for themselves.

Further, in web applications, you need to bear the bad UI for parameters out-of-the-box. I have simply removed it completely and the cost of creating it in ASPX pages made me think to design tabular reports in DataGrids instead using ObjectDataSource and database pagination technique. You cannot layout the parameters to your needs. Bugs in paramters sections postsback complete reports without any changes. Paging with grouping works with a trick, but than sorting fails on complete dataset. For every bit of medium to advanced level of UI requirement, SSRS costs so much of time figuring out that it is simply not possible. As there are less of SSRS users, online community has no good solutions for simple problems. Not to forget the good side of SSRS is its deployment, notifications built-in, caching and configuration side, but no UI to win.

BOTTOMLINE is that i've seen SSRS frustating you just due to the nonresponsiveness of Microsoft Support team when they have to say 'sorry! not now' after a month. SSRS 2008 also doesn't have many of these issues fixed rightaway. Moreover, moving to SSRS' 08 means a complete migration of back-end platforms as well. Keeping the equation in mind that the more you use a software, the more it gets mature over time, Crystal is anyways a much better choice because, SSRS soon accumulates costs for fixing their bugs by yourselve.

Solution 3

You can deploy an app using Reporting Services by including 3 DLL files. That's a huge benefit. (Note--you have to get one of the 3 DLL files from the GAC.)

With Crystal Reports, you have to install the runtime on each machine that will run the application (either a website or client app).

Reporting Services has all of the features most people need, and the deployment is MUCH easier. I will never user Crystal Reports unless I have to.

Solution 4

Since this thread has popped back open, I'll add my two cents. I had to use Crystal for about three years during the version 7 and 8 days. I hated every minute of it. I've seen a little bit of the newer versions and still don't like it.

I dislike it so much that it pains me to say this: from my experience Crystal's better suited than SSRS for complex reports. A coworker and I tried desperately to get a moderately complex report layout to work in SSRS and gave up. My impression of the product -- just my opinion, mind you -- is that it's not quite ready for prime time.

Crystal will make you hate your life and look for another job, but there's a reason it's so pervasive: it works.

Solution 5

Reporting Services is much better in my experience. It is a better environment, but best of all the connections (data sources) are separate from the report and can be shared. This makes for much simpler deployment between environments.

View more solutions

Comments

[AR.]

So, Peter...you're actually not sure about your position then?! ;-) Actually, I agree wholeheartedly!

[Pulsehead]

lol @ Clubbing the Crystal Dodo.

[AviD]

Agree - but note that your sample SQL - SELECT COUNT() FROM SOMETABLE WHERE 1=0 - is not a good example, as this is SHOULD return a single row, with a single field - the value of COUNT(), which should be exactly 0 - this must always be returned.

[Peter Wone]

"SELECT COUNT(*) FROM SOMETABLE WHERE 1=0" should return one row, with one column, the vaue therein being 0. The value it returns in that cell is 1, which is incorrect. This is a bug, and it is profound.

[adolf garlic]

They will never charge for RS per se, but they have always charged for SQL Server and will continue to do so. This is just another compelling reason to use SQL Server.

[adolf garlic]

crystal AND vba? jeez, i thought my job sucked

[dotjoe]

just another way to get locked into sql server!

[cjk]

While designing your reports you can default your parameters to useful values

[dotjoe]

But, how to run the report while designing?

[carveone]

You know what! There are even guys making a living out of Excel shits, I mean sheets!

[adolf garlic]

Dotjoe: Well considering there are only 2 choices of databases out there (Oracle and SQL Server) Which solution would you prefer to get locked in to? If money is no object, then I'd go with Oracle, if not then SQL Server.

[adolf garlic]

Given that .DATA files are created whenever you run the report in VS.net, I'd have thought there is a way of previewing the report based on these (without the need for a db). [.DATA files are caches of the data based on params selected]

[dotjoe]

@adolf - I just want to get locked into the solution that makes it easiest for me to design a report. :) If it was my decision, I'd be using postgresql and some open source reporting tool. I admit to not playing with the reporting services much longer than a couple days. Is their an addon to design the report in ssms? I really dislike report design in VS.

[Epaga]

+1 I agree (seeing as how I'm a Crystal-Clear developer, hehe...)

[Peter Wone]

+1 for presenting the only credible counterargument I have seen so far. I should point out that "I don't know how to make it do X" is not the same as "It can't do X". Personally I think the Microsoft support is better than the CR support.

[queen3]

This comment is a bit misinformative or outdated. SSRS, at least 2008, can export to DOC, and can have formatting inside text boxes (it can even do HTML there).

[queen3]

+1 for clarifying the most important reason which is not technical advantage but the pain of working with Crystal Reports.

[queen3]

Hm, I personally see CR designer as good and its sections and groups concept as pretty powerful. I could do incredible things with CR. It's more low-level but this (like in Photoshop) just allows to do more things. Of course, it also makes it more pain to do ;-)

[Adrian Grigore]

Unfortunately a LOT more pain in my experience...

[Behrooz]

"without multiple overlapping textboxes",in my language(Persian, RTL) i should use one textbox per word! WTF?

[irag10]

fwiw - When using SSRS with SQL Express it is NOT allowed/supported to connect to datasources other than SQL Server instances on the same machine (msdn.microsoft.com/en-us/library/cc281020.aspx and msdn.microsoft.com/en-us/library/ms365166%28SQL.90%29.aspx), so if you're planning to use SQL Express to get around licensing it'd be best to check closely.

[g t]

tl;dr (needs formatting)

[StuartLC]

Re : Binding fields in header & footer - there is a hacky workaround using hidden textboxes - see msdn.microsoft.com/en-us/library/bb395166(SQL.90).aspx

[armonge]

What do you mean with "a bunch of amateurs" ???

[Cheung]

The documentation of report service is better then CR

[Arushi Rajput]

I tend to agree with most of your sentiments here and history here. If you get used to using Crystal Reports it's not all that bad, but does have it's flaws. Crystal can be tedious, but it does serve its purpose well. SSRS is another option, but I don't find it vastly superior. SSRS is probably better from a programming standpoint, but not necessarily to solve the business's data needs. I think it's a much better solution for business users to use to write there own reports than SSRS (using predefined data views).

[Peter Wone]

By "a bunch of amateurs" I meant a group of people acting without remuneration. By definition, a person who performs a service without the expectation of payment is an amateur. Olympic athletes are amateurs.

[Peter Wone]

There's no kind way to say this, so I'll just be blunt. People don't create tools because you need them, they do it to make money. RTL languages haven't been commercially important for three hundred years.

[Peter Wone]

I never thought about that. But in my experience shops that use CR typically lack amenities like staged release management and the associated tooling.

[Peter Wone]

SSRS 1.0 was retired in 2008 and I seriously doubt CR would fare well in a direct comparison with the current release of SSRS, so I expect they haven't published one. But if they have, the URL would be of interest.

[queen3]

Right, and the fact that you put the equal sign between such shops and CR tells even more ;-)

[Royi Namir]

"With Crystal Reports, you have to install the runtime on each machine that will run the application (either a website or client app)." - do you mean that if im a web user and want to see CR so i have to install some softs ???

[Jason Rae]

This is one of the best responses I have read in awhile. My company just switched to SSRS for all new customer reports, and it is so much easier. It makes Crystal Reports look positively stupid by comparison. We spend a lot of time creating customized reports for customers and we have found we can create, edit, and deploy reports much faster.

[Vincent Vancalbergh]

You keep saying "killer features". I don't think that word means what you think it means.

[surfen]

This answer refers to client-side reporting, I guess

[Peter Wone]

I find this an astonishing statement, but your response as a whole is literate and thoughtful so rather than laugh it off I'll ask the question. What sort of report would be beyond the capabilities of SSRS that wouldn't also be far beyond the famously limited technical ability of "top level managers , directors & CEO"?

[Deb]

Several reports i can tell but that will be beyond the scope of this discussion. Ever tried using arrays ? Try comparing built in functions of both. It is not that i cant design those reports in SSRS. But with CR i have the option of shifting many types of processing into the CR itself without putting extra burden on DB. and coding.

[Deb]

The clients often want to shift some sections from one place to another which can be done easily with CR. Also Page no., group item value, firstpage,lastpage wise customisations to sections are very easy through built in variables. Every sections are fully databound which was not the case with SSRS untill SSRS2008. The designer is much better than SSRS .The MS guys should atleast stay ahead then BO in their own field which is userfriendly GUI. The gap however is rapidly closing.But some of the new features of SSRS was available with CR since Day 1.This however is my personal opinion.

[Nathan Griffiths]

I would just like to address a couple of your points there - SSRS runs as it's own process (ReportingServicesService.exe) and manages it's own memory - it's not dependent on SQL Server memory, except to the extent that it uses ReportServerTempDB for some temporary processing.

[Nathan Griffiths]

Also the perception that SSRS is "free" is a common misconception. It is a component included with the SQL Server product, which is not free (except Express editions). The cost of all SQL Server components (SSRS, SSAS, Database Engine etc.) are factored into the price of a SQL Server license. That said, if most or all the components are utilised, it certainly makes a compelling value proposition.

[Saber]

Have you tried SSRS 2012 to give us an up to date review?

[Lynn Crumbling]

/me ponders whether Talha may be part of the CR support team.

[Lynn Crumbling]

+1: Excellent point. It really forces the committer to write PERFECT logs about exactly what they changed; I think most SC systems will treat them as binary, which means no merging. I would be really nice if the next rev of CR supported a text-based format.

[Chrysalis]

Great response. Not only did a learn a good bit about how SSRS compares to CR, I also realized the actual definition of amateur, which is not exactly what I used to think it was :)

[David Tansey]

@Arvand I believe that if one looks closely at SSRS 2012 they will find it to be almost completely unchanged from SSRS 2008. This fact has not been a positive discovery for a project I have been working on.

[Steam]

@PeterWone - Although SSRS is not amazing either, I wonder if the team behind crystal reports was on crystal meth. Perhaps, that is where the inspiration comes from.

[Steam]

Also, I recently saw SAP Data Services and compared it to SSIS. SAP DS is an unintuitive piece of junk. Perhaps SAP itself is the dodo and CR, DS etc. are its slothful offspring.

[Steam]

Not sure if the online community for SSRS is small.