Both SQL Server Reporting Services (SSRS) and Crystal Reports (CR) are business intelligence applications that take data from data sources of varied formats and generate reports providing decision support information. Neither Crystal nor SSRS is a full object-oriented report writer and both are object-based with a rich object model, and feature a certain amount of cascading. Crystal Reports has had twelve versions in the past twenty years. The past two versions of Crystal are owned by BusinessObjects. On the other hand, the first version of SSRS came out in 2004 and they are currently in the third version. SSRS has 75 percent of Crystal Reports functionality. However, beyond these similarities, these two products vary when it comes to functional and reporting capabilities and the biggest differences between the two are in the ease of use and performance.
Creating New Reports
A new report can be created in crystal designer in visual studio 2005 which contains most of the functionalities. Crystal provides three options to create a new report.
- Create using report wizard.
- Create a blank report.
- Create using an existing report.
A new report created will contain a report header and footer, a page header and footer and a detail section. You can add sections and groups based on report requirements. Crystal has a previewer and in most cases the report elements can be modified in the preview mode.
To create SSRS reports, you must first create a Business Intelligence project in Visual Studio 2005, and select Report Server Project from the list of installed templates. After you create the new project, you can set target URL deployment and other options in the project properties dialog box. A Report Server project contains shared data sources and report files. SSRS provides two options: You can use the Add New Report wizard (again, to define a data source and general layout), or you can start with a blank report. A blank SSRS report does not contain as many default sections as a Crystal report, but you can easily add sections (such as headers, a table layout, etc.). The design environment for SSRS contains three tabs: one for data definition, one for layout, and one for preview.
Note that unlike Crystal, SSRS preview mode does not allow you to modify any formatting on the fly—you need to go back to the Design tab, make the change, and return to the Preview tab. Finally, an SSRS report uses an extension of RDL (Report Description Language) and the file itself is an XML file.
Data Sources
Crystal Reports stores the data source properties within each report. Each data source can also have its own connection information. This gives you great flexibility for using different servers to generate data from. However, when you want to deploy your reports from a development server to a production server, you will have to remap the reports to the new server, unless you are using Crystal Reports Server. With Crystal Reports Server, you can use Business Views to share data sources. Multiple reports can be based on a single business view. The repository allows storing Command Objects to be shared among reports.
Crystal Reports also uses a “single point of entry” architecture. It can connect to a myriad of data types (SQL Server, Oracle, My SQL, etc.) and join them together to form a single resultset. If you want to report on an independent data source you have to incorporate sub-reports to do so.
SSRS is designed so that reports can share a common data source. Switching from a development server to a production server is fairly straightforward and all reports will immediately reference the new server.
This can be done by either modifying the Data Source properties within the report project or using the Reporting Manager to set the Data Source location. By using a shared data source that is managed by a single tool, the deployment process is greatly simplified.
The downside is that the individual reports don’t have the flexibility to have the data source modified during runtime. For example, if a report defaults to using a certain server and database and occasionally needs to switch to a different server for reporting on historical data, this can’t be done. By sharing a common data connection, you lose the granularity of being able to control individual reports.
SSRS can have multiple unrelated data sources for a single report, but cannot link or join them to a single resultset. Each object on the report gets assigned its own data source and reports on it independently of the other objects. Thus, a report can have multiple points of entry using multiple resultsets. This means it is easier to conceptualize the data that a report is printing because the resultsets are more compartmentalized and this also virtually eliminates the need for sub-reports.
Sections and Groups
An empty Crystal report contains a report header and footer, a page header and footer, and a report detail band. You can insert a group by right-clicking in the main report body, and then selecting insert group from the shortcut menu. You can also customize section behavior by right-clicking any section divider and selecting Section Expert, which loads the Crystal Section Expert. The hide option allows you to implement drill down capabilities.Crystal allows you to define whether the group is based on an existing column or a formula, the sort order, and whether the entire group should stay on one page.
By default, an empty SSRS report starts with just that—an empty report. However, by using either the table or matrix report controls, and by inserting a page header/page footer , you can easily build a basic report. After you add either a table or matrix to the report body, you can define a group (which will insert a group header and group footer) by right-clicking in the row selector area and selecting Insert Group . SSRS will then display the Group Options. Note that SSRS does not provide the functionality to keep an entire group on a subsequent page. For any grouping specified in the wrong order, you’ll need to modify each group number manually in SSRS if you need to change the physical group sequences.
Page Control Options
Crystal generally provides better support for page control through the ‘Keep Together’ options for both individual report sections, as well as entire report groups. Adversely, while SSRS allows you to define a page break at the end of a group, it will not conditionally allow you to essentially implement “widow-orphan” protection to ensure that a group or section will fit on a page. SSRS does implement a “Keep Together” property for the rectangle control, as well as a certain level of page control through the use of subreports.
Subreports, Charts and Drill down capabilities
A subreport is a report within a report. Typically you build a subreport when you need to display multiple levels of detail data, and/or when you need to display data from multiple data sources. In most instances you’ll need to establish a link between the subreport and the parent report.
At face value, you might think that subreports in SSRS are very similar to Crystal. However, there are some differences. You can “nest” subreports in SSRS, which is something that you cannot do in Crystal. In SSRS, you cannot insert a subreport into the Page Header or Page Footer area. This makes it difficult to build a common header/footer template. In some instances you’ll need subreports in SSRS for situations where subreports would not be necessary in Crystal. You’ll see this most commonly when a stored procedure or web service returns multiple tables in the result set.
Charting capabilities
Crystal allows you to add the following chart types: Bar (horizontal or vertical), Line, Area, Pie, Doughnut, 3D Riser, 3D Surface, XY Scatter, Radar, Bubble, Stock, Numeric Axis, Gauge, Gantt, and Funnel.
SSRS supports the following chart types: Column (vertical bar chart), Bar (horizontal bar chart), Area, Line, Pie, Doughnut, Scatter, Bubble, and Stock.
Despite the greater number of chart types in the Crystal list, both tools are very similar in terms of overall charting functionality. Both tools contain chart options dialog screens that are quite similar to charting configuration options in Microsoft Excel and other popular charting tools.
Drill down capabilities
Drill down capabilities help see data at hierarchy levels. Crystal supports drill-down capabilities through the “Hide” property in the Section Expert. You can turn this option ON for each child level in the hierarchy where you want to implement a drill-down feature.
SSRS supports drill down through the use of two properties: Hidden and ToggleItem. You set the hidden property to “TRUE” for each child level in the hierarchy, and you set the ToggleItem property to each child’s parent level.
Exporting and Integrating Reports into Applications Programmatically
Crystal exports to PDF, Excel, Word, and RTF. Crystal also allows you to export a page range, which is particularly helpful if you need to export a subset of a full report. SSRS exports to the following formats: XML, CSV, TIFF, PDF, web archive (MHTML), and Excel. Crystal reports don’t support TIFF and SSRS doesn’t support MS Word. Crystal provides a rich API for launching reports in both a browser and Windows Forms environment.
SSRS reports are launched in two ways:
- Specify the URL for the report, including any report parameters.
- The URL string must include the name of the Report Server to which you deployed the report, and then a parameter for the name of the report, and then any additional parameters.
- Use the SSRS web service (ReportService2005.asmx) for greater control. You’ll need to add this file as a web reference to your web application.
Previewing in Windows Forms or in the Browser
Crystal Reports provides two report viewers, one for Windows-based applications and one for web-based applications. As SSRS is essentially an ASP.NET application,users preview SSRS reports in a web browser, using the SSRS report previewer. Additionally, Microsoft supplies a ReportViewer control for Windows Forms applications so that users can view SSRS reports in a Windows environment. The ReportViewer control is a new UI class control in Visual Studio 2005 that renders SSRS reports. In addition to SQL Server stored procedures, you can bind an ADO.NET DataTable (typed or untyped) or custom collection to the report and then preview it using the ReportViewer.
Formatting and Formula Options
Both crystal reports and SSRS provides various formatting options like watermarks, tooltips, dynamic formatting, suppressing duplicates, Date Formatting and Numeric/Currency Formatting, expressions calculated fields. Crystal Reports support CSS, text rotation, or RTF rendering which SSRS don’t.
Additionally, SSRS contains a formatting option that Crystal does not have—the ability to define a detail column so that the user can interactively sort on it. In reality, many reports have multiple levels of grouping and predefined sort orders that make the need for interactive sorting to be marginal at best; however, other types of detailed listings will benefit from this type of option. Finally, SSRS allows you to add custom code to reports, either by embedding Visual Basic code directly into reports, or by adding externally created and compiled .NET assemblies.
Overall, SSRS is a better choice for the modern user who is more concerned with performance and ease of use. On the other hand, CR has been in existence for a longer period and more people are likely to be more familiar with it. In addition, those who are finicky with the smaller details might prefer the low-level features of CR. At Segue, we use crystal reports.