Practical ASP.NET

Crystal Reports vs. SQL Server Reporting Services

If you're trying to decide whether to use Crystal Reports or SQL Server Reporting Services with ASP.NET, you can rest assured that there is no bad choice.

I sometimes get asked to compare Crystal Reports with SQL Server Reporting Services. I tell people it's like choosing between Visual Basic and C#: It's a lifestyle choice. From my point of view, with the latest versions of the two tools, they are both equally capable. While the two tools do things in different ways, I don't know of anything that I can do in one that I can't do in another. Things that are easy in one tool are marginally more difficult in the other but they're really not all that different.

Since this is an ASP.NET column rather than a reporting column, I'll demonstrate that claim with examples of the typical things you would do when integrating reports into an ASP.NET page.

For instance: To display a Crystal Report, you add a CrystalReportSource and a CrystalReportViewer to the page. If you're using a client-side Crystal Report (represented by an .rpc file) you bind the CrystalReportSource to the report and the CrystalReportViewer to the CrystalReportSource.

With SQL Server Reporting Services (SSRS) you just drag a ReportViewer onto your page and bind it to the .rdlc file that represents an SSRS report. There is an equivalent to the CrystalReportSource in SSRS but it's an ordinary ObjectDataSource and it's generated automatically when you add the ReportViewer to the page.

There are several different ways that you can provide data for a report -- for these examples I'll assume that I've loaded data into a dataset (a great strategy, by the way, because it provides an easy way to cache data and reduce trips to the database server). With SSRS if you referenced the DataSet when you built the report, no additional code is required to get the data out of the DataSet and into the report.

With Crystal Reports getting the data out of the DataSet and into the report requires a little more work. You must pass the DataSet to the SetDataSource method of the ReportDocument available from the CrystalReportSource control. That's only a single line of code and it looks like this:

Me.CrystalReportSource1.ReportDocument.SetDataSource(ds)

One more example: If you're dropping data into a DataSet and caching it, then you're probably retrieving more data than you need for any particular report. So, for any report you'll be filtering your data as part of passing it to the report. In Crystal Reports that's easy: you don't have to do anything at design time and, at run time, you just have to set the CrystalReportViewer's SelectionFormula property to something that looks very much like a SQL Where clause. This example, for instance, limits the data to those rows where the Region field of the CustomerOrders table is set to BC:

Me.CrystalReportViewer1.SelectionFormula = 
                           "{CustomerOrders.Region} = 'BC'"

With SSRS to filter data, you have to set up a parameter at design time in your report. To use the parameter you have to create an array of ReportParameter objects and pass the array to the SetParameter method of the ReportViewer. Again, it's not a lot of code: These three lines of code limit the report to rows where the Region field is set to BC for an SSRS report:

Dim rps(0) As Microsoft.Reporting.WebForms.ReportParameter
rps(0) = New Microsoft.Reporting.WebForms. _
                           ReportParameter("Region", "BC") 
Me.ReportViewer1.LocalReport.SetParameters(rps)

Throughout these two tools you'll see the same kind of differences. While both tools support the same range of features, a "no code" solution in one tool requires code in the other; some features require no design-time effort in one tool but require design-time set up in the other. It really is a lifestyle choice. Or, to put it another way, "What you gain on the ferris wheel, you lose on the merry-go-round."

About the Author

Peter Vogel is a principal in PH&V Information Services, specializing in Web development with expertise in SOA, client-side development, and user interface design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His most recent book ("rtfm*") is on writing effective user manuals, and his blog on language and technical writing can be found at rtfmphvis.blogspot.com.

comments powered by Disqus

Reader Comments:

Tue, Dec 28, 2010 Peter Vogel Canada

While I'm still skeptical about whether "one product does x and the other product can't" (though I firmly believe that some things are easier in one product than the other), the comments on this article have brought out everything that a prospective buyer would need to consider. That's a real service.

Thu, Dec 23, 2010 Mark Aurit Sandpoint, ID

I bought Brian's book (see his 12/14 comment) and its excellent, saved me a lot of time. I keep think of switching to SSRS, as Im concerned that someday in the future MS might decide not to bundle CR.

Thu, Dec 16, 2010 Keith USA

There is something additional that this article overlooks. I do prefer SSRS over Crystal, but in order to use it you must have a MSSQL server in your environment. Unfortunately for many of us, we work in places where SQL Server does not exist. Only Oracle or Sybase or DB/2. That is where having the Visual Studio edition of Crystal really helps out in a pinch.

Wed, Dec 15, 2010

Crystal is free with Visual Studio...

Wed, Dec 15, 2010

I agree, it depends on the requirements which tool I would use.

Wed, Dec 15, 2010 Hank Allentown PA

#1 Crystal is very expensive and SQL Reporting is included with SQL. #2 Crystal is very slow compared to SSRS. The exact same report with the exact same dataset runs 5-10 times faster in SSRS than in Crystal. #3 When SQL and VS upgrades, you need to buy the CRS upgrade. I personally experienced this. We dumped CRS and went with SSRS when we found out that the CRS upgrade would cost $16K to go from VS2003 to VS2008 because of the changes in .NET 3.5.

Wed, Dec 15, 2010 MARC

@PJ - Crystal (at least through VS2008) is free for deploying on internal-facing web server applications. So, that limits the strength of the cost argument

