A Conversion Experience

Reporting Services is not the first report-authoring environment to come along. Hundreds of thousands of reports have been created using other tools. If you have legacy reports and are looking to switch to Reporting Services, these legacy reports will need to be re-created—that is, unless your legacy reports were written in Microsoft Access. If that is the case, you are in luck.

The report-authoring environment in Visual Studio includes an import tool for taking Access reports and making them into Reporting Services reports. Not everything in your Access reports will import directly into Reporting Services. Even so, this import tool will give you a leg up on having to rebuild each entire report from scratch.

We will go through a sample report import here to give you an introduction to the import tool. You can consult the Reporting Services Books Online for more information on exactly which features the Access report import will and won’t import.

The Paid Invoices Report

Features Highlighted

• Importing an Access report

Business Need The Galactic Delivery Services accounting department has an Access report that lists paid invoices. The accounting department would like to convert this report to Reporting Services and get rid of the InvoiceInfo.mdb file. The MDB file uses linked tables to pull data from the SQL Server database.

Note

The Access import can only be done if you have Microsoft Access installed on the PC where you are running Reporting Services.

Task Overview

1. Import the Access Report and Change the Data Source

Paid Invoices Report, Task 1: Import the Access Report and Change the Data Source

1. If you have not already done so, download the InvoiceInfo.mdb file from the website for this book.

2. Create a System ODBC data source called Galactic that points to the Galactic database. Use “GalacticReporting” for the SQL login and “gds” for the password. This ODBC data source is used by the linked tables in InvoiceInfo.mdb to access the Galactic database in SQL Server. This ODBC data source must be in place in order for the conversion to function properly.

   Caution

   This is a Windows ODBC data source, not a Reporting Services data source. Use the ODBC Data Source Administrator under Administrative Tools in the Control Panel to create this data source.

3. Reopen the Chapter08 project if it has been closed.

4. Right-click the Reports folder in the Solution Explorer and select Import Reports | Microsoft Access from the context menu. The Open dialog box will appear.

5. Browse to the InvoiceInfo.mdb file, select it, and click Open.

6. Visual Studio will import any reports it finds in the selected MDB file.

7. When the import is complete, you will have a new report called PaidInvoices.rdl in your Solution Explorer. Double-click this report to open it.

8. Select the Data tab.

9. Click the … button in the Query Builder toolbar. The Dataset dialog box will appear.

10. Click the … button next to the Data Source drop-down box. The Data Source dialog box will appear.

11. If you examine the connection string, you will see that the data source is going back to the MDB file to get its data. Because the MDB file was using linked tables and pulling data from the SQL Server database, it makes sense to now go to the SQL Server database directly.

12. Click Cancel to exit the Data Source dialog box.

13. Select “Galactic (shared)” from the Data Source drop-down list.

14. Click OK to exit the Dataset dialog box.

15. Remove the “dbo_” prefix from the table names in the SQL panel.

16. Run the query to verify that it will now work properly with the SQL tables.

17. Clear your query results.

18. Select the Preview tab.

19. Select Save All from the toolbar.

Task Notes You can see that the column headings from the Access report have been placed in the page header in the Reporting Services report. This looks rather strange, but it is nothing a minute or two of additional formatting couldn’t fix. The import does not create a perfect replica of your Access report in Reporting Services. It does, however, save you a lot of time over rebuilding each report from scratch.