Create SSRS report with Excel as Source

12
2225

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.
Excel Spread sheet as a source in SSRS
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.

Naming a new SSRS report in BIDS

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 .

Configuring Data Source propertiec in SSRS

  • 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.

Connection Properties in SSRS

  • 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.

Select Data Source in SSRS

  • 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.

ODBC System DSN Warning in SSRS

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

Create new Data Source in SSRS

  • 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 .

Choose provider for Data Source in SSRS

  • 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.

ODBC Microsoft Excel Setuo in SSRS

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

Select Excel workbook for report creation in SSRS

  • 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.

Setting up Connection Properties in SSRS

  • 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.

Data Set properties in SSRS

  • 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.

Create Report in SSRS

  • Click on Preview to view the data.

Output of SSRS report with Excel as Source

  • 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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 8.8/10 (25 votes cast)
VN:F [1.9.22_1171]
Rating: +18 (from 22 votes)
Create SSRS report with Excel as Source, 8.8 out of 10 based on 25 ratings

12 COMMENTS

  1. Hi Avinash,
    Thanks for sharing your story with us. Really great thanks for sharing what you think and how you do as it help us to clarify doubts too.
    I was looking for some Drill through and Drill Down differences and tutorials.
    Could you please help me in this subject?
    I will really appreciate your effort.

    Thanks,
    Chander Sharma

    • Hi chander,

      This is an awesome support from you , I am really happy and have been enjoying to work with #PhpRing from long time.This journey won’t ever end, it will always go like a journey . I will keep continue this and I’m very sure i can help in Drill down and drill through reports.

      Thanks,
      Avi

  2. Hi Avinash,
    Thanks for sharing this with us.
    I tried what u said, data source creation is successful.
    But while creating the data set am getting this error
    “Could not create list of fields for the query.Verify that you can connect to the data source and that your query syntax is correct”

    Please look through it once

  3. Hi,
    Everything works as you mentioned in your article but as I try to deploy the report it can not see at manager URL
    E_msg is like :An error has occurred during report processing. (rsProcessingAborted)
    Cannot create a connection to data source ‘Excel’. (rsErrorOpeningConnection)
    ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    Pplease share how to give a path of data source to report server ?

    Thanks

Comments are closed.