Microsoft's Azure recently celebrated its first birthday, and in the year since its initial release, Azure has added or improved many features, and many other features have been announced. One of the newly announced features is Azure Reporting, built on SQL Server Reporting Services. In light of that announcement, it may seem silly to have another reporting solution in the mix. If you're starting a new BI application, which needs reporting, it's worth considering the available reporting solutions for Azure, to make the best choice. But if you want to migrate legacy reports from Crystal Reports or Access, ComponentOne reporting is a no-brainer. With ComponentOne reporting, you can directly import these legacy reports into the C1Report format, which is utilized by the C1ReportViewers on all platforms
In this post, we'll look at importing legacy reports from Access and Crystal Reports, and modifying these reports to use SQL Azure as the database. In some cases, Access reports have been written against SQL Server databases, but in most cases, the data are stored in Access, too. If you need to migrate data from Access to either SQL Server 2005/2008 or SQL Azure, read my Part 0 post linked below. It's really easy to get the schema and data into the desired target database.
Here's an outline of the posts in this series:
- Part 0: Migrating Access to SQL Server/SQL Azure using SQL Server Migration Wizard for Access
- Part 1: Importing Legacy Reports from Access or Crystal Reports (you're reading this now)
- Part 2: Designing Reports from Scratch
- Part 3: Using the ASP.NET Ajax Report Viewer (link to Silverlight viewer)
- Part 4: Generating reports in a worker role
- Part 5: Emailing reports from a worker role
The import, conversion and editing of the reports is handled by the C1ReportDesigner. The C1ReportDesigner is found under Start >> ComponentOne >> Studio for (ASP.NET Ajax/Widows Forms/Silverlight/WPF) >> Reporting Tools. You'll see two report designers installed-C1ReportDesigner is targeted to the .NET 2.0 CLR (which is the base for .NET 3/3.5), and C1ReportDesigner4.0 is targeted to the .NET 4.0 CLR. Both designers have the same features, it's merely the runtime you use for your application or that you have installed on your machine.
The C1ReportDesigner is built with ComponentOne WinForms components, and the UI conforms to the Windows 7/Office 2010 standards. The source code is available in the Report Designer Edition if you want to embed the designer in your applications.
Open the designer, and click on the C1 button in the upper left corner.
This opens the equivalent of a File menu, and the Import function is found here.
Choose Import, navigate to your Access or Crystal Reports file, and click Open.
You can then choose which reports to import. The main difference between importing Crystal Reports and Access Reports is Crystal Reports are imported one at a time, while a single Access application may contain a number of reports, all of which can be imported at the same time.
After importing the reports, you'll notice they are all listed. C1Report format allows you to group all related reports into a single XML file. This makes handling relates reports much easier, and is great for master/detail reports. You can split the reports into separate files later, if you want to. For Crystal Reports, multiple reports can be imported into the same C1Report file, or they can be imported into separate files. Selecting a report displays its design in the designer panel.
The first edit you need to consider is the connection string. For Crystal Reports using SQL Server as a back-end database, you probably won't need to update the connection string unless you're also changing the server at this time. For Access, even if you used the SSMWA to migrate your Access database (see Post 0 above), and you selected to have the application links updated, the connection strings will still point to the original Access database. There are a couple ways to update the connection strings should you need to.
The first way is to edit the connection strings in the report designer. Click on the Data Source button, and then on the ellipses (.) button to open the Data Link Properties dialog.
The Data Link Properties dialog is a familiar interface for building connection strings, providing tabs for choosing providers and connection details.
Using the Data Source means changing the connections for each report one at a time. If there is a large number of reports, you probably want to change all the connections at once. The C1Report format is simply an XML file, and by editing this file directly in a text or XML editor, a simple search and replace will change all the connection strings. It's a good idea to change the first report with the Data Source editor, and test that report before changing the rest of the reports.
Once the connection strings have been updated, it's time to do a sanity check on the query syntax. If you've used some Access specific syntax, you'll need to change from Jet-SQL to TSQL. For instance, the query below uses "No" as a column value.
However, the Discontinued column is converted by SSMWA to a Bit type in SQL Server.
In order to make this query run correctly, you need to change the "No" to "0" (zero). The query should be usable-the Preview function will confirm this. If this correction needs to be made in a number of reports, the XML can be edited directly.
Once any edits are made (if any are necessary), you're now ready to make any design changes (if you want to), or incorporate these reports into your applications.