Business Intelligence in SharePoint 2010 with Business Connectivity Services : Consuming External Content Types (part 1) - External Lists & External Data

When all the heavy lifting is complete, and the external content type has been built and saved into SharePoint, it is ready for consumption. There are three main ways that you can leverage the external content type in SharePoint: External Lists, External Data, and BCS web parts.

External Lists

An external list looks and acts just like a SharePoint list. Contents are displayed in rows and columns. You can also leverage the columns by sorting and filtering on them and by creating custom views. The main difference between an external list and a regular SharePoint list is that the actual contents of the external list live outside of SharePoint, hence the name.

To create an external list, complete the following steps:

1.	In Internet Explorer, open the SharePoint site . The URL will be something like http://mss2010.mydomain.com/sites/BCSSample/.
2.	Select Site Actions and View All Site Content.
3.	In the All Site Content page, click Create.
4.	In the Create page, select External List, and click Create.
5.	In the new list page, enter Customers as the list name.
6.	Leave the Display This List on the Quick Launch option set to Yes.
7.	For the External Content Type field, an External Content Type picker is available. Click the Select External Content Type icon. Select BCS Customer, as shown in Figure 1, and click OK. Figure 1. Settings for creating a new external list.
8.	BCS Customer (AdventureWorksLT) should display as underlined. Click Create.

The resulting display is the entire Customers table from the AdventureWorksLT database looks and acts like a standard SharePoint list. Notice that the following capabilities are available on the list, as shown in Figure 2:

• Sorting— Hover over any of the column headers to sort the list in either Ascending or Descending order.

• Filtering— Any column—for example, SalesPerson—can be used for refining the number of records shown in the list.

• Views— As with any other SharePoint list, site owners can create custom views.

Figure 2. Sorting and filtering columns on an external list.

External Data

The second means by which external content types can be consumed in SharePoint sites is through External Data columns. An External Data column basically provides a hybrid between a SharePoint list and an external list. External Data starts with any basic SharePoint list. The following steps use the SharePoint issues list with an external column to connect each issue to one of Adventure Works’ customers:

1.	In the SharePoint site, select the View All Site Content option from the Site Actions menu.
2.	In the All Site Content page, click Create.
3.	In the Create page, select Issues Tracking. In the Name box, type Customer Complaints and click Create.
4.	Once the list is created, click the List Ribbon, and click the Create Column icon.
5.	Type Customer as the Column Name, and select External Data as the column type.
6.	In the External Content Type picker, type the word Customer into the text box, and click the checkmark icon to Check if External Content Type Exists. BCS Customer (AdventureWorksLT) should display as underlined, as shown in Figure 3. Figure 3. Applying the External Data column settings.
7.	Select CustomerID from the Select the Field to be Shown on This Column list box.
8.	Under Add a Column to Show Each of These Additional Fields, select CompanyName, EmailAddress, FirstName, LastName, Phone, and SalesPerson, as shown in Figure 3. When these selections have been made, all the fields selected will be dynamically added to the custom SharePoint list. Click OK.

When all this is complete, you can begin entering data into the Customer Complaints list.

1.	In the Customer Complaints list, click the Add New Item link.
2.	Type Order received was incomplete as the Title.
3.	The Customer field, displayed at the bottom of the New Item form, provides an External Item picker utility. Click the Select External Item icon (the second icon). Select the record with CustomerID of 3, Donna Carreras, and click OK. Note When creating the Read List operation on the Customers table, the Return Parameters portion of the wizard included a Show in Picker check box for each field listed. The exercise called for checking this box for the CustomerID, FirstName, LastName, EmailAddress, and Phone fields. These are the fields displayed in the Choose BCS Customer lookup.
4.	Click Save to commit the new item to the Customer Complaints list.
5.	Repeat steps 1 through 4 to add a second record. Type Order included broken parts as the Title. Select the record with a CustomerID of 4, Janet Gates.
6.	Repeat steps 1 through 4 to add a third record. Type Order is three weeks overdue as the Title. Select the record with a CustomerID of 1, Orlando Gee.

Figure 4 shows a view of the Customer Complaints list that has the three records entered in the preceding exercises. Notice that, in addition to the traditional Issue Tracking columns, there is also the Customer (External Data) column.

Figure 4. View of a SharePoint list with an External Data column.

The view also includes several additional columns relating to the customer. These columns are prefixed with Customer:, which indicates that they are columns based on the Customer External Content Type.

Periodically, data in the source SQL Server database may change. The BCS database contains cached versions of this data. In Figure 31.8, the Customer column header is highlighted, the result of hovering the mouse over this header. The column includes a double-arrow icon next to it. Clicking this arrow initiates a refresh of the data stored in SQL Server. If using External Data columns, periodically click this icon to ensure that the most current data is displayed in your SharePoint lists.