Create SSRS report using Excel Data Source Step by Step

By creating a report using Excel as a Data Source we will follow the following steps ...

Right click on Shared Data Source folder and choose Add New Data Source. A new popup screen will open. Give the Data Source Name and Choose ODBC from the dropdown box.

Then Click on Edit button. A new screen will open. From the new screen i.e. Connection Properties go to theUse user or system data source name and from the drop down choose Excel Files .

Then Choose the Use Connection String and click on Build button. A new pop up screen will open . Click on New button.

Then click on Next button

Choose the Microsoft Excel Driver and click on Next button and then click on Finish button.

Then click on Select Workbook button and choose the file and click on OK button.

Click on OK.

Test the connection and press OK.

Click on OK.

Click on Reports folder and choose add new items and choose Report. Map your Data Source and click OK.

Right click on Dataset Folder and Write the query for the table. (I have used a very simple query .For ex.)

This is Your Report from Excel data source.

1.     Background

The purpose of this article is to describe how to create a report using Excel as a data source for the report. We are going to use ODBC provider for excel to get the data for the reports.

2.     Step by Step procedure to create Excel as Data source for Report:

·         Open the Reporting services solution in BIDS/SQL Server Data Tools and

·         Go to Shared Data Sources folder then Right click and click on Add New Dataset

·         It will start Data Source Wizard, give the name of Data Source , give Type as ODBC and Click on Edit to create connection string for the Excel:

·         Once you click on Edit, It will open Connection property Window, select use connection string and click onBuild.

·         Go to File Data Source and go to the location of the source excel file by using browse button in Look in:

·         Click ok. It will create the connection string for the excel

·         Click ok and your dataset for excel source has been created.

·         In the credential window, select the credential type as per your requirement. Here I have selected Windows Integrity security:

·         Now right click on Shared Dataset and click on New Dataset:

·         Give the name of Dataset and select Data source as ExcelDataSource that we have just created. Write the query for the excel:

Note : Check the sheet name in the excel before writing queries

3.     Steps to create report

·         Now create a report and map this newly created dataset “ExcelDataSet” to the report.

·         Add a tablix to the report layout and map the fields from the attached dataset.

·         Do formatting as per your requirement and run the report. Below is the resultant report:

4.     Conclusion
By using the above steps, we can create SSRS report using excel as data source

----------------------------------------------------End of Article---------------------------------------------------

Aim :- This article will aim at creating a SSRS report with Excel as source.

Description :- Hello members, in our previous articles on SSRS (one of the best reporting tool), we have shared with you that we can take SQL SERVER as a Source. But now we will go with one more approach i.e. we will create a SSRS report with Excel as source. I found this as a cool idea so thought of sharing with you. Isn’t it cool?

Recently, I experienced a new thing while creating a report with crystal reports. In this, I was frequently using ODBC connections to connect to different sources like (SQL Server, Oracle, Excel, etc.). So one fine day I got one thought that why can’t I create ODBC connections in SSRS? Then I started my search and found a way to create ODBC connections in SSRS. This makes me happy as finally I succeed with my search. I found a simple way to create ODBC connections to connect with the Excel. Moreover, we can also use this with different sources.

In SSRS to connect to different kind of sources, we can use specific providers for those sources. We can use Microsoft Sql server to connect to the SQL Server and Oracle provider to connect Oracle server and many more. We have too many providers in SSRS for the connection. So, ODBC is also one of the providers present in SSRS. I know I am writing very technically. Before you get irritated, I am going to explain you what I am talking.

Question :- What is ODBC?

Answer :- ODBC can be abbreviated like this (“Open Data Base Connectivity”). The name itself indicates that it is an open database provider to connect to any Database source. Let’s see what “Google” says – An ODBC driver uses the Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data. This might helped you in clarifying your doubts.

This completes our Theoretical part. So now I can jump into my favorite section – Practical section.

Step 1 – Excel Spread sheet as a Source.

Take any excel sheet or you can create one new sample Excel spread sheet with few rows.

This excel sheet will act as source for my report.

I am inserting 2 columns named id and name in the given new excel sheet.

Now, I am inserting some data in the excel cells under both the columns.

Step 2 – Naming a new SSRS report in BIDS

• Open Bids (Business intelligence development studio).
• Create new SSRS project , in my case I created my project name as “Phpring-SSRS”.
• Create one new report name as “Connecting to excel”.
• Once you done with all the above 3 steps the window will looks like below.

Step 3 – Creating Data source for the SSRS report

In this step I’m going to show you how to create a connection string with excel by taking provider as “ODBC”.

• Goto Report data pane on the left side and right click on the “Data sources” and click on add “new data source”.
• Once you come up with the above step the window will looks like below .

• In the above window click on “Type” under embedded connection then click on “ODBC”.
• Once you complete to choose with ODBC the window will looks like below. Here you can click on “Edit”.
• Once you click on edit it will bring up below window.

• Here you can “check connection string” then click on “build”.
• Once you click on “build “ it will bring up the below window. Here you can choose
  “Machine Data source”. If we complete to choose with machine data source the window will looks like below.

• In the above window you can click on “New” to create a new connection.
• Once you click on New you may get the below error message, don’t worry about that simply click on OK.

• If we come up with the above warning message we can get the below wizard.

• Don’t change anything in the wizard , by default the first option is select we can also move ahead with that option only . Simply click on “Next”.
• Once you click on next , we will get a wizard like below , here we can choose our required driver . We can see plenty of drivers available in the below wizard , but we can choose “Microsoft Excel Driver” move ahead by clicking on next. Please follow below picture if you get any doubt in the meanwhile .

• Click on next then click on finish.
• Once we done with 13 step , we can get a new wizard. it looks like below. Give data source name and description as “Excel-sample”. Please take a look at below screen shot if you have any doubt.

• Click on “Select workbook” to choose our excel source. It will bring up a new wizard like below.

• Double click on C:\ then drag the vertical bar down the click on Users.
• Here I have chosen my user name and I can goto my desktop to select my excel source. In this case I have saved my excel source on Desktop . so simply I can go and choose my excel source. If anybody is saving in different you can click on “Drives” then choose your correct location.
• I have taken my excel file , it names with sample. Xlsx. Then finally we can click OK for four times to jump into the below wizard. In the below wizard we can ago ahead and check our connection, whether it is working or not. So click on Test connection.

• Observing the above pic we can say that our connection is tested successfully then we can able to connect to that excel source successfully without having any issues. Finally click on OK for three times to come finish up the data source.

Step 4 – Creating Data set for the SSRS report.

• Again goto report data pane, right click on dataset click on add dataset.

• Choose datasource that what we created just a moment ago and write a simple select query like above. In that query sheet1$ is my sheet name it contains the data of employees. You must enclose the sheet name in brackets and give $ “dolor” symbol after the sheet name. Finally click on ok to finish up the data source.

Step 5 – Report creation using SSRS in BIDS

• Its time to create the report with the data that what we have in the excel spread sheet. Goto toolbox pane and drag and drop the table and take the column names. So finally the report design looks like below.

• Click on Preview to view the data.

• Observing the above picture we can concluded that our connection worked perfectly and also our query was ran safely . Finally we can view our data in the report.

With this we complete our article on Create SSRS report with Excel as Source. That’s why i mentioned above that SSRS is a handy and market demanding reporting tool. I hope you enjoyed this post. Your Comments and Suggestions are always welcome as comments below.