How to Import Reports from MS Access to SSRS

8
1468

Aim :-  Import Reports from MS Access to SSRS i.e. Microsoft Access to SQL Server Reporting Services.

Description :-  In this article, we are going to learn how to import reports from MS Access to SSRS (SQL Server Reporting services) by using Report designer. Report designer is a client tool that is used to create report projects. When you import reports from MS-Access database (.mdb) or from an Access project file (.adp) to reporting services, Report manager converts each report to a RDL (Report Definition Language) file. After converting it to RDL file, it then saves the reports in the Report project.

Note :-  We must have Access 2002 or a later version installed on the same computer on which report designer is installed, in order to use the import feature. The Data source for the Access reports must be available when reports are imported.

Now, let’s see all these things practically.

STEP 1.  Create MS Access Reports

  1. Open MS-access. Now, Click on Blank database.
  2. Once we click on Blank database, it will pop up one small window. You can see that small window at the right side bottom oright side f below screen shot.
  3. Give a database name and path.

Follow the below screen shot :-

Create database in MS Access

Create one table named as “Emp”. I don’t want to go and show all the things related to table creation now, it is out of scope of this article. In our database already I have one table called as “Emp”.

Follow the below screen shot to see the data of “Emp” table.

Fetching all the data of emp table in MS Access

Create couple of reports by using the above emp table to display the information of employees who belongs to deptno 20, deptno 10. Below two screenshots will show the dept20, dept10 data.

Reports in MS Aceess

STEP 2.  Import Reports from MS Access to SSRS.

  1. Open Business Intelligence Development Studio (BIDS).
  2. Create a Report project and name it as say “PHPRING-reports”.
  3. In solution explorer, Go to Reports and Right click on it. Go to Import Reports option and click MS Access.

If anybody is facing any issue while import reports from MS Access to SSRS, Please follow the below Screen shot.

Import Reports from MS Access to SSRS

Note :- We can’t Import the reports individually and we don’t have any option to Import reports individually. At a time, we can Import all the reports which exists in that current database. Once we click on “Microsoft Access” option as we shown in above figure, It immediately connect to Access  database and try to pull the all reports from “Access database ” to “SSRS”.

Follow the attached screen shot and see the imported reports from MS Access to SSRS. We can also observe that those reports are converted into RDL (Report Definition Language). Just see the extension of each report.

Check RDL extension of Converted reports

Finally we can open each report and see the Data exists or not. Now, I am going to open my first report “Dept 20 data”, later we will open our second report.

Report of Dept 20 data:-

First SSRS Report - Dept 20 data

Report of Dept 10 data:-

Second SSRS Report - Dept 10 data

Note :-  Page layout in MS Access is different than in Reporting services. MS Access arranges items on the page using “Bands”, that is sections arranged vertically on the page. These sections may include the Report header, Report footer, Page header, Page footer, Groups and Detail. Reporting services provides more flexible layout. Data regions provide Grouping and Detail and we can place multiple data regions anywhere in the body of the report, even side by side. Reporting services also uses a “Banded” Page header and Page footer, similar to the Page header and Page footer in MS-Access.

Summary :-

  1. Created one MS-Access database and named it as “Employee“.
  2. Within that database, created one table named “Emp” and created some sample reports by using that table data.
  3. Created one SSRS project.
  4. Import reports from Ms Access to SSRS project.

Free e-books and Pdf’s :-

Please Subscribe or Like PhpRinG Tutorials for free e-books and pdf’s.

  • Launching Very Soon free e-books and pdf’s on SSRS – MS SQL Server Reporting Services.

With this we reached to the end of this article. I really hope this post named Import reports from MS Access to SSRS enlighten you with some features of SSRS and MS Access.Your feedbacks and Compliments make us more inspired towards our passion on writing and learning MSBI. Please drop them as comment below.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 10.0/10 (14 votes cast)
VN:F [1.9.22_1171]
Rating: +15 (from 15 votes)
How to Import Reports from MS Access to SSRS, 10.0 out of 10 based on 14 ratings

8 COMMENTS

  1. Can you recommend any good on-line virtual learning labs for SSRS?
    Is there someplace to log onto a virtual server to take training for SSRS?
    Thanks for a great article. Look forward to more.

Comments are closed.