Click on Start on Your Menu Bar

USING EXCEL AS A DATA SOURCE FOR BRIO

(retrieved from the Purdue website)

There are times when it would be nice to use an Excel spreadsheet as a data source for Brio. You can, of course, import data from Excel into Brio, but that would have to be done every time a change is made to your spreadsheet. An alternatiive method is to just query against the actual spreadsheet.

The first step is to set up named ranges in your Excel spreadsheet. Each named range will be translated into a table by Brio. To set up named ranges, open your Excel spreadsheet.

Highlight the cells you want to be a table in Brio. Make sure you have column titles.

From the Insert drop down menu, select Name, then Define.

Excel will show the first column title as a default name.

Type in what you would like to name the range. Click OK.

Save your workbook. You now have a named range.

Now you need to set up an ODBC (Open DataBase Connection)driver to allow Brio to connect to your spreadsheet.

Click on Start on your menu bar. Go to Settings – Control Bar.

Double click on Data Sources (ODBC)

Make sure you are on the User DSN tab. (SK Note: -- I’ve used the System DSN tab and it worked fine). You will need to click Add. Even if you see Excel, you will want to set up (Add) a connection for each different spreadsheet you want to run Brio against.

Clicking on Add brings up the following screen. Scroll down until you see the driver for the data you want to connect to. Click Finish.

Up to now you’ve been telling the computer how the data is stored (as an Excel file). Now you need to tell it where to find the data. The Data Source Name and Description are for your benefit. Put in what will help you identify the data. Then select your version of Excel from the drop down box. Then Click on the Select Workbook button.

Change drives and directories until you locate the worksheet you want to link to. Highlight it and then click OK.

Click OK again.

You’re now back to the first ODBC screen. You can click OK.

Close the control panel screen and start Brio.

In Brio, you will need to create a new connection file (OCE file) for the Excel file for which you set up the ODBC driver. So when Brio opens, click on New Database Connection File (or go to Tools, Connection, Create).

Use the drop down boxes to choose ODBC as both the connection software and the type of data base. Click Next.

Choose the data source name you entered in the ODBC setup from the Host dropdown box.

Click Next.

Click finish.

Click Yes to save the OCE you just created.

Save the OCE with a name you can relate back to the data. Click Save.

Brio should now open.

If you click on the plus sign (+) next to Tables or hit the F9 key, the list of available tables should appear. Remember, in Excel these need to be named ranges.

C:\DOCUME~1\SHERRY\LOCALS~1\TEMP\exclodbc.doc10/16/181 of 10