SQL Server Reporting Services and Crystal Reports:
A Competitive Analysis
Copyright 2005, Brian Bischof
Passing Parameters
Passing parameters lets the user filter data output for their specific needs. Parameters are also used to customize the formatting of the report. Both Crystal Reports and SSRS allow users to specify parameters prior to printing a report.
Crystal Reports provides extensive parameter support. The types of parameters supported are: single value, multi-value, and range value (e.g. Start date to End date), or a combination of all three.
SSRS parameters only support entry of a single value. It doesn't support multi-value parameters or range parameters. For example, the user can't be presented with a list of Employees and select more than one for reporting on. Doing so requires writing custom code and writing more complex SQL queries.
Dynamic Cascading Parameters in Crystal Reports allow you to connect each parameter to a live data connection. Parameters can also be linked together so that what the user selects in one parameter will filter the selection list of another parameter. Both products support Dynamic Cascading Parameters.
One thing that I see a lot of people commenting on is the lack of a date-time picker for parameters. SSRS makes you enter date parameters as regular text and doesn't have a built-in calendar component for selecting dates. Although this is more of a benefit than a requirement, it is high on the list of features that people would like to have. Crystal Reports does have a built-in calendar control.
Calculating Formula Values
Creating formulas in a report is an essential aspect of providing relevant data to the reader. Databases consist of raw data and you use formulas to apply business logic to the data so that it is more meaningful to the reader. Formulas are also useful for providing custom formatting of data during runtime (e.g. when inventory is below the minimal level, change the color to red).
Both Crystal Reports and SSRS have a formula editor that lets you write functions and perform runtime formatting. The Crystal Reports editor is more advanced than the SSRS editor. The Crystal Reports editor provides a better user interface for determining what functionality is available and helping with the syntax. SSRS only gives you an empty box and lets you type. There is no assistance provided at all.
The maturity of Crystal Reports shows in its extensive library of built-in functions. This isn't matched in SSRS. For example in the financial reporting area SSRS provides only 13 built-in functions and Crystal Reports gives you over 50. For working with dates and times SSRS gives you a few simplistic functions such as DateDiff() and WeekDayName(). Crystal Reports provides over four dozen date related functions of which the most significant let you group records by financial quarter and perform account aging.
Another problem with SSRS is that it doesn't support sharing variables between sub-reports and the main report. Nor does it encourage sharing data between objects. A frequent report requirement is to take data derived from a sub-report and display it on the main report (e.g. subtotals). Implementing this functionality in SSRS requires more complex SQL programming.
A major downfall of SSRS formulas is that they don't have an order of execution property. For example, in Crystal Reports you can set whether a formula can be calculated before another formula is calculated or you can set it to execute prior to a report printing or while records are being printed. This prevents a lot of problems with incorrect results and allows formulas to work with complex grouping use cases. With SSRS, this isn't possible and problems will result. For example, a running total can appear fine in the details section of a report but show up as zero within the group header.
SSRS aggregate functions are limited to being calculated within a single recordset or within a single area. This limitation is due to the architecture of object based reporting because report objects don't share data. Complex reports require using a combination of objects and nesting objects but SSRS doesn't provide a means of letting these objects pass values to each other.
SSRS has a limitation with performing aggregate functions. The first limitation is that aggregate functions can't be performed on values in a textbox. The second limitation is that you can't perform aggregates of aggregates. For example, the function Sum(First(Fields!Inventory.Price, "Group")) doesn't work in SSRS.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||