Defining Excel Data Sources for use with Eureka: Reporter

Products

• Cleverpath Reporter
• EUREKA:Reporter
• EUREKA:Strategy

Issue/Introduction

---

Summary

These instructions will take you through, step by step, to create a data source that can be used with Reporter to run reports against an Excel spreadsheet.

 

Environment

Release:
Component: PORTAL

Resolution

Detail

First, open your Excel spreadsheet. Make sure the spreadsheet contains the column names in the first line of data. Select the entire area on the spreadsheet that contains your data by clicking on the "A1" cell to anchor the range and highlight the desired data area. (It is possible to have multiple tables on one excel worksheet.)

HINT: If there is only ONE range on the spreadsheet, place your cursor in the first cell of the range (generally, cell A1) and press CTRL+SHIFT+END at the same time to highlight the entire spreadsheet where data is contiguous

 

 

Click Insert/Name/Define to give the selected table a name.

This will bring up the dialog that allows you to create a table name.
*NOTE: The range you defined should be displayed in the Refers To: field
Type the table name in all capital letters and click ADD then OK.
MAKE SURE THE NAME IS IN ALL CAPS. THE METADATA MANAGER WILL NOT BE ABLE TO IMPORT LOWER CASE NAMES.

 

 

Make sure to save your spreadsheet after you have given your table a name.

Next, we will need to create a data source. Go to Start/Settings/Control Panel/ODBC Administrator. Click the tab for system data sources and click Add. This will give you a list of available database drivers.

 

 

Select the Microsoft Excel Driver and click Finish. This will give you a dialog for the Microsoft Excel Driver:

 

 

Give your data source a name and a description. Make sure to fill in the correct Version of Excel you are using. Click on the button to Select Workbook.

 

 

Browse to your spreadsheet and select it. Click OK. You have now created a data source for your excel spreadsheet.

Next we will need to create a Metadata file for Reporter to use to report against your spreadsheet. Go to Start/Programs and to your Reporter group. Select "Reporter Metadata Manger".
Once you are in the Metadata Manager select Edit/Options. This will bring up the following dialog:

 

Verify that "Load Full Column Definitions" is not checked. Click OK.

Next we will choose File/New to create a new Metadata file. You may modify the business view name, but leave the rest of this screen the way it is by default:

 

Click the Load button. You will then be prompted for your data source information. Select Machine Data Sources and then find the Data source we created above:

 

 

This will take you to a screen of available tables in your excel spreadsheet. You should see the table name we defined above under Selected Tables. Notice that by default, tables are selected to be loaded.

<Please see attached file for image>

Click OK. You will now see the Metadata Manager define columns and then display a message that says Business View Loaded Successfully. Save your Metadata file and close the Metadata Manager. The final step is launching Reporter to create your report from this new Metadata file. Go to Start/Programs and to your Reporter Group. Select the Report Administrator. With a new, blank, freeform report open, go to Query/Database Connection Override. Select your data source and browse to your new Metadata file that you created in the last step:

 

 

Now you should be able to drag the fields from your metadata onto your freeform report: