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 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 Designer includes an import tool for taking Access reports and making them into Reporting Services reports. Not everything in your Access reports imports directly into Reporting Services. Even so, this import tool gives you a leg up on having to rebuild each entire report from scratch.

We now go through a sample report import 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

Feature 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 eliminate 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 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 was 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 appears.

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

6. The Report Designer imports any reports it finds in the selected MDB file.

7. When the import is complete, you 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 appears.

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

11. If you examine the connection string, you 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. Change the query in the SQL Panel to the following:

     SELECT InvoiceHeader.CustomerNumber, InvoiceHeader.InvoiceNumber,        InvoiceHeader.TotalAmount, PaymentInvoiceLink.PaidDate,        PaymentInvoiceLink.PaidAmount FROM   InvoiceHeader INNER JOIN PaymentInvoiceLink         ON InvoiceHeader.InvoiceNumber=PaymentInvoiceLink.InvoiceNumber INNER JOIN Payment         ON PaymentInvoiceLink.PaymentNumber=Payment.PaymentNumber WHERE (InvoiceHeader.PaidInFullFlag=1) ORDER BY InvoiceHeader.CustomerNumber, InvoiceHeader.InvoiceNumber,          PaymentInvoiceLink.PaidDate

16. Run the query to verify it now works properly with the SQL tables.

17. Clear your query results.

18. Select the Preview tab.

19. Make any additional formatting changes necessary to get the report looking as it should.

20. Select Save All from the toolbar when the report is completed.

Task Notes   You can see the column headings from the Access report were 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.