Crystal Decisions vs. SSRS
Below are my observations on some of the differences between CR/CE and SSRS. These
observations are based on the use of these software packages for ad hoc report design and
web delivery of the reports. I don’t do any standalone application development and can’t
speak to their capabilities in these areas. This list is by no means complete and some
things are completely a matter of opinion. I hope this can help you in your decision.
Installation
Both CR and CE are significantly easier to install. No documentation on how to
configure SSRS. This could be because it is a trial version of a new product.
Report Designers
1. CR is significantly more user friendly for non programmers.
2. SSRS uses visual studio .NET. If you are intimately familiar with Visual Studio
IDE this is not an issue. For those of us unfamiliar with it requires a learning
curve.
3. CR has easy to use running totals with a simple interface to design. SSRS I am
sure can do this using a VB functions but not as easily. There are running totals
but I have not used them so I do not know how easy to use or if they are as
flexible as CR running totals.
4. SSRS has dynamic pick lists that can be run off your report data or even an
independent table. Multiple parameters can have picklists that are determined by
earlier selections. This is easy to implement and dynamic at run time unlike CE
which saves the picklists to the published file. This is a very big convenience.
5. SSRS allows you to build drill down cross tabs while CR does not. The cross tabs
however do not export to excel as pivot tables that allow drill down. This can be
accomplished by exporting to OWC enabled HTML.
6. When you make changes even just formatting changes the data must be refreshed.
This can slow down development if you have several changes to make. After you
change the layout you can’t preview it using saved data.
7. It is easier to repeat column headings on new pages in SSRS.
8. SSRS has a tabular format option which allows you to arrange items in columns.
This makes it easier to layout reports and improves export to excel functionality.
9. CR handles calculations with summaries a little easier. Particularly if you want
percent of total summaries.
10. SSRS produces multiple group summaries much more easily. Simple cut and
paste.
11. SSRS reports are saved in an XML format. I have not worked at it but it should
be easy to build a documentation database without purchasing third party
software.
12. SSRS Page headers cannot contain Data fields.
13. In SSRS tables if you try to put data in the table heading so it repeats on every
page the data does not change from page to page as the data in the table changes.
14. SSRS handles SQL server stored procedures a little better than CR. I have had
problems with reports that run complicated stored procedures particularly those
that store intermediate data in tables.
15. Drill through reports as well as subreports. Drill through is similar to on demand
sub reports except there is no limit to the levels. Each report can link to another
report.
Designer Conclusion
If you are developing ad hoc reports that don’t require wide or repeated distribution it is
difficult to top CR. It is easy to use and very powerful. SSRS does have a few
advantages but most of those are in deployment the reporting services web pages.
Deployment Comparisons
1. CE has an easy to somewhat customize interface. End User can set colors, and a
couple of other settings like how many reports per page, list view vs. thumbnails.
2. Deploying reports from VS is much easier than publishing them in CE.
a. CE gives every report a cryptic name when it is published. SSRS uses a
standard file structure naming convention.
b. If you made changes to several reports and want to republish them with
the publisher the reports must be deleted then republished. All scheduling
and shortcuts for these reports must be rebuilt. The other option is to save
them into the enterprise folders. This eliminates database passwords and
settings for parameters. This is a huge pain when your users tend to run
reports on demand. You also must be careful to save the report to the
folder the master copy is kept to prevent versioning problems. SSRS you
just select deploy and it overwrites the old file with the new one
preserving all scheduling info. A huge improvement.
3. Scheduling reports in SSRS is more complicated but more powerful.
a. In CE all scheduled reports get saved in the History file. SSRS allows you
to set whether or not an execution gets saved and also lets the user save a
report hey just ran to the History file.
b. Changing the schedule is much easier in SSRS. You select the
subscription and edit the settings. CE requires a new schedule and then
you must delete the old one. This is huge if your distribution lists change
regularly.
4. SSRS has data driven subscriptions. CE does not.
a. This is a huge deal. If you have a set of reports that all go to the same
users you can just create a table with the distribution list and it will send to
everyone on the list. Add people, Remove people it is all done in the table
you created.
b. You can run the report repeatedly with different parameters for each user
all driven by the table and just one subscription.
c. All the settings for the subscription can be set in this table even export
format so different users can get the same report in different formats. A
huge improvement over CE.
5. SSRS has linked reports. If you have a report that runs with a small set of varying
parameters you can create linked reports. They look like new reports but just
have default parameters set differently. Here is an example. We have a report
that can be run for the last workday, last work week, or last work month. In SSRS
I can deploy the report than create three linked reports one for the daily, one for
the weekly, and one for the monthly. When the end users want to run the report
they don’t need to bother with entering parameters, they can just run the selected
report. (it automatically selects the dates based on which type of report you
select.)
6. SSRS can use cached reports. If you use a non production database for reporting
that gets updated once a day you can set reports to cache data for the day. Then
when someone runs the report it saves the data on the report server db. If
someone comes back later and runs the report again with the same parameters it
just pulls the saved data saving on system resources. You can also then set the
reports to run during down times and cache data to further save on system
resources. Much easier than scheduling and searching history of scheduled
reports.
7. I don’t know about other peoples experience but I have had large problems with
scheduled reports in CE. I can’t compare reliability yet but so far no problems
with SSRS.
8. CE allows you to use date pickers which are not available in SSRS
Differences between SSRS and Crystal Reports:
Ease of hosting reports: - Using the URL technology in RS we can host reports more easily than in crystal report where we need to make a UI for the same.
Supporting platforms: - Crystal can run on windows, IBM and sun while RS (reporting services) can run only on windows environment.
Client tools: - In reporting services we have Business intelligence ( BI ) and development studio while in crystal its Report designer.
Caching: - This achieved in crystal by using cache server while in reporting services it’s stored as snapshots in Reportserver database.
Export formats:- In crystal we have HTML,PDF,Excel,XML,Word , PDF , RTF , CSV, text files while in SQL Server 2005 we have all the formats above it also gives capability to extend custom reporting formats.
Data sources:- Crystal support more data sources while RS only supports Microsoft and oracle data sources. Crystal supports ADO, COM, Database excel Access, Exchange, NT, Xbase, JDBC, File system and Paradox. RS supports only SQL Server, Oracle, ODBC, OLEDB and you can also extend additional data sources which does not exists in crystal reports.
Version issues: - One of the main issues faced in crystal is it have different versions which makes it difficult to use, while RS comes with SQL Server minimizing the version issue.
Web server support: - Crystal can run on IIS 5/6, Apache, lotus etc while RS works only with IIS 5.0 and above.
Below are my observations on some of the differences between CR/CE and SSRS. These
observations are based on the use of these software packages for ad hoc report design and
web delivery of the reports. I don’t do any standalone application development and can’t
speak to their capabilities in these areas. This list is by no means complete and some
things are completely a matter of opinion. I hope this can help you in your decision.
Installation
Both CR and CE are significantly easier to install. No documentation on how to
configure SSRS. This could be because it is a trial version of a new product.
Report Designers
1. CR is significantly more user friendly for non programmers.
2. SSRS uses visual studio .NET. If you are intimately familiar with Visual Studio
IDE this is not an issue. For those of us unfamiliar with it requires a learning
curve.
3. CR has easy to use running totals with a simple interface to design. SSRS I am
sure can do this using a VB functions but not as easily. There are running totals
but I have not used them so I do not know how easy to use or if they are as
flexible as CR running totals.
4. SSRS has dynamic pick lists that can be run off your report data or even an
independent table. Multiple parameters can have picklists that are determined by
earlier selections. This is easy to implement and dynamic at run time unlike CE
which saves the picklists to the published file. This is a very big convenience.
5. SSRS allows you to build drill down cross tabs while CR does not. The cross tabs
however do not export to excel as pivot tables that allow drill down. This can be
accomplished by exporting to OWC enabled HTML.
6. When you make changes even just formatting changes the data must be refreshed.
This can slow down development if you have several changes to make. After you
change the layout you can’t preview it using saved data.
7. It is easier to repeat column headings on new pages in SSRS.
8. SSRS has a tabular format option which allows you to arrange items in columns.
This makes it easier to layout reports and improves export to excel functionality.
9. CR handles calculations with summaries a little easier. Particularly if you want
percent of total summaries.
10. SSRS produces multiple group summaries much more easily. Simple cut and
paste.
11. SSRS reports are saved in an XML format. I have not worked at it but it should
be easy to build a documentation database without purchasing third party
software.
12. SSRS Page headers cannot contain Data fields.
13. In SSRS tables if you try to put data in the table heading so it repeats on every
page the data does not change from page to page as the data in the table changes.
14. SSRS handles SQL server stored procedures a little better than CR. I have had
problems with reports that run complicated stored procedures particularly those
that store intermediate data in tables.
15. Drill through reports as well as subreports. Drill through is similar to on demand
sub reports except there is no limit to the levels. Each report can link to another
report.
Designer Conclusion
If you are developing ad hoc reports that don’t require wide or repeated distribution it is
difficult to top CR. It is easy to use and very powerful. SSRS does have a few
advantages but most of those are in deployment the reporting services web pages.
Deployment Comparisons
1. CE has an easy to somewhat customize interface. End User can set colors, and a
couple of other settings like how many reports per page, list view vs. thumbnails.
2. Deploying reports from VS is much easier than publishing them in CE.
a. CE gives every report a cryptic name when it is published. SSRS uses a
standard file structure naming convention.
b. If you made changes to several reports and want to republish them with
the publisher the reports must be deleted then republished. All scheduling
and shortcuts for these reports must be rebuilt. The other option is to save
them into the enterprise folders. This eliminates database passwords and
settings for parameters. This is a huge pain when your users tend to run
reports on demand. You also must be careful to save the report to the
folder the master copy is kept to prevent versioning problems. SSRS you
just select deploy and it overwrites the old file with the new one
preserving all scheduling info. A huge improvement.
3. Scheduling reports in SSRS is more complicated but more powerful.
a. In CE all scheduled reports get saved in the History file. SSRS allows you
to set whether or not an execution gets saved and also lets the user save a
report hey just ran to the History file.
b. Changing the schedule is much easier in SSRS. You select the
subscription and edit the settings. CE requires a new schedule and then
you must delete the old one. This is huge if your distribution lists change
regularly.
4. SSRS has data driven subscriptions. CE does not.
a. This is a huge deal. If you have a set of reports that all go to the same
users you can just create a table with the distribution list and it will send to
everyone on the list. Add people, Remove people it is all done in the table
you created.
b. You can run the report repeatedly with different parameters for each user
all driven by the table and just one subscription.
c. All the settings for the subscription can be set in this table even export
format so different users can get the same report in different formats. A
huge improvement over CE.
5. SSRS has linked reports. If you have a report that runs with a small set of varying
parameters you can create linked reports. They look like new reports but just
have default parameters set differently. Here is an example. We have a report
that can be run for the last workday, last work week, or last work month. In SSRS
I can deploy the report than create three linked reports one for the daily, one for
the weekly, and one for the monthly. When the end users want to run the report
they don’t need to bother with entering parameters, they can just run the selected
report. (it automatically selects the dates based on which type of report you
select.)
6. SSRS can use cached reports. If you use a non production database for reporting
that gets updated once a day you can set reports to cache data for the day. Then
when someone runs the report it saves the data on the report server db. If
someone comes back later and runs the report again with the same parameters it
just pulls the saved data saving on system resources. You can also then set the
reports to run during down times and cache data to further save on system
resources. Much easier than scheduling and searching history of scheduled
reports.
7. I don’t know about other peoples experience but I have had large problems with
scheduled reports in CE. I can’t compare reliability yet but so far no problems
with SSRS.
8. CE allows you to use date pickers which are not available in SSRS
Differences between SSRS and Crystal Reports:
Ease of hosting reports: - Using the URL technology in RS we can host reports more easily than in crystal report where we need to make a UI for the same.
Supporting platforms: - Crystal can run on windows, IBM and sun while RS (reporting services) can run only on windows environment.
Client tools: - In reporting services we have Business intelligence ( BI ) and development studio while in crystal its Report designer.
Caching: - This achieved in crystal by using cache server while in reporting services it’s stored as snapshots in Reportserver database.
Export formats:- In crystal we have HTML,PDF,Excel,XML,Word , PDF , RTF , CSV, text files while in SQL Server 2005 we have all the formats above it also gives capability to extend custom reporting formats.
Data sources:- Crystal support more data sources while RS only supports Microsoft and oracle data sources. Crystal supports ADO, COM, Database excel Access, Exchange, NT, Xbase, JDBC, File system and Paradox. RS supports only SQL Server, Oracle, ODBC, OLEDB and you can also extend additional data sources which does not exists in crystal reports.
Version issues: - One of the main issues faced in crystal is it have different versions which makes it difficult to use, while RS comes with SQL Server minimizing the version issue.
Web server support: - Crystal can run on IIS 5/6, Apache, lotus etc while RS works only with IIS 5.0 and above.
