A Comparative Analysis of SQL Server Reporting Services and Crystal Reports

Posted by Sambeet Patra under .NET Software Development, Software Architecture, Software Development Platforms, e-Business Software Solutions

The objective of this post is to present differences between SQL Server Reporting Services (known as SSRS) and the Crystal Reports suite. Since it is impractical to identify the “better product” without the context of a specific deployment scenario, I will focus on laying out the high level differences and let the readers draw their own conclusions.

(A) Product Maturity

Crystal Reports has 11 major releases and it is currently at the XI version. Since the early conception, the product has been focused on end to end reporting needs and has matured comprehensively over the last few years. There is a general misconception about the common issues with Crystal Reports especially in the .Net community. Many people do not realize that the embedded crystal reporting features in Visual Studio .Net 2003 was a limited edition of Crystal Reports 9 which has been out of date for a number of years now. The newer versions have matured significantly and provide features to seamlessly integrate with .Net applications.

SSRS, on the other hand is only 2 versions old. The first version was released in 2004 as an Add-On to SQL Server 2000. However, there are significant feature expansions in the next release [SSRS 2005]. While this product is relatively new, Microsoft had the advantage of analyzing the existing reporting solutions in the market and implementing the most sought after features in SSRS.

(B) The Product Suite

Crystal Reports suite has 3 main components. The designer interface allows users to design report layout. The server is the hosting environment for the reports and it helps manage / schedule reports. The suite provides extensive library to integrate with the various development platform.

SSRS, in contrast, is a .Net based reporting system. If you do not want to work on .Net platform, SSRS is not the right solution for you. SSRS has a design interface integrated into Visual Studio .Net. The Report Server hosts reports and the report manager is used to manage / schedule reports and the related data sources.

(C) Licensing

Crystal Reports provides various editions for designing reports. There is embedded edition available with Visual Studio .Net at no extra cost. This edition helps developers design reports in the Visual Studio .Net environment. There are stand alone design suites available to design reports without the need of Visual Studio .net environment. This package is sold as a per user license. The crystal server license is sold separately.

SSRS is slightly confusing in terms of licensing costs involved. It is available for free when you buy SQL server license. However in large enterprises you would like to separate out the reporting infrastructure. The reason being, SSRS is extremely resource intensive and tends to take up a lot of RAM / CPU, thereby reducing the bandwidth available to the SQL server itself. This forces organizations to buy additional licenses of SQL Server just so that they can install SSRS in an isolated environment.

(D) Report File Format

Crystal Reports are stored in binary format. The crystal reports object model can be used to programmatically access and manipulate the report components.

The reports created using SSRS are stored as plain XML text. This can be opened in a text editor and changes can be made without opening the Visual Studio .Net report designer. You may tend to assume that this is a convenient option to make changes to the report; But keep in mind that the report XML can get extremely complex and it takes a lot of effort to accurately understand and parse the XML. You are much better off using the Visual Studio .Net designer interface. The XML based format allows vendors to conveniently write third party tools for SSRS. Also, Visual Studio .Net 2005 has an inbuilt feature to design client reports that do not require an SSRS back end to be hosted. This report format is derived from the existing xml format.

(E) Data Binding

Data binding is the process of obtaining data from an underlying source and attach it to an interface. This is an important aspect in report development [in fact, display of data is what reports are all about!!]. Crystal Report allows a single data source per report. If data is to be obtained from multiple database tables, then you have to join the tables to get a single resultset. To display multiple content sets in the reports coming from multiple data sources you will have to design and include multiple sub reports in the main report.

SSRS is flexible in this regard as it allows any number of datasources per report. It is easy to design reports with unrelated sections displaying data from different data sources.

(F) Report Design Features

There are endless debates on differences in the design capabilities between Crystal Reports and SSRS. While it is impossible to provide a complete and detailed list, I will compare some of the common features:

WYSIWYG Capability: Since Crystal Reports has matured over a number of major releases, the designer interface helps articulate the exact look and feel for the intended report. SSRS however has minor issues which will probably be resolved in forthcoming versions.

Report Structure: Crystal Reports takes a Section based approach. There are predefined sections in a specific order [i.e. page header / page footer / group header / footer / body etc] and the designers customize each section to achieve the desired look and feel. SSRS takes a different approach. it is possible to drag / drop and customize report elements onto the report deign interface. This provides better flexibility in defining the structure of the report.

Formula / Calculations: Crystal Reports provides a wide range of predefined formulas that can be readily used in the reports. SSRS provides all the basic functions, but it does not match up to Crystal Reports in terms of the extent of predefined functions provided.

Extensibility: SSRS provides options to write custom code or make calls to external .net assemblies from the report.

Managing Data Flow / Sections: Crystal Report provides better features to manage page breaks / section breaks / spacing paragraphs etc. SSRS does not do an equally impressive job. These features are highly desirable in generating reports for finance / insurance / banking.

Summary

Both tools provide extensive features to build reports in your application. While Crystal Reports takes credit for being in the field for so many years, SSRS provides exciting features in the .Net platform. Before deciding on the product that is right for you, make sure you identify your primary report requirements and then evaluate the products to decide on the most appropriate one.

About the Author

The author is a seasoned Electronic Business Solutions architect at Silicus Technologies (An Offshore Software Development Company) and has lead several large scale software development projects for e-Commerce, CRM and Business Process Management solutions across multiple industries.