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
- Open MS-access. Now, Click on Blank database.
- 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.
- Give a database name and path.
Follow the below screen shot :-
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.
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.
STEP 2. Import Reports from MS Access to SSRS.
- Open Business Intelligence Development Studio (BIDS).
- Create a Report project and name it as say “PHPRING-reports”.
- 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.
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.
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:-
Report of 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.
- Created one MS-Access database and named it as “Employee“.
- Within that database, created one table named “Emp” and created some sample reports by using that table data.
- Created one SSRS project.
- 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.