Data Connections

Excel has built-in support for data connections so that reports can display real-time information from business applications and information systems. This support is available for workbooks that you publish to Excel Services as well. By generating reports directly from business data, you ensure that the information is up-to-date and accurate versus if the information was manually entered into a workbook. Entering manual information can be very time-consuming and in some cases may be prone to error. Reports created based on external data connections to key business applications mean you reduce the amount of effort users have to dedicate to produce reports and help minimize the requirement for manual entry which can sometimes lead to errors.

Although previously Microsoft Office applications such as Excel and InfoPath supported data connections, users had to create their own individual data connections for each report or document. Chapter 10 looked at the creation of InfoPath forms. This meant repeating work to maintain similar information.

Using SharePoint 2007, you can create a data connection library that has a shared repository of data connection files, which multiple reports and documents can share. If you need to make a change to the data source, you only need to update one file rather than every single report.

Although a data connection library is similar to all other document libraries in SharePoint, you don’t manage documents; instead, the items are special files that contain information about specific business applications and how to connect to them to pull information. The next three Try It Outs show you how to navigate around data connections. For a data connection library to provide information to Excel Services documents as a trusted source, you must first add it as a trusted data connection source within the Shared Services Administration site, which you learn to do in the first Try It Out. Once you identify the data connection library as a trusted location, the second Try It Out shows you how users can upload their spreadsheets, reports, and business documents. In the last Try It Out, you see how to add an existing data connection file to a document.

Try It Out-Add a Trusted Data Connection Library

Before you can start using a central data connection library on your site for the storage of data connection files for your Excel Services documents, you must first add it as a trusted data connection library within Excel Services. In the previous Try It Outs, you created a reporting site for tracking performance of your organization. For this example, you add your performance site’s data connection library as a trusted library within your Excel Services environment. You use the data connections from this library for a workbook in an upcoming example.

1. Go to the SharePoint Central Administration site for your SharePoint environment.

2. Click the link to the Shared Services Administration site from the side navigation.

3. From the Excel Services Settings group, select Trusted Data Connections Libraries.

4. Click the Add Trusted Data Connection Library button from the toolbar.

5. Enter the address of the data connection library from your performance site.

6. Enter the following for a description:

   Important 

   Location for storage of shared data connections related to documents stored within the performance reporting site.

7. Click the OK button.

How It Works

The page refreshes with your data connection library URL listed as a trusted connection, as shown in Figure 11-13.

Figure 11-13

Because data connections from your library will be called from within Excel Services documents, it is important that a system administrator verifies and trusts them. This acts as a security measure to ensure that files do not call unsafe locations that may not be secure or may contain untrusted files or information.

Try It Out-Upload a Data Connection File to SharePoint

In this exercise, you create an Access database that business users use to collect and track important sales information. Once created, you construct a data connection file for it from Excel using the standard Data Connection tab commands. You then save it in a local file system folder as a .UDC file. You can take that file and upload it to a data connection library in SharePoint so that it’s available for use for other files in the future.

You start by creating a sales pipeline database using Access. Access is just one example of a data source that you can connect to via Excel. Other sources may include an Excel document, website, SQL database, Oracle database, or OLAP cube (OLAP stands for on-line analytical processing). The exercise went with an Access database because you more than likely have the templates to easily create such a source. Using the Data tab, you can add data connections to a workbook with just a few clicks and then add them to a local folder where you store other data connections. Of course, it’s more efficient to have a central location where you can share data connection files with others, so you upload the connection file to a shared repository on the performance site.

1. Open Microsoft Office Access 2007.

2. From the Template Categories group, select Local Templates and then select the Sales Pipeline Database template.

3. Click the Create button. Your sales database will be created.

4. Close the Access application and open Microsoft Office Excel 2007.

5. Select the Data tab from within Excel.

6. From the Get External Data group of commands, select From Access to allow yourself to select an Access data source for information to display in your spreadsheet.

7. Browse to and select the Access database you created in the previous steps.

8. Select the customers table and click the OK button. The Import Data dialog box appears as shown in Figure 11-14.

   
   Figure 11-14

9. Select the option to display a table of data within the existing worksheet using the settings and click the OK button.

10. Close the Excel document without saving your changes. Even though you are closing the workbook that you had created, the data connection file still exists in your My Data Sources folder with your Documents folder.

    Tip 

    If you are running Microsoft Windows Vista, data connection files are stored in a My Data Sources folder that is automatically generated in your Documents folder as soon as you create your first data connection. If you are running Microsoft Windows XP, the same folder is created for you automatically in your My Documents folder.

11. Go to the home page of your performance site and select Data Connections from the Quick Launch bar.

12. Click the Upload button.

13. Browse to your My Data Sources folder and select the data connection file for your sales pipeline database and click the OK button.

14. For Content Type, select Office Data Connection File.

15. Enter sales and customers as keywords to help make the file easy for others to find.

16. Click the OK button.

17. Hover your mouse over the document to expose the file’s contextual menu.

18. Select Approve/Reject.

19. Select Approved from the list of approval choices and click the OK button.

How It Works

Because of the Content Approval setting on the data connection library, you had to get the file approved before you could share it. This is the default setting for this library when created as part of the Report Center site. You can change content approval settings from the Version History settings of a library.

Try It Out-Use a Data Connection File from a SharePoint Library

In this example, you create a new spreadsheet and add an existing data connection from the centralized library of data connections. Because you have added this data connection library to your SharePoint server farm’s trusted data connection libraries in the first Try It Out of this section, this location is a suitable place for storing all connections to key business applications.

1. Open Excel and launch a blank workbook.

2. Select the Data tab to access information from business applications.

3. From the Connections group, select the Connections button.

4. Because you started with a blank workbook, no existing connections are present. Instead you add a connection from the performance reporting site’s data connection library. To do this, click the Add button.

5. Because the data connection you are looking for is probably not displayed on the list of connections that appears, select the Browse for More button.

6. Type the URL of your performance reporting site and click the Open button.

7. Select the Data Connections library from the list of content that is available on your site.

8. Select the Sales Pipeline Customers.odc file that is displayed in the library and click the Open button.

9. Click the Close button to close the Workbook Connections window. Now, when you click the Existing Connections window, you will be able to access your Sales Pipeline database.

How It Works

Your connection is now ready for use within your new spreadsheet. This approach encourages users to share data connection files. It minimizes the effort of creating reports and maximizes control over what data sources are used. If later you make changes to the Sales Pipeline database customer table, you will only need to update one file to maintain accurate information within the various reports.