Wed, Dec 15, 2010 Peter Vogel Canada

I have to admit that I've never (never!) found anything that I can do with SSRS that I couldn't do with CR and vice versa (but, who knows, I may just not have wanted to do the "right" things). This is also an ASP.NET column so my focus is very much on getting data on the screen and only secondarily on getting data on the page...but I suspect there isn't much (if any) difference there, either. If someone wants to provide a CR report that they are confident can't have an equivalent produced in SSRS, I'd be interested in testing to see if that's true (but, obviously, I don't think that report exists). I will add one caveat: I do say "equivalent"--there may be some thing on the report that can't be reproduced exactly in SSRS but something equivalent could be done (and I'd be willing to bet that the reverse is true: there might be some thing you can do in SSRS that can't be reproduced exactly the same way in CR but there will be an equivalent).

Wed, Dec 15, 2010 PJ

There's one big advantage to SSRS that makes it the winner in many businesses - it's free with SQL Server! In the current economic climate of cutbacks cost is a major factor and Crystal is not a cheap reporting solution for a business.

Tue, Dec 14, 2010 Igor Rozenberg Melbourne, Australia

Peter, Please note that SSRS Dataset, unlike DataSet used by crystal is NOT an ADO.NET DataSet. As a result CR could deliver reports based on data stored in application memory, while SSRS would hit database twice - once to retrieve report metadata, and second - to retrieve report data. And this is BY DESIGN.

Tue, Dec 14, 2010 Brian Bischof

If you need code samples and tutorials on using Crystal Reports .NET, you can check out my book on Amazon.com. http://www.amazon.com/Crystal-Reports-Encyclopedia-NET-2005/dp/097495361X/ref=sr_1_1?ie=UTF8&qid=1292379046&sr=8-1 If you need a more in-depth review of CR vs SSRS I wrote a white paper about it here: http://crystalreportsbook.com/SSRSandCR_ExecSummary.asp Unfortunately, I wrote this a few years ago and SSRS has improved their product since then. I need to go back and update it, but no time right now. Sorry. Brian

Tue, Dec 14, 2010 syntap

Coming from a long Delphi background where the reporting tools changed almost with each major release and caused major upgrade headaches, it was nice when moving to VS to have Crystal Reports bundled with it. Later it was real discouraging to see CR separate from the pack with VS2010. For that reason I'll consider using SQL reporting services for new projects. That said I have found Crystal to be quite powerful once you have muddled your way through its annoyances and have established a code and designing template or methodology that is reproducible and works. The big example that comes to mind for me is pushing different datasets to a single report template at runtime (i.e. point to this database for this run of the report, that database for that run of the report, all using the same asp.net page and .rpt). It was a bear to figure out but once I did I went on to really start appreciating the in-report customization I can embed in the report template, like passing parameters in the dataset to get rows to switch colors, formula-based totaling, stuff like that. I haven't tried the final production CR for VS2010 yet and don't really want to leave CR, but I would like some confidence that there will be a CR for VS2012 or whatever the next version will be. I have that confidence with SQL Server reporting services.

Tue, Dec 14, 2010 KevinR

Personally I have been able to do just as much with SSRS as I have been able to do with Crystal and it is much faster. Complex groupingand data based formatting can all be done with SSRS. Consuming various data sources including SOAP web services also works (where Crystal will not consume a web service returning a .Net based dataset). I have even found that exporting to various file formats renders better than Crystal.

Tue, Dec 14, 2010 Minh in Sac

I agree with Marc on his critique of this article. I was hoping that the author talks more about capibility of each product. I found that Crystal Report, while not a perfect product, is a good choice for writing complex reports. By complex,I mean complex formatting. I didn't see that with SSRS. If I did, I'd would have jumped on it already. I was hoping that the author disprove my view of SSRS.

Tue, Dec 14, 2010

Although it may be true that neither option is a “bad choice”, as a developer who uses both I disagree in the “lifestyle choice” analogy. There are significant pros and cons to both, so before deciding on one or the other you need to define what it is you’re using the tool for. I think the biggest difference between the two technologies is reporting. Wait, did he just say reporting? Yes I did. Because, who would have thought that was an important factor in a reporting tool (considering Microsoft didn’t even allow printing in its first release).
The physical layout and printing of reports is implemented completely different between the two and this can have serious ramifications. When it comes to printing and formatting I feel Crystal is a far superior reporting tool. If you are making ad hoc or simple reports that are being displayed on a web server with no sort of fancy pagination rules then Reporting Services is a great, simple and free solution (with SQL Server). You don’t need all the overhead that comes with Reporting Services. If you need to make good looking reports that are actually being printed and given to consumers that have rules about page breaks and layout Reporting Services won't work for you.

Tue, Dec 14, 2010 Marc

This article is worthless. It doesn't really say anything the most casual ASP.NET user doesn't already know. As for the overall premise of the article, there is no wrong choice is only true if your needs are relatively simple. Doing more complex reporting is much more difficult in SSRS. Some examples: -Page N or M by group -Adding a blank page if last page of a group is on an odd page -Field data in page header/footer These are features that SSRS needs to add to be a reporting tool that can be used as a Crystal Reports replacement.

Add Your Comments Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.