Using Excel As A Reporting Services Datasource

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.

Advertisements

12 Responses to Using Excel As A Reporting Services Datasource

  1. Lea Ann says:

    This is awesome Joe- I so wish I had known how to do this when I was still working. Am sending this to my former coworkers and of course to Holder!

  2. I don’t think you have enough spreadsheets taped together. I have seen them printed out on a plotter. That is when you know you have made it big time… πŸ˜‰

    Thanks for the article. It will def. come in handy.

  3. I have seen this in person. It is kind of unnerving

  4. Pingback: Dew Drop – September 16, 2010 | Alvin Ashcraft's Morning Dew

  5. Pingback: Weekly Link Post 162 « Rhonda Tipton's WebLog

  6. ABHISHEK SRIVASTAVA says:

    How to do this if report and datasource both are on server…?

  7. To believe the new economy is over is like somebody working in london in 1830 saying the entire industrial revolution is over because some textile manufacturers in Manchester went broke.
    Corporation: A nifty little device for obtaining profit without individual responsibility.

  8. Hany Zayed says:

    the challenge i’m facing here is i have a set of excel 2010 workbooks, represents years, contains multiple repeated sheets, every sheet represents a school in the area, contains a data of a different class room, and contains these fields, (student name, material1:max grade, grade, material2: max grade, grade, material3: Max grade, grade).
    currently i can query a single sheet (school) of a single year (excel file), i need to query the Sheet Names (schools in sheet) to be used in grouping and parameter…

    i’m using SSRS 2008 R2 installed on win7-64x, and excel 2010.

    can you help

  9. genesisbc says:

    Thanks for this info. here are few more reasons that i came across about the finesse of excel based reporting system: http://www.excelreports.info/excel-reports-the-hidden-possibilities-in-microsoft-excel/

  10. Yasser says:

    how can you use Parameters ? it seems that it is not working 😦

  11. prakash says:

    i have a Merged column in COL1 and 5 different values in COL2. How do i set them in SSRS report ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s