InfoAccess - Using Excel as a Data Source for Interactive Reporting

Data can be imported from Excel into Interactive Reporting (Hyperion), but that would have to be done every time a change is made to your spreadsheet. An alternative method is to just query against the actual spreadsheet.

Note: Interactive Reporting, including both the IR Workspace (web) and the Studio Desktop client, will be decommissioned on March 1st, 2019. Neither the web nor the desktop software will be available or supported after this date.

UW-Madison affiliates can find more information on alternative tools on the Office of Data Management and Analytics Services website.

Other UW System affiliates can find additional information, as well as a list of support contacts, on the UWBI Support website.

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

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

From the Insert drop down menu, select Name, then Define. Note: In new versions of Excel, this option can be found in the Formulas ribbon under Define Name.

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 Hyperion 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 but System DSN tab may work as well. 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 Interactive Reporting 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 Interactive Reporting.

In Hyperion, 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 Hyperion 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 drop-down box. Click Next.

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.

Interactive Reporting 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.

Taken from the Purdue website

Keywords:Excel data source connection Hyperion   Doc ID:7587
Owner:KAY S.Group:DoIT Help Desk
Created:2008-03-23 19:00 CDTUpdated:2018-12-06 16:11 CDT
Sites:DoIT Help Desk
Feedback:  17   1