Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

Using Excel as a Reporting Services datasource

While teaching a Microsoft SQL Server Reporting Services class in Charlotte, North Carolina, this week, one of the students asked to see an example of a report that uses a Microsoft Excel Spreadsheet as its datasource. Here are some screenshots from the demonstration.

The first step in using an Excel spreadsheet as a datasource is to create a System Data Source Name (DSN) for the spreadsheet. Open the ODBC Data Source Administrator from Control Panel | Administrative Tools.

SSRS_Excel1-2008-08-21

Add a new System DSN, providing a name in the Data Source Name box, and selecting the workbook that you wish to use for your report.

SSRS_Excel2-2008-08-21

Next, from within Business Intelligence Developers Studio (BIDS), create a new data source. Change the Type to ODBC. Click the Edit… button and select the DSN that you just created in the prior steps.

SSRS_Excel4-2008-08-21

To use the data from the spreadsheed in a report, create a new Dataset. For the query, type

SELECT *

FROM [Sheet1$]

Note that you can replace Sheet1 with the actual name of the worksheet tab in the Excel Workbook. The syntax requires that the name of the worksheet be followed by a dollar sign ($). As such, you must enclose the worksheet name in square brackets [] as shown below since a dollar sign is not considered a valid character in a select statement.

SSRS_Excel5-2008-08-21

Once we've created the Dataset, we can display it in a report like any other Dataset.

SSRS_Excel6-2008-08-21

Thanks Kim and Debbie for the question and for providing the fodder for this post.

Cheers!

Joe

kick it on DotNetKicks.com

Legacy Comments


Virgilio
2008-09-26
re: Using Excel as a Reporting Services datasource
Hello,
Is there any way that we can use an images as a data source.
This is the scenarion.
Daily I'm emailed a report that contains several charts.
I have a SSRS report deployed and I would like to create a lable that redirects to this image's path \\myserver\e$\myfolder\image
So next day I can overrite the image(updating it with new report) in that path and the SSRS report will refresh the content.

Am I crazy????

Joe Webb
2008-09-29
re: Using Excel as a Reporting Services datasource
Images are really just pictures and don't have datasets from which to draw data. So, no, you cannot use images as a dataset.

On the otherhand, if you have access to the underlying datasat that made up the images, then sure.

HTH...

Joe

Joe Webb
2008-09-29
re: Using Excel as a Reporting Services datasource
Images are binary representations of the data. But unfortunately, they don't contain the data itself. So you cannot use an image as a datasource for your dataset.

If you have access to the original information, you can use.

HTH...

Joe

Theunis
2008-10-08
re: Using Excel as a Reporting Services datasource
Can one extract an image from an Excel document that is set up as a data source? In other words can I display in my report an image that is contained in the excel document?

Thanks

Michael
2008-10-27
re: Using Excel as a Reporting Services datasource
I've been given the task of automating some forms with SSRS 2005 with data from an excel document. I have the data populating just fine, i have my dataset working, everything seems to be good. My problem is that i cannot figure out how to get parameters to work. I need to be able to set a start date and end date when the forms are run. It keeps telling me that i have too few parameters which means i'm calling the data wrong. What is the syntax for setting up parameters in the TSQL?

Thanks

Joe Webb
2008-10-28
re: Using Excel as a Reporting Services datasource
Why not use filters?

Michael
2008-10-28
re: Using Excel as a Reporting Services datasource
The form will be posted to our reporting server and the people who will actually be using it want to be able to set date ranges for the print outs, that way they aren't printing every record out.

Joe Webb
2008-10-28
re: Using Excel as a Reporting Services datasource
Right, you can create a dataset based on the entire spreadsheet. Then use report parameters to prompt the user for what they'd like to see. And finally use a filter on the data region to limit the output based on their preferences. That would be more efficient.

Michael
2008-10-28
re: Using Excel as a Reporting Services datasource
Ah right right, i was initially trying to filter from my dataset but i can definatly do it afterwards. Thanks!

Rajasekaran
2009-02-19
re: Using Excel as a Reporting Services datasource
Hi I did the above mentioned steps to create datasource connect to excel. But its not happending
My test connection is BIDS level are success but i could able to get the data from the sheet as well.

Please provide some way to connect it down

Raj
2009-04-17
re: Using Excel as a Reporting Services datasource
Hi all,

I am planning to use SQL reporting 2005 with MS excel 2007 as a datasource. Could you please let me know I should to use ODBC or OLE DB?. If anyone can give config details that will be a great help. Also once connected, can I use

SELECT *

FROM [Sheet1$]

command to get the data from excel ?. Please give me a reply. Thanks

Jane Woodhouse
2009-05-27
re: Using Excel as a Reporting Services datasource
I want to filter data from spreadsheet with WHERE in Select Query. I can select * from [spreadsheetname$] but want to add WHERE 'spreadsheet cell' <> 'blank' or empty. Cannot get the syntax correct. Any help?

ram
2009-07-10
re: Using Excel as a Reporting Services datasource
Hey,

u can use

SELECT *

FROM [Sheet1$] column1 <>""

ram
2009-07-10
re: Using Excel as a Reporting Services datasource
sorry i missed where clause

SELECT *

FROM [Sheet1$] where column1 <>""

Navin
2010-02-12
re: Using Excel as a Reporting Services datasource

Getting error on using the query

SELECT *

FROM [Sheet1$] where column1 <>@Column1

An error occurred while executing the query.
ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1. (Microsoft Report Designer)

===================================

ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1. (odbcjt32.dll)

Navin
2010-02-12
re: Using Excel as a Reporting Services datasource
its called unnamed parameter use questionmark for paramter . I am not sure on multi value as its not owrking when i place it with in.

FROM [Sheet1$] where column1 <>?

Venkat
2010-02-23
re: Using Excel as a Reporting Services datasource
Is it possible to JOIN on the data retrieved from the Excel Source with the data existing in the SQL Database and produce a report? Or is this feature not available?

Simon
2010-03-24
re: Using Excel as a Reporting Services datasource
multi-parameter in a dataset is ok, but all parameter names become questionmark.
I would like to ask do you know how to

1) alias the parameter name
2) is seems IN clause can not work in this case, does anyone know how to use IN clause?
3) can we join several worksheets in a Excel, just like joining different table in database?

Chloe
2010-04-21
re: Using Excel as a Reporting Services datasource
I have locking issues on the source excel sheet, and I need to be able to update the data and for the RS report to refresh. Is there any way of getting round this?

geld
2010-06-16
re: Using Excel as a Reporting Services datasource
You can use graphic reporting instead of images but there is no way to put images with vba

dataman777
2010-10-21
re: Using Excel as a Reporting Services datasource
This works fine from the BIDS console or on a single machine but not on a server running SSRS using shared datasources to connect to a backend database on another server. Any insite on creating a shared ODBC connection inside report manager data source folder on the SSRS server?

I can create a ODBC connection logged on the SSRS Windows 2008 64-bit server from adninistrative tools by changing the target to sysWOW64 as described above but from the Report Manager Datasource folder create new datasource I recieve this error:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I also tried embedding the ODBC datasource inside the .rdl to no avail.

Emtucifor
2010-11-23
No DSN needed
Folks,

You can do this without a DSN and ODBC. In fact, you don't even need to create a Data Source.

Add a Data Flow Task to the package. In the Data Flow Task, add an Excel Data Source. Poof, you're reading from Excel.

marimuthu
2010-12-20
re: Using Excel as a Reporting Services datasource
HI,

I want to read the multiple sheet data in excel sheet, its posible.
But one thing , each sheet have diffrent database fields.

George
2011-01-06
re: Using Excel as a Reporting Services datasource
Hi,
I am using Excel as an ODBC datasource for a SSRS report. However, when i add or edit data to the excel worksheet. the data is not updating in SSRS? Do i need to re-create the Datasource and ODBC every time i edit anything in the worksheet?
Thanks

George
2011-01-06
re: Using Excel as a Reporting Services datasource
nvm...just took a while to update

Suresh
2011-02-05
re: Using Excel as a Reporting Services datasource
Hi,
I created system DSN and called dat from BIDS.
But, in the query designer, I am getting the error.


Error in FROM clause: near '['.
Unable to parse query text.

SELECT *
FROM [ExcelDS]

is my query whereas ExcelDS is the my sheet name.
Do I need to give path name also? I already gave path at workbook path while creating DSN.

Reply at the earliest please...

Suresh
2011-02-05
re: Using Excel as a Reporting Services datasource
Sorry, missed $ in my query

SELECT *
FROM [ExcelDS$]

is my query, please help

Prasad
2011-03-22
re: Using Excel as a Reporting Services datasource
EError in FROM clause: near '['.
Unable to parse query text.

SELECT *
FROM [Sheet1$]

How to solve this problem


Abhi
2011-04-19
re: Using Excel as a Reporting Services datasource
Need some help.



a. When using Excel 2007 as data base for SSRS report I am able to generate and run SSRS report properly on Microsoft Visual Studio (on my pc)

b. But when I deploy the report and data source on SSRS report server it asks for DSN name (here I get stuck)

i. If I give full dsn path it says DSN name too long (dsn also on server in same folder)

ii. Doesn't recognize the file if no address is given (even though dsn and datasource file are in same folder on report server)

iii. If I remove DSN it says “Cant find datasource name and default driver not specified” (without DSN in connection string it is working fine on my PC)



Thanks in Advance!

Lakks
2011-06-21
re: Using Excel as a Reporting Services datasource
Hi,
Can anyone help on converting a numeric values present on Column1 as string..

Robert
2011-10-14
re: Using Excel as a Reporting Services datasource
Is there a way to use Excel to drive an SSRS 2008 R2 report. The idea is that the SSRS report takes a parameter. I would like to loop through an Excel Spreadsheet column, pick up the value and call the SSRS report passing the parameter. I also want to render the report as a PDF and place it in a specific folder on my local or shared drive. Is this possible and are there any examples of doing this?
Thanks, appreciater your blog.

John
2011-12-16
re: Using Excel as a Reporting Services datasource
Make sure that you have unchecked Read-Only Option while configuring excel work book....