SQL Server Reporting Services and Crystal Reports:
A Competitive Analysis
Copyright 2005, Brian Bischof
Connecting to Databases
How a report connects to a data source is a critical aspect of the reporting architecture. Both Crystal Reports and SSRS have two different approaches for this and each has a different impact.
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.
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. This means changing to a production server is just as simple as with SSRS.
If you upgrade to Crystal Reports Server, you can use Business Views technology 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. This puts Crystal Reports on equal footing with SSRS data connectivity and eliminates the limitations of only using Crystal Reports Developer.
Although Microsoft created DataSet objects as the preferred method of connecting to data sources with a .NET application, SSRS doesn't support them. You have to write a custom data extension to connect the DataSet to the report. SSRS also doesn't support reporting from XML files unless you want to write more custom data extensions.
Crystal Reports supports connecting to DataSets and reporting from XML files without writing any additional code.
Another aspect to consider is how the report pulls data for the data source. Crystal Reports 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. This single resultset is used by the report to render the output. Each data source must be related to the other so that the tables can be joined on their relevant fields. If you want to report on an independent data source you have to incorporate sub-reports to do so. The benefit is that there is less overhead on the database servers because the data is queried more efficiently. The drawback is that it can require writing more complex SQL statements to join all the different tables so that they form a single resultset.
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. The drawback is that there can be crossover between the different resultsets and the database server has more overhead as it processes multiple requests for similar information.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||