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.