TN 037: How to Look up Data from an Excel Spreadsheet
Dream Report includes a direct driver to process data in an Excel spreadsheet, which can then be reported on in a Dream Report report. However, there are times when you might want to query specific data directly out of an Excel spreadsheet and use the result in a report – for example, if the spreadsheet includes a “lookup table” you need to reference. This document describes the steps to connect to, and query, an Excel spreadsheet using ODBC (open database connectivity.)
1. Configure an ODBC Data Source Name (DSN) to the Excel Spreadsheet
a. From Windows Administrative Tools, open ODBC Data Sources (32-bit)
b. Add a new System DSN, using the Microsoft Excel Driver:
c. Name the Data Source, and click “Select Workbook…” to select your Excel file:
d. Click “OK” and close the ODBC Data Source Administrator (32-bit).
2. Create a Data Source Name (DSN) in Dream Report Studio
a. From the Tools menu in Dream Report Studio, open DSN Manager
b. Create a new DSN, using the Connection Type “ODBC”, and in the Configuration section, select the ODBC data source name you created in section 1. above:
c. “Test” the connection, and if successful “Add” this DSN configuration to the list.
d. Click “OK” to close the DSN Manager.
3. Add a Reporting Object to Query the Excel Spreadsheet
a. Add a Single Data Object, SQL Query Table, or any other object that supports a SQL query to your report (this example will use a Single Data Object)
b. For the “Select Statistical Function”, choose SQL Query
c. Click “Define SQL Query”, and the SQL Query Definition window will open
d. From that window, select your Database Source (DSN) configured in section 2. above
e. Type in your SQL query to get data from the spreadsheet
f. In this example, the spreadsheet contains the following data:
The SQL query might be as follows (to look up a recipe name for specific recipe ID):
Select RecipeName from [Sheet1$] Where [RecipeID] = 'DR-333'
g. Note the format for referencing the specific sheet on the Excel workbook – the name must be followed by $
h. Format the reporting object as needed, and save and generate the report