Microsoft Excel as a Data Source

IMPORTANT: Due to the removal of ODBC functionality in Java 1.8, iDashboards will be retiring support for ODBC. The following applies only to iDashboards v9.1 and earlier, or later versions which are running 32-bit Tomcat and Java 1.7.


iDashboards can connect to Microsoft Excel files using two different methods. The preferred method is to use the iDashboards Excel Autodirectory, which uploads a copy of your Excel file to the iDashboards server. iDashboards can also connect via a direct ODBC connection to an Excel file. Both methods of connections are explained in this document.

Prerequisites

Download and install the appropriate Microsoft ODBC driver for your version of Microsoft Excel. This driver should be installed on your iDashboards server.

• For Excel 2013, download and install the 32 bit Microsoft Access Database Engine 2010 Redistributable from here: https://www.microsoft.com/en-us/download/details.aspx?id=13255
• For versions of Excel prior to 2013, download and install the 32 bit Microsoft 2007 Office System Driver: Data Connectivity Components from here: https://www.microsoft.com/en-us/download/details.aspx?id=23734

iDashboards requires that at least one named range be defined in your Excel file before it may be used as a data source. A named range in an Excel file is viewed by iDashboards similar to how it views a table in a database. To create a named range in your Excel file, highlight the data that you wish to be used in iDashboards and type a name in the Name Box (circled in red in the following screenshot).

Once you have created your named range, make sure to save your file. You can modify and add additional named ranges using the Name Manager under the Formulas tab.

Creating a Data Source using the Excel Autodirectory

When a Microsoft Excel file is opened, a lock is created on the file which prevents any other users from accessing it. This locking feature can prevent dashboards connected to an Excel file from loading, and users from editing Excel files while a dashboard is loading. To prevent this, it is recommended that the iDashboards Excel Autodirectory feature be used. The Autodirectory is an easy to use upload tool in the iDashboards Admin Application that creates a copy of an Excel file and places it onto your configured Excel Autodirectory folder. By default, this folder is located in your ivizgroup home directory on your iDashboards server.

To add an Excel data source using the Autodirectory, follow these steps:

1. Log on to your iDashboards Admin Application and click on the Data Sources tab.

2. Click the Excel Autodirectory… button.

3. In the Excel Autodirectory page, click Choose File to select your Excel file that you wish to upload and then click Upload Excel File.

4. You should now see your Excel file listed under Data Sources in the User Application’s Chart Designer. The named ranges that you defined in your Excel file will be listed under Select a Table or View.

Creating a Data Source using a direct ODBC Connection

It is also possible to connect iDashboards directly to an Excel file using an ODBC connection. This method of connection is not recommended, as it will cause file locking issues as explained in the Overview. If a direct ODBC connection to your Excel file is required, follow these steps to make the connection:

1. Create a 32 bit ODBC connection to your Excel file. To create a 32 bit connection, use the following tool on your iDashboards server: C:\Windows\SysWOW64\odbcad32.exe

2. In the 32 Bit ODBC Data Source Connector, select System DSN, and then click Add.

3. Select the Microsoft Excel Driver (*.xls).

4. Browse to your Excel file to create your ODBC connection.

5. Log on to your iDashboards Admin Application and click on the Data Sources tab.

6. In the (new) dropdown menu, select Generic ODBC and click Add Data Source

7. In the Add Data Source screen, enter a Data Source Name (for reference in iDashboards only) and the DSN that matches the name you gave your ODBC connection earlier. Select Allow Custom Queries if you wish to write your own SQL queries when building your charts. Click Save. Your new data source is now available.