Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

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

Print | posted on Friday, August 22, 2008 4:46 PM | Filed Under [ Reporting Services ]

Feedback

Gravatar

# 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????
9/26/2008 2:59 PM | Virgilio
Gravatar

# 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
9/29/2008 7:16 AM | Joe Webb
Gravatar

# 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
9/29/2008 7:19 AM | Joe Webb
Gravatar

# 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
10/8/2008 2:29 AM | Theunis
Gravatar

# 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
10/27/2008 3:20 PM | Michael
Gravatar

# re: Using Excel as a Reporting Services datasource

Why not use filters?
10/28/2008 8:42 AM | Joe Webb
Gravatar

# 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.
10/28/2008 10:09 AM | Michael
Gravatar

# 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.
10/28/2008 10:24 AM | Joe Webb
Gravatar

# 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!
10/28/2008 1:11 PM | Michael
Gravatar

# 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
2/19/2009 4:59 AM | Rajasekaran
Gravatar

# 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
4/17/2009 9:18 PM | Raj
Gravatar

# 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?
5/27/2009 4:24 PM | Jane Woodhouse
Gravatar

# re: Using Excel as a Reporting Services datasource

Hey,

u can use

SELECT *

FROM [Sheet1$] column1 <>""
7/10/2009 4:14 AM | ram
Gravatar

# re: Using Excel as a Reporting Services datasource

sorry i missed where clause

SELECT *

FROM [Sheet1$] where column1 <>""
7/10/2009 4:16 AM | ram
Gravatar

# Windward Reports Excels at this!....Get it? :)

Excel as a datasource is necessary for any reporting software in my opinion, not so much because large, complex sets of data should be stored in Excel but because they often are.

Having an Excel file delivered to you, and then having to transform it into XML is not only tedious but time consuming. Don't settle for this process by using Windward Reports which operates in Microsoft Word or Excel and can properly connect, implement and interpret data stored in an Excel format.
7/27/2009 6:19 PM | GrahamS
Gravatar

# 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)
2/12/2010 5:12 AM | Navin
Gravatar

# 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 <>?
2/12/2010 6:40 AM | Navin
Gravatar

# 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?
2/23/2010 4:23 PM | Venkat
Gravatar

# 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?
3/24/2010 10:16 PM | Simon
Gravatar

# 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?
4/21/2010 8:59 PM | Chloe
Gravatar

# 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
6/16/2010 12:25 AM | geld
Gravatar

# re: Using Excel as a Reporting Services datasource

Thanks Kim and Debbie for the question and for providing the fodder for this post.
10/15/2010 12:49 AM | columbia jackets
Gravatar

# re: Using Excel as a Reporting Services datasource

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.
10/15/2010 12:50 AM | snow boots for women
Gravatar

# re: Using Excel as a Reporting Services datasource

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.
furry boots | womens snow boots | columbia outlet | columbia sportswear jackets | the north face jackets | north face jacket | cheap mac makeup | discount makeup
10/19/2010 4:19 AM | fur boots
Gravatar

# 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.
10/21/2010 4:57 PM | dataman777
Gravatar

# re: Using Excel as a Reporting Services datasource

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free video converter for Mac, This video converter for Mac is m
10/26/2010 1:28 AM | hanly
Gravatar

# 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.
11/23/2010 3:19 PM | Emtucifor
Gravatar

# 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.
12/20/2010 6:52 AM | marimuthu
Gravatar

# 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
1/6/2011 11:43 AM | George
Gravatar

# re: Using Excel as a Reporting Services datasource

nvm...just took a while to update
1/6/2011 12:35 PM | George
Gravatar

# 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...
2/5/2011 10:42 AM | Suresh
Gravatar

# re: Using Excel as a Reporting Services datasource

Sorry, missed $ in my query

SELECT *
FROM [ExcelDS$]

is my query, please help
2/5/2011 10:45 AM | Suresh
Gravatar

# 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

3/22/2011 5:28 AM | Prasad
Gravatar

# 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!
4/19/2011 1:29 AM | Abhi
Gravatar

# re: Using Excel as a Reporting Services datasource

Hi,
Can anyone help on converting a numeric values present on Column1 as string..
6/21/2011 5:28 AM | Lakks
Gravatar

# re: Using Excel as a Reporting Services datasource

Very happy to see your article I very much to like and agree with your point of view
8/2/2011 12:41 AM | registryspeeder
Gravatar

# re: Using Excel as a Reporting Services datasource

I've used Excel as a data source before, and it works great. A friend of mine who is an attorney has told me that they use Excel as a storage query tool for ediscovery and it works great. I think many times people underestimate the power of Excel.
9/23/2011 2:00 PM | Kenny
Gravatar

# 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.
10/14/2011 10:40 AM | Robert
Gravatar

# re: Using Excel as a Reporting Services datasource

Make sure that you have unchecked Read-Only Option while configuring excel work book....

12/16/2011 4:52 AM | John
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET