Using Excel As A Reporting Services Datasource
September 15, 2010 12 Comments
It’s been said that Excel is the poor man’s database. I don’t know about that, but I can tell you that an awful lot of data lives in Excel spreadsheets in corporate America and around the world. Whether it’s project timelines or employee lists, many business depend on Excel.
This is evident in my consulting practice. It’s also commonly mentioned when I’m teaching Microsoft SQL Server Reporting Services courses for clients. Students want to see an example of a report that uses a Microsoft Excel Spreadsheet as its datasource.
Dude, Where’s My Data?
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 Data Sources (ODBC) applet from Control Panel | Administrative Tools. Of course the name and location of the applet can change depending on the version of Windows you’re using.
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.
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.
Creating the Report
To use the data from the spreadsheed in a report, create a new Dataset. For the query, type
SELECT * FROM [Sheet1$] WHERE Is_Enabled = 1
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.
Once we’ve created the Dataset, we can display it in a report like any other Dataset.
And there you have it, a Reporting Services report that uses data stored in a Microsoft Excel spreadsheet as the source of its data.
Feedback
# re: Reporting Services Licensing
really, as far as I know every server that runs any service of sql server needs separate licensing… And it is very expensive to have at leat 2 physical processor server for your SSRS…
# re: Reporting Services Licensing
I could use a clarification here. Having any SQL Service run on a separate server makes sense that it would require an additional license. However, I’m under the impression that having the web components run on a different server does NOT constitute a SQL Service and therefore would NOT require a separate license.
Am I mistaken there?
Nick
# re: Reporting Services Licensing
Check this document:
http://download.microsoft.com/download/1/e/6/1e68f92c-f334-4517-b610-e4dee946ef91/2008%20SQL%20Licensing%20Overview%20final.docx
It seems like if you want to install reporting services web components (the site and the web service) on a different server where SQL Server 2008 is installed then you will need an other license.