SSRS: Building a report on an excel datasource with Reportbuilder 2.0

Introduction
Today a small tutorial about using an excel file as a source for a report with reportbuilder 2.0.The following steps should be followed when you're creating a report based upon an excel file.

1) Create an excelsheet with som testdata.
2) Create an ODBC DSN to access the excel file.
3) Create a datasource in reportbuilder.
4) Build a dataset and a report.

1) Create an excelsheet with some testdata
First, create a simple excel sheet with data with Excel 2007. We will build a report based on this data. Save this file under the name ImportantInformation.xlsx.

2) Create an ODBC DSN to access the excel file
As the title of the post suggested we are gonbe build a report based on an Excel sheet based on an ODBC connection. The details of how to do is listed here:

Click on Start, All Programs, Control Panel, Administrative Tools, Data Sources (ODBC). If the tabbed page is not in User DSN, change the tab to User DSN and click on the Add button.

3. Scroll down and highlight Microsoft Excel Driver (*.xlsx) and click on the Finish button.

This opens the ODBC Microsoft Excel Setup window as shown.

 Press on "Select Werkbook" and select the Excel sheet

 Press Ok and the excel file will appear in the User DSN screen

Click on OK and the window closes.

3) Create a datasource in reportbuilder
The ODBC connection has been created in the last step and now it should be possible to create a datasource in reportbuilder and connect it to the ODBC connection. Lets create a report based on this ODBC. Choose New Data source.

Set the properties Name (i didn't) and choose a connection embedded in my report. Press Build in order to create the connection string.

Choose "Use connection string" and press build (again(?)) and select the ImportantInformation.xlsx

Press OK and the Select Workbook will come up. Locate the excel sheet (i've stored mine on E:\Excel)

Press ok and Press "test connection"

And the connection string is build

The connection string is as follows:

Dsn=ImportantInformation.xlsx;dbq=E:\Excel\ImportantInformation.xlsx;defaultdir=E:\Excel;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

Click on Ok en we're entering the next universe.

4) Build a dataset and the report
The  next step is create a simple report based on the data in the Excel sheet. Select "Table or Matrix".

The following window appears and you're asked for selecting the right datasource. In our case only one is created and we can't choose the wrong one here.

The next step is the most important one. Took me some trails before i got it right. Type SELECT * FROM [Sheet1$] (if you named the tab "Sheet1"). Press ! and the query is executed.

The wizard continues...

 Put some fields in the details section and Column1 in the row groups. I deselected the sum.

Choose appropriate layout.

Choose appropriate style

Press finish

Conclusion
Easy to do and this is enabling power users building reports on Excel data. Opening and closing the excel file when building the report result also in error messages in Report builder 2.0 (Something like connection lost with the excel file). This is an error i recieved when i tried to open the excel file when Reportbuilder is using the excel file:

Greetz,
Hennie