ODC creation is a valuable tool in Excel. It is a function that is wizard based and has some extensible features such as inputting SQL Query’s to get specific data sets within the Database. This document will explain how to properly create and store ODC files to build Excel Pivot Tables and Charts against Data Sets.

ODC Files in Excel

The below is a step by step ODC creation guide with Screen Captures. If there are any questions please contact the Author of this Document. This example is Oracle DB related.

After Opening Excel 2007, click the Data Tab on the Ribbon:

Figure 1 Data Tab

Once the data tab has been opened click on the “From other Sources” button:

Figure 2 From Other Sources

Select the Data Connection Wizard which opens a wizard for connecting to the data sources not listed to the right of the “From Other Sources” button or on the Other Data Sources Drop down menu.

Figure 3 From Other Sources Wizard selection

Select the Microsoft Oracle Connection property and click next:

Fill in the necessary information including the server name and the Oracle log-in information. NOTE Windows Authentication is not possible when connecting to Oracle.

Figure 4 Server Name and Login information

Since we will be specifying our own SQL Query table selection input, uncheck Connect to a Specific Table and click next:

Figure 5 Do Not Connect to a Specific Table

Setting the Connection Properties is next ensure Always use this file to refresh data (Blue), add your SSO ID to the Authentication Settings (Yellow) and Check Save Password in File (Green), you may also change the name of the file and its Friendly name (Red).

Figure 6 Connection Properties

Figure 7 Authentication Settings

Click Finish and Select the type of Table you would like to build:

Figure 8 Insert Pivot Table

Once the Pivot Table is created go back to the data tab and click Connections button:

Figure 9 Connections

Click Properties:

Check the properties for Refresh (Enable background Refresh amd Refresh every 60 Minutes) under the Usage Tab:

Figure 10 Refresh Settings

Then click the Definition Tab (Blue) and select the following options (Always Use connection File, Save Password, Enter your SQL Query, Check to ensure your SSO ID is set in Authentication Settings)

Figure 11 Definition Settings

Then Click Export and Browse to the Data Connection Library you will be storing the Connections in:

Figure 12 Export the Connection File

Now Close Excel and Reopen it, we will now select the newly saved ODC file from SharePoint and re-create our Pivot Table. For whatever reason through the Wizard, the connection properties do not save properly.

Click Data Tab and Connections:

Figure 13 Connections

Click Add

Figure 14 Add

Click Browse for More

Figure 15 Browse

Select the appropriate Connection File:

Figure 16 Select Connection

Once the connection is selected go back to the Data Tab and click Existing Connections, the click on the Connection and select your Table display of choice. NOTE: Pivot Tables and Charts work in Excel Services, DATA TABLES DO NOT.

Select Existing Connections

Figure 17 Existing

Select the Active Connection (The one you just pulled from SharePoint)

Figure 18 Active Connection

Then select the table function you wish to display.

Figure 19 Pivot Table

Configure your Pivot Table.