Using an Excel Workbook as a Data Source

From Sage Intelligence Knowledgebase
Jump to: navigation, search

In order to use an existing Excel Workbook as a data source for a report, the data needs to be organised into named ranges.

Naming the Data Ranges

1.Open the workbook in Excel.

2.Make sure that the data is stored with accurate headings so that when expressions are added, the data remains meaningful.

3.Select the data required for report writing purposes by highlighting it.

4.Select Formulas, Define Names

Using an Excel Workbook as a Data Source

5. Enter a name for the data range 6. Select OK 7. This data range will now be available for selection when you add new data containers within the Administrator module.

Connecting to the Excel Data

A new connection type has been added to the Administrator tool for connecting to Excel workbooks as source data via an ODBC connection.

To add a new data connection to an Excel workbook you will need to ensure that you have a selected the applicable data in Excel and have named the range prior to adding the connection within the Administrator tool.

1.Select the ODBC driver for Excel Connection and right click

2.Select Add Connection

3.Enter a Connection a name

4.Enter the location of the Excel file

5.Once you have created the connection to the data, right click on the connection and select Add Container

6.In this window you will see each column as well as the Named Data Range

Using an Excel Workbook as a Data Source