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.

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.

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.

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

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

Cheers!

Joe