First of all, why migrate from Crystal Reports to MS Reporting Services? Does it really bring in some considerable benefits? The short answer is yes, it does. SSRS proves to be much more suitable for scalable tasks, delivers better .NET integration as both technologies share the same manufacturer, Microsoft. SSRS is also free as long as you already have an SQL Server license. In order to determine whether your business is ready to migrate to SSRS from Crystal Reports, please refer to this article.

And if you already decided to switch to SSRS, you are probably looking for the simplest and the most affordable way to accomplish this. We will try to provide a thorough insight into this problem.

Ways to convert Crystal Reports to SSRS

Years after SSRS was released, the preferable way to migrate from Crystal Reports is still manual conversion. This is a bit strange. Indeed, after googling around, you perhaps already have found a number of services offering automatic conversion of Crystal Reports to SSRS. At the same time many other resources claim that automatic migration is nearly impossible in case of the CR/SSRS pair. Who to believe? Let’s get this straightened now.

Basically, the migration procedure consists of several steps:

• 1. Analyze reports you have in Crystal Reports.
• 2. Plan your migration.
• 3. Based on the structure of your reports, re-create datasets and connect them to data sources in SSRS.
• 4. Create dataset queries to fetch all required fields for the report in SSRS.
• 5. Create the layout of the report in SSRS.
• 6. Establish row-level data, groupings, and subtotals
• 7. Create formulas and custom functions.
• 8. Create matrices, charts, multiple data sources, subreports, and linked reports.

In other words, converting a Crystal Reports report to a Reporting Services report means you have to manually create a new SSRS report from scratch copying the structure and the internals of the original report. A tedious and time-consuming task unless you have merely a handful of reports to migrate. Otherwise, you surely want to speed up the process somehow.

Steps 4-8 above look like they can be automated, so what’s the problem? Why not delegate them to some tool? The problem is: conversion can only be automated to some extent. Principles of these two reporting systems are different, and these differences lead to discrepancies in layout, formulas and expressions, error handling, dataset parameters and so on. When adopting reports manually, all these discrepancies are handled relatively easy, while automated conversion fails to provide accurate results in many cases (see below).

The more complex a report is, the more glitches and deviations from the original you will find in an auto-converted SSRS report. This means, even if you chose automatic migration from Crystal Reports, you still have to examine the results for possible issues and manually fix them. Hiring a specialist who knows all the pitfalls along this way is generally a good idea.

When automation fails

Here is a brief and incomplete list of possible issues that may render an auto-converted SSRS report non-working or ruin its layout.

Data source issues

• Shared data source sometimes returns exception while generating a report. Shared data sources allow you to use the same data for multiple reports. Provided the bulk of data sources are shared, this is a huge problem.
• Datasets need to be refreshed or even rebuilt due to differences in parameter names. In Crystal Reports and SSRS the order of parameters and their names may be different, which results in a report being unable to connect to the data source.
• Ranged parameters. In CR you can easily define boundaries of a given value thanks to RangedParameter. In SSRS, however, you have to specify lower and upper boundaries separately.
• Renamed parameters. When a report parameter is renamed you have to find and change all dependent items manually.
• Redundant or absent parameters. Some parameters a Crystal Reports report has are redundant in SSRS, while parameters SSRS requires may be absent in the report auto-converted from CR.

Report layout issues

• Layout fixes. Complex reports featuring a non-basic layout often require manual layout fixes (e.g. parts of the report intersect with each other or intersect page borders)
• Inflexible pagination. While Crystal Reports was meant to produce printer-friendly reports, SSRS is more web-oriented. Therefore, for accurate pagination of the converted report, some manual adjustment is required.
• Object overlapping. When it comes to overlapping, SSRS doesn’t provide the same freedom as Crystal Report does. Therefore, overlapped objects that looked great in Crystal Reports sometimes render incorrectly in SQL Server Reporting Services.
• Keeping together does not work well. Due to the fact that SSRS is relatively weak in pagination, the Keep Together option has its issues. Manual adjustment of a report might be required.

VB code or expression issues

• Expressions and custom code are hard to debug. SSRS does not provide any kind of expression evaluation other than building a report. And if an error occurs, it is hard to locate the erroneous expression. This must be taken into account when working with expressions in an auto-converted report.
• Broken expressions. Often, code imported from Crystal Reports uses functions, properties and values that do not exist in SSRS. Therefore, the corresponding expressions simply do not work. Taking into account debug troubles mentioned above, the situation becomes even worse.
• Syntax errors in VB code. Automatic conversion of Crystal Reports to SSRS often results in numerous syntax errors in VB code. We usually replace VB code with expressions to fix this.

Other known problems

• No way to pass values from subreports to the main report. This requires changing the logic of a report and manual rebuilding.
• Multicolumn report difficulties. SSRS barely supports multicolumn reports without using nested table objects. Additional manual adjustment may be required for this workaround to work properly.
• Replace textboxes with tables. Whenever data are fetched from the database, a textbox object in SSRS won’t do. You need to replace it with an appropriate table.

Every issue mentioned above requires attention and manual fixing or adjustment.

Conclusion: Auto vs. Manual

Automated conversion of Crystal Reports to SSRS definitely has issues. However, in many cases adjustments needed to fix glitches the output SSRS report will have are minor. This means simple reports based on standard templates can be auto-converted with minimum efforts.

Although, as complexity of a report grows, the share of manual operations increases too. At some point, it is simply not worth running automated conversion at all. The result, a half-baked non-functional report, will be disappointing, while manual adjustment required to bring such a report back to life under SSRS will be hard and possibly unrewarding.

The footnote is: do not rely on Crystal Reports to SSRS auto-conversion if it isn’t supervised by trained specialists who know all the pitfalls of automatic conversion and can either avoid or quickly fix them manually.

Reporting Services, Crystal to SSRS migrations

‍