Using Excel as a Data Source for Reports
Question: I have added an Excel file as a data connection and tested the connection successfully. The excel file has 4 columns of data which I was instructed to create as “Named Fields”. When I go to add a container, type = table, I see all 4 fields and they are each listed as “Table” under the type column. If I select them all, each are defined as individual tables. I’m trying to create 1 table with all fields to add as an expression for my report.
Answer: The process you are using is correct but you need to Name the Data Range in Excel rather than name the fields in Excel. All the fields will then be available in 1 table for reporting.
How: In order to use an existing Excel Workbook as a data source for a report, the data needs to be organized into named ranges.
Excel Data Source
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, Name Manager
5. Select New
6. Give the data a name, and select the data you are naming
7. You have now given the data a Named Range
8. This range will now be available for selection when you add new data containers within the Administrator module
Creating a new connection to the Excel Workbook
To add a new data connection to an Excel workbook, you will need to ensure that you have selected the applicable data in Excel and have named the range prior to adding the connection within the Administrator tool.
- Open the Administrator
- Select the ODBC driver for Excel
- Click on the Add Icon which will display the Connection Info window
Connection Name – give the connection a name. i.e. Sales
Excel Workbook: Browse to the Excel Workbook
- Name the connection and specify the Excel workbook that you will be accessing. If the workbook has been protected, you will need to add the relevant user id and correct password
- Click Add
6. Now add the data container
7. Select Table
8. The option to select the table, (the data that you named in Excel workbook- DATA), will come up. Select ok
9. If you sample the container you will see all the data from your Excel workbook is available to report on
10. You can now use this container and create reports off it in the report manager
Enjoyed this Sage Intelligence Tip?
Join over 3,000+ subscribers in receiving Sage Intelligence tips and tricks in your inbox twice a month and to get access to all our Sage Intelligence Tips and Tricks e-books.