By: Siddharth Mehta | Comments (2) | Related: > Reporting Services Development
Problem
Reports are part of almost any IT ecosystem that generates, gathers and analyzes data. Typically when the scope of the budget approved for the IT expenditure is low, businesses start with low cost data management and reporting alternatives likes MS Access. As the volume of data keeps growing, so does the volume and variety of reports. In this journey of organic growth, eventually a time kicks in when the IT Department feels the inevitable need to migrate the data along with the reports to a more capable database management system like SQL Server and SSRS.
Considerable investment goes into building reports, and redeveloping all the reports from scratch poses a sizeable challenge in terms of cost and schedule. This situation demands an automated way to migrate reports from MS Access to SSRS to reduce partial efforts of rebuilding the report layout and associated details from scratch.
Solution
One of the features of SSDT is the ability to import reports. In order to understand how we can use this import reports feature of SSDT to import reports from MS Access, lets go step-by-step to create a sample MS Access database with reports. For the purpose of this exercise, I am using MS Access 2013 and SQL Server 2016 CTP along with SQL Server Data Tools. This feature should even work with SQL Server 2014.
Creating a Sample MS Access Database with Reports
We assume that MS Access 2013 is already installed on the machine where this exercise is being performed. Northwind is a sample MS Access database that is available from Microsoft, that contains samples tables along with data, and few reports with it. In order to install a Northwind MS Access template, just follow the steps mentioned below and in a couple of minutes you can install the Northwind template.
- Open MS Access 2013
- Type "Northwind" in the "Search for Online Templates" box at the top of the screen
- Click on Northwind 2007 Sample in the result screen
- Provide a filename for your Northwind database in the File Name textbox.
- Click the Create button and it would create your sample MS Access Database using the Northwind Template.
Open the created sample database, and you should be able to find reports under the "Reports" section, as shown in the below image.
Importing MS Access Reports using SSDT
Create a new blank SSRS project using SSDT. Click on the "Import Reports" sub-menu, under the "Project" menu, and you should be able to see the option of Microsoft Access as shown in the below image.
After opening the MS Access dialog, select the Northwind sample database that we just created in the above steps, and click Open. This would import the reports from the MS Access database into the SSRS project as shown in the below image.
One thing to note here is that these reports would not be totally error-free and ready for execution, as shown in the below image for the Invoice report. There will probably be errors due to features that aren't compatible after migration from MS Access to SSRS. There may also be missing data sources and datasets obviously, we the report developer would have to fix these issues.
Summary
This raises the question that in this case why should one use this feature at all and whether it is worth investing time in fixing a broken imported report or developing a new report from scratch. The answer to this question is - it depends on the complexity of the report. The report developer / analyst needs to make a judgment call whether it would be quicker to develop the entire layout and parameters of the entire bunch of reports hosted in MS Access or would it be just quicker to develop it from scratch in SSRS.
Generally speaking, highly complex reports having a large number of data fields and parameters are a good candidate for this method of migration as this would save considerable time in re-designing the same report by continuously comparing the new report with the older one. For reasonably smaller or simpler reports that can be developed using the SSRS Report Wizard, it would be quicker to develop such reports from scratch.
Given a scenario that if one has to migrate 50+ reports, it would make sense to import all the reports from the MS Access databases using this feature to bring it under a single SSRS solution. Once the reports are imported, then one can selectively make a call on which ones make sense to fix and which ones can be totally discarded and developed from scratch, depending upon the effort required to get the report up and running.
Next Steps
- Try to fix some of the issues on the Invoice report imported from MS Access, to get an idea how the tasks can be done after the reports are imported.
- Read this article to get an idea about MS Access Reports Features supported by SSRS.
- Check out all of the SQL Server Reporting Services tips.
- Check out all of the Microsoft Access tips.
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
