Report Migration: Crystal Reports (Rpt) to Sql Reports (Rdl)

Here's a .Net example of code that grabs the Command Sql from all Crystal Reports in a given directory. It requires the Crystal 2008 .Net SDK to be installed (you can download a trial from SAP):

foreach (string file in Directory.GetFiles("c:\\projects\\Reports", "*.rpt"))
{
    Console.WriteLine(String.Format("Processing {0}...", file));
    var doc = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
    doc.Load(file);

    foreach (dynamic table in doc.ReportClientDocument.DatabaseController.Database.Tables)
    {
        if (table.ClassName == "CrystalReports.CommandTable")
        {
            string commandSql = table.CommandText;

            //TODO: do something with commandSql
        }
    }
}

To get the SQL as Crystal would build it when running a report, see this link: SAP Note 1280515 - How to extract SQL query from Crystal reports using RAS sdk.

I believe to do this, you need to supply the report parameter values so that Crystal can connect to the database in order to build the SQL. In the example, since a Report Viewer control is used, Crystal can prompt the user for the parameters.

From my experience there are few things to think about both things:

I. RDL reports are HOSTED reports generally. This means you need to implement SSRS Server. They are a built in extension of Visual Studio from SQL Server for the reporting language. When you install SSRS you should have an add on called 'Business Intelligence Development Studio' which is much easier to work with the reports than without it.

R eport

D efinition

L angauge

Benefits of RDL reports:

1. You can host the reports in an environment that has services running for you on them.
2. You can configure security on an item or inheriting level to handle security as a standalone concept
3. You can configure the service to send out emails(provided you have an SMTP server you have access to) and save files on schedules
4. You have a database generally called 'ReportServer' you can query for info on the reports once published.
5. You can access these reports still through 'ReportViewer' in a client application written in ASP.NET, WPF (with a winform control bleh!), or Winforms in .NET using 'ProcessingMode.Remote'.
6. You can set parameters a user can see and use to gain more flexibility.
7. You can configure parts of a report to be used for connection strings as 'Data Sources' as well as a sql query, xml, or other datasets as a 'Dataset'. These parts and others can be stored and configured to cache data on a regular basis.
8. You can write .NET proxy classes of the services http:// /ReportServer/ReportingService2010 or /ReportExecution2005. You can then make up your OWN methods in .NET for emailing, saving, or manipulating SSRS data from the service directly of a Server hosting SSRS reports in code. Programmatically Export SSRS report from sharepoint using ReportService2010.asmx

Downsides:

1. SSRS is kind of wonkey compared to other things on getting it up fast. Most people get confused by the security policy and designing reports as an 'add on' to VS. SQL 2005 = VS BIDS 2005 , SQL 2008 = VS BIDS 2008, SQL 2012 = VS BIDS 2010(LOL).
2. Continuing on 1 the policy for security settings IMHO are idiotically overcomplex. There is server security, database security and roles, two security settings on the page hosted for the service. Most people only set up an admin than can't get in and wonder why other users cannot. Most common complaint or question on SSRS is related to getting in generally from my experience.
3. You can use 'expressions' that will supposeduly 'enhance' your report. Often times you do more than a few and your report goes to a crawl in performance.
4. You have a set amount of things you can do and export to. SSRS has no hover over reporting I know of without a javascript hack.
5. Speed and performance can take a hit as the stupid SSRS config recycles the system and a first report can take a while at times just loading the site. You can get around this by altering it but I have found making a keep alive service for it works better.

II. RDLC reports are CLIENT CONTAINED reports that are NOT HOSTED ANYWHERE. The extra c in the name means 'Client'. Generally this is an extension of the RDL language meant for use only in Visual Studio Client Applications. It exists in Visual Studio when you add a 'reporting' item.

Benefits of RDLC reports:

1. You can hookup a wcf service much much much more easier to the dataset.
2. You have more control over the dataset and can use POCO classes filled with Entity framework objects or ADO.NET directly as well as tables themselves. You can monkey with the data for optimization it before binding it to the report.
3. You can customize the look more with add on's directly in code behind.

Downsides:

1. You need to handle parameters on your own and while you can implement wrapper methods to help the legwork is a little more than expected and unfortunate.
2. The user cannot SEE the parameters in a 'ReportViewer' control unless it is in remote mode and accessing an RLD report. Thus you need to make textboxes, dropdowns, radio buttons on your own outside the control to pass to it. Some people like this added control, I do not personally.
3. Anything you want to do with servicing the reports for distribution you need to build yourself. Emailing, subscriptions, saving. Sorry you need to build that in .NET or else implement a proxy that already does that from above you could just be getting using hosted reports.

Honestly I like both for different purposes. If I want something to go out to analysts that they use all the time and tweak for graphs, charts, drill downs and exports to Excel I use RDL and just have SSRS's site do all the legwork of handling the email distributions. If I want an application that has a report section and I know that application is it's own module with rules and governance I use an RDLC and having the parameters be smaller and be driven by the decisions the user made before getting to the report part of what client they are on and site and then they usually just choose a time frame or type and nothing more. So generally a complex report I would use RDL and for something simple I would use RDLC IMHO.

I hope that helps